Solved: sending data from word form field to access table

[phill952000]

Hi,

i have a word document that contains some form fields and they are named the relevant names.

i also have some vba code to gather what ever is input into the form fields and sent it to a access database.

the problem i am having is that this works perfectly with numbers but when i input text into the form fields and run the macro to send the data to access i get a runtime error;

runtime-error '-2147217904 (80040e10)':
no value given for one or more required parameters.

i do not understand why i get this runtime error with text but the data is sent to the access database perfectly if i just use number.

any help would be appriciated

i have attatched my code at the bottom just incase
Sub WordtoAccess()

'Transfer new shipping company record to

'Shippers table in Northwind database.

Dim connect As ADODB.Connection

Dim strConnection As String

Dim strexc As String

Dim strPath As String

Dim doc As Word.Document

Dim strCompany As String

Dim strContact As String

Dim strJobTitle As String

Dim strWorkNumber As String

Dim strMobile As String

Dim strFaxNumber As String

Dim strEmail As String

Dim strWebsite As String

Dim strAddress1 As String

Dim strAddress2 As String

Dim strTown As String

Dim strCounty As String

Dim strPostalCode As String

Dim strSupply As String

Dim bytContinue As Byte

Dim lngSuccess As Long

Set doc = ThisDocument

'On Error GoTo ErrHandler

strCompany = ActiveDocument.FormFields("Company").Result

strContact = ActiveDocument.FormFields("Contact").Result

strJobTitle = ActiveDocument.FormFields("JobTitle").Result

strWorkNumber = ActiveDocument.FormFields("WorkNumber").Result

strMobile = ActiveDocument.FormFields("Mobile").Result

strFaxNumber = ActiveDocument.FormFields("FaxNumber").Result

strEmail = ActiveDocument.FormFields("Email").Result

strWebsite = ActiveDocument.FormFields("Website").Result

strAddress1 = ActiveDocument.FormFields("Address1").Result

strAddress2 = ActiveDocument.FormFields("Address2").Result

strTown = ActiveDocument.FormFields("Town").Result

strCounty = ActiveDocument.FormFields("County").Result

strPostalCode = ActiveDocument.FormFields("PostalCode").Result

strSupply = ActiveDocument.FormFields("Supply").Result

'Confirm new record.

bytContinue = MsgBox("Do you want to insert this record?", vbYesNo, "Add Record")

Debug.Print bytContinue

'Process input values.

If bytContinue = vbYes Then

strexc = "insert into [MGM Address Book] " _
& "([Company], [Contact], [JobTitle], [Work Number], [Mobile], [Fax Number], [E-mail], [Website], [Address1], [Address2], [Town], [County], [PostalCode], [Supply])" _
& "VALUES (" _
& strCompany & ", " _
& strContact & ", " _
& strJobTitle & ", " _
& strWorkNumber & ", " _
& strMobile & ", " _
& strFaxNumber & ", " _
& strEmail & ", " _
& strWebsite & ", " _
& strAddress1 & ", " _
& strAddress2 & ", " _
& strTown & ", " _
& strCounty & ", " _
& strPostalCode & ", " _
& strSupply & ")"

Debug.Print strexc

'Substitute path and connection string with DSN if available.

strPath = "C:\Documents and Settings\Paul.MGMPLC\My Documents\word to access transfer\MGM ADDRESS BOOK.mdb"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & strPath

Debug.Print strConnection

Set connect = New ADODB.Connection

connect.Open strConnection

connect.Execute strexc, lngSuccess

connect.Close

MsgBox "You inserted " & lngSuccess & " record", vbOKOnly, "Error Added"

End If

Set doc = Nothing

Set connect = Nothing

Exit Sub

'ErrHandler:

'MsgBox Err.Number & ": " & Err.description, _
'vbOKOnly, "Error"

'On Error GoTo 0

'On Error Resume Next

'cnn.Close

'Set doc = Nothing

'Set cnn = Nothing

End Sub

[CreganTur]

The reason you're getting this error is related to basic SQL syntax. When you set parameters or values in a SQL string, you have to wrap the value with a symbol that tells SQL what data type you are working with. Numbers do not require a symbol, but strings must be wrapped with single quotes. Dates must be wrapped with pound signs.

The reason you are getting this error has to do with the VALUE section of your SQL string. As they stand, SQL reads the value of all of the variables in your VALUE section as being the number data type, because they have no data type qualifier (symbol).

It seems that you are going to be able to submit either numbers or text through the fields in your Word doc. Because of this, I would suggest you wrap all relevant fields that could be either data type with the single quote symbol- this means that SQL will handle everything as a string data type.

Here's the corrected version of your SQL that has the qualifiers:
strexc = "insert into [MGM Address Book] " _
& "([Company], [Contact], [JobTitle], [Work Number], [Mobile], [Fax Number], " _
& "[E-mail], [Website], [Address1], [Address2], [Town], [County], [PostalCode], [Supply])" _
& "VALUES ('" _
& strCompany & "', '" _
& strContact & "', '" _
& strJobTitle & "', '" _
& strWorkNumber & "', '" _
& strMobile & "', '" _
& strFaxNumber & "', '" _
& strEmail & "', '" _
& strWebsite & "', '" _
& strAddress1 & "', '" _
& strAddress2 & "', '" _
& strTown & "', '" _
& strCounty & "', '" _
& strPostalCode & "', '" _
& strSupply & "')"

[phill952000]

Thanks CreganTur,

spot on with everything,

much appriciated

phill952000