CODEWITHSUNDEEP
sqlvbams-access
Joey B
Joey B

Reputation: 23

Writing to a database table from an Access form

I am trying to write to an editable database table, from an Access form, and I keep receiving a syntax error.

The table has about 30 columns, but I only want the record filling out 8 of them: (Job #, Job Name, Date, Superintendent, Hauling Vendor, Material, LTC, Loads).

The form consists of the 8 fields named the following:

These are a combination of drop down list from queries, text inputs, and hard coded values, so a simple Open Form > New Record will not work here; or so I do not believe.

Is this even even possible?

I am using the following line and can not get this error to clear. 

CurrentDb.Execute "INSERT INTO MATLog(Job #, Job Name, Date, Superintendent, Hauling Vendor, Material, LTC, Loads) " & _
    " VALUES(" & Me.ReportingCOM_Job & "," & Me.ReportingCom_JobName & ",#" & Me.ReportingBox_Date & "#," & _
    Me.ReportingCom_SI & "," & Me.ReportingCom_Mat & "," & Me.ReportingCom_Vendor & "," & Me.ReportingCom_LoadType & ",#" & Me.ReportingBox_Loads & "#)"

This form should create a new record with these 8 fields, and leave the rest blank.

Upvotes: 1

Views: 62

Answers (2)

Parfait
Parfait

Reputation: 107767

Simply use a saved query and open it to run. Doing so, you avoid any need to concatenate or punctuate.

SQL (saved below as stored query, replace myFormName with actual form name)

INSERT INTO MATLog([Job #], [Job Name], [Date], [Superintendent], 
                   [Hauling Vendor], [Material], [LTC], [Loads]) 
VALUES(Forms!myFormName!ReportingCOM_Job, Forms!myFormName!ReportingCom_JobName,
       Forms!myFormName!ReportingBox_Date, Forms!myFormName!ReportingCom_SI,
       Forms!myFormName!ReportingCom_Mat, Forms!myFormName!ReportingCom_Vendor, 
       Forms!myFormName!ReportingCom_LoadType, Forms!myFormName!ReportingBox_Loads)

VBA (no need to close action queries, use warnings to avoid user prompts)

'DoCmd.SetWarnings False
DoCmd.OpenQuery "mySavedQuery"
'DoCmd.SetWarnings True

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16025

Fields with spaces or other reserved names or symbols in their field names (such as Date) will need to be enclosed with square brackets, e.g.:

INSERT INTO MATLog([Job #], [Job Name] ... )

You will also need to surround the values of Text fields in the VALUES list with single or double quotes, e.g.:

",'" & Me.ReportingCom_JobName & "',

However, whilst the above should allow your query to execute successfully, your current approach of constructing a SQL statement using unvalidated form values input by a user is vulnerable to SQL injection, and you will also encounter issues when inserting text values which themselves contain SQL string delimiters (single/double quotes).

Instead, a better approach is to parameterise your query.

One possible example of this could be the following:

With CurrentDb.CreateQueryDef("", _
    "insert into matlog ([Job #], [Job Name], [Date], Superintendent, [Hauling Vendor], Material, LTC, Loads) " & _
    "values (p1,p2,p3,p4,p5,p6,p7,p8)")
    .Parameters!p1 = Me.ReportingCOM_Job
    .Parameters!p2 = Me.ReportingBox_JobName
    .Parameters!p3 = Me.ReportingBox_Date
    .Parameters!p4 = Me.ReportingCom_SI
    .Parameters!p5 = Me.ReportingCom_Vendor
    .Parameters!p6 = Me.ReportingCom_Mat
    .Parameters!p7 = Me.ReportingCom_LoadType
    .Parameters!p8 = Me.ReportingBox_Loads
    .Execute
End With

Upvotes: 1

Related Questions