Access VB Code not working

Question

This is where it didn't compile:

            DoCmd.RunSQL "UPDATE InspEvent " & _
                 "SET InspEvent.SpecsLoaded = lngRecordsAdded " & _
                 "WHERE InspEvent.EventId = [Forms]![frmInspEvent]![txtEventId];"

Where is the error happening? The liklihood is that you're not passing a valid parameter for the line "WHERE InspEvent.EventId = [Forms]![frmInspEvent]![txtEventId];" but more information is needed. The "Missing Parameter" is saying it's expecting a value, but not getting one. — Erin B, Apr 13 '18 at 15:31
Error happens in the Private Sub cmdDone_Click() part of the code. Do you have any ideas how I can fix the code? — techindustry, Apr 13 '18 at 15:49
Have you step debugged? You might want to disable error handlers for debugging. — June7, Apr 14 '18 at 01:25
How do you step debug? I'm very new to vba and so I don't really know much. Do I just comment out the parts of code where there is Err? Please note that the code works perfect in Access 2003 but not in Access 2016. Thanks! — techindustry, Apr 14 '18 at 13:04
Comment the `On Error GoTo` line and/or set breakpoint(s). Why don't you search web for tutorials on debugging? — June7, Apr 14 '18 at 17:34
[How to debug VBA code](http://www.cpearson.com/excel/DebuggingVBA.aspx) -- [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) — Andre, Apr 16 '18 at 13:16
Your where statement should be something like: `"WHERE InspEvent.EventId = " & [Forms]![frmInspEvent]![txtEventId] & ";"` — Brandon Pratt, Apr 16 '18 at 14:09
Actually, `DoCmd.RunSQL` can evaluate Forms and Control references inside the SQL string. `lngRecordsAdded` sounds more suspicious like a VBA variable. @BrandonPratt — Andre, Apr 16 '18 at 14:31
I think you've cut your code down too much - there's nothing too obvious in the SQL string that would cause the error. `lngRecordsAdded` sounds like a VBA defined variable, but is placed within the SQL string - so it's reading it as `lngRecordsAdded` rather than the value it represents. — Darren Bartrup-Cook, Apr 16 '18 at 15:55

Darren Bartrup-Cook · Accepted Answer · 2018-04-16T16:16:47.103

Looking at your original code you have lngRecordsAdded defined in your code module.

One update would be:

DoCmd.RunSQL "UPDATE InspEvent " & _
             "SET InspEvent.SpecsLoaded = " & lngRecordsAdded & _
             " WHERE InspEvent.EventId = " & [Forms]![frmInspEvent]![txtEventId]

Edit:
Another way would be:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("", "PARAMETERS RecordsAdded LONG, Event_Identifier LONG; " & _
    "UPDATE InspEvent SET SpecsLoaded=RecordsAdded " & _
    "WHERE EventID = Event_Identifier")
With qdf
    .Parameters("RecordsAdded") = lngRecordsAdded
    .Parameters("Event_Identifier") = [Forms]![frmInspEvent]![txtEventId]
    .Execute
End With

Edit 2:
Looking through your original code you also have code blocks like:

If IsNull(DLookup("Vendor", "PurchaseOrder", strFilter)) Then
    strVendor = "None"
Else
    strVendor = DLookup("Vendor", "PurchaseOrder", strFilter)
End If

This could be shortened to the single line:

strVendor = Nz(DLookup("Vendor", "PurchaseOrder", strFilter), "None")

Access VB Code not working

1 Answers1