Import data from textbox into table (1 Viewer)

[mounty76]

Hi All,

I am trying to create a simple way that people can enter data into an email (in a perfect world some type of online form that I can send out a link to) then with this data import it into an access table.

I've managed to get as far as a text box with command button that puts the field names into an email, then once I get the reply wanted to copy and paste their reply back into the text box and click a button that then copies that data into the relevant fields. I have the below code to import the information from the textbox to the field but am getting an error "The expression On Click you entered as the event property setting produced the follow error: User-defined type not defined"

Does anyone have any ideas or alternative ways to do this?


Private Sub ParseLine(S As Sring)

If Left(S, 11) = "First Name:" Then FirstName = Trim(Right(S, Len(S) - 11))
If Left(S, 8) = "Surname:" Then Surname = Trim(Right(S, Len(S) - 8))

End Sub

Private Sub importbutton_Click()

Dim S As String

While InStr(custinfo, vbNewLine) <> 0
S = Left(custinfo, InStr(custinfo, vbNewLine) - 1)
ParseLine S
custinfo = Right(custinfo, Len(custinfo) - InStr(custinfo, vbNewLine) - 1)
Wend

If custinfo <> "" Then
ParseLine custinfo
custinfo = ""
End If

End Sub



 

[MajP]

That error is when you declare a variable but such a variable does not exist. Check your spelling

Dim x as itrager
Dim y as oject
Dim z as Dictionary (but no reference to scripting runtime)
(S As Sring)

 

[MajP]

Maybe something like

Private Sub importbutton_Click()
Dim aS() As String
dim i as integer
aS = split(CustInfo,vbNewLine)
for i = 0 to ubound(aS)
  if instr(aS(i),":") > 0 then
     parseLine aS(i)
  end if
next i
End Sub

Private Sub ParseLine(S As String)
  dim aS() as string
  aS = split(S,":")
  if aS(0) = "First Name" then FirstName = aS(1)
  if aS(0) = "Surname" then Surname = aS(1)
End Sub

 

[arnelgp]

Does anyone have any ideas or alternative ways to do this?

this line has error:

Private Sub ParseLine(S As Sring)

should be:

S As String

 

[mounty76]

Thanks arnelgp..... couldn't see the wood from the trees late last night

 

[mounty76]

This works now, but it is quite a crude way to import data (a little clunky copying and pasting). Does anyone have a better solution where by I can send a link to a form that someone can open with their phone, enter the details and then me import the details into a table? It is basically for new employees to enter their personal details in, I then can issue a contract based on their personal details. Open to any suggestions

 

[Gasman]

Google docs?
@Uncle_Gizmo has posted about using Google Docs.
The docs can populate a Google sheet,and you can use that.
They fill a form of your design online.

 

[mounty76]

I've been looking into that a bit, I think the person sending the data back needs a google account to do so, also I'd need a google account (which isn't the end of the world). I note in Access 2007 and 2010 there was a Collect Data Group function.....this seemed more suited to what I was after but in recent access versions it is not available. Will look at post using google docs anyway

 

[mounty76]

Looks at Uncle_Gizmo's post on it. It is straight forward but I need it automated as much as possible as I'm not always the one importing the data, need a clean solution, I'm thinking email is the easiest way as I don't have a webpage to use for data collection

 

[Gasman]

How about an Excel workbook?

 

[mounty76]

I've been using the excel workbook until now, but it requires the new employee to have access to excel then us saving the workbook in a specific location when we get it back. It has worked to be fair, just wanted to try and make it a bit more user friendly for the person returning the information

 

[MajP]

I have never used it nor do I think the feature still exists. Hopefully someone knows. But there was (maybe is) an Access Data Collection Form. I think the user still would need Outlook

Update: Only existed in 2007 - 2010.

 

[arnelgp]

..or you can use Fillable PDF.

 

[Gasman]

I've been using the excel workbook until now, but it requires the new employee to have access to excel then us saving the workbook in a specific location when we get it back. It has worked to be fair, just wanted to try and make it a bit more user friendly for the person returning the information

Excel on Android is free. I use it pretty much daily.

 

[mounty76]

arnelgp a fillable PDF would work and be much cleaner, have you any links or examples on how to go about this? I really don't have a clue...sorry! I have PDF editor etc.. so can make the PDF, just not sure what code I'd need to import the data into my table

 

[MajP]

I search this site. I know there is example of write to fillable PDF here at @theDBguy

Fillable PDF Forms

I think that demos read from too, but I have not gone through it.

 

[mounty76]

I search this site. I know there is example of write to fillable PDF here at @theDBguy

Fillable PDF Forms

I think that demos read from too, but I have not gone through it.

It looks like that is just exporting data to PDF, am happy making the fillable PDF, just don't know where to start with VBA to import the data back into access table

 

[theDBguy]

It looks like that is just exporting data to PDF, am happy making the fillable PDF, just don't know where to start with VBA to import the data back into access table

The third demo (pdf fields) can be used to scan a pdf for its field values that you can store into your table. Another approach is probably to export the pdf into xml and then import the result into Access.

 

[mounty76]

Thanks DBguy, this is getting in the right direction, however it copies the fillable name and not the data. EG: Question: Date of Birth: The fillable box is called DOB. The database form returns the value DOB and not the actually date in the fillable field.

If I can copy the actual data into a text box then I can paste this into a table.....I think lol

 

[theDBguy]

Thanks DBguy, this is getting in the right direction, however it copies the fillable name and not the data. EG: Question: Date of Birth: The fillable box is called DOB. The database form returns the value DOB and not the actually date in the fillable field.

If I can copy the actual data into a text box then I can paste this into a table.....I think lol

Please show us the code you're using. I'm not sure which version is posted on the website. If it's the one that doesn't pull the field's value, I can post a new one that does.