CurrentDb.Execute code

[lightkeepr]

I have 3 values that are received from a form. They are bulk_add_name, bulk_add_weight and bulk_add_date. what I want to do is add an entry to the table called Payroll that puts bulk_add_name into field SchoolName, bulk_add_weight into ShiftWeight and bulk_add_weight to DateWorked. I also want it to get the value FullName from [Guard Information Query] and pass it into the Payroll table for fields DefaultGuard and WorkingGuard.

So if in [Guard Information Query] there is 20 names listed, 20 new records will be created in table Payroll, one for each name and the values bulk_add_name, bulk_add_weight and bulk_add_date are all added for each record.

Here is what I have been kicking around, where did I go wrong?

CurrentDb.Execute "INSERT INTO Payroll (SchoolName, ShiftWeight, DefaultGuard, WorkingGuard, DateWorked) " & _
"SELECT #" & bulk_add_name & "# AS SchoolName ", #" & bulk_add_weight & "# AS ShiftWeight ", [Guard Information Query].[FullName], [Guard Information Query].[FullName], #" & bulk_add_date & "# AS DateWorked " & _
"FROM [Guard Information Query] " & _
"WHERE (( [Guard Information Query].[Active])=Yes) " & _

 

[MarkK]

You have not described an error, nor have you described a symptom of a problem. What fails? What is the evidence of that failure?

 

[lightkeepr]

When I run the code, I get a popup box that says Compile error: Syntax Error. when I click ok, I am taken to the code with

Private Sub StartShiftAdd()

in yellow and

'MsgBox ("Started Shift Add")
CurrentDb.Execute "INSERT INTO Payroll (SchoolName, ShiftWeight, DefaultGuard, WorkingGuard, DateWorked) " & _
"SELECT #" & bulk_add_name & "# AS SchoolName ", #" & bulk_add_weight & "# AS ShiftWeight ", [Guard Information Query].[FullName], [Guard Information Query].[FullName], #" & bulk_add_date & "# AS DateWorked " & _
"FROM [Guard Information Query] " & _
"WHERE (( [Guard Information Query].[Active])=Yes) " & _
End Sub


in red.

 

[spikepl]

http://www.baldyweb.com/ImmediateWindow.htm

 

[MarkK]

Here, check out this code . . .

Private Sub Test1923467()
    Dim qdf As DAO.QueryDef
    
    Set qdf = CurrentDb.CreateQueryDef("", _
        "INSERT INTO Payroll " & _
            "( SchoolName, ShiftWeight, DefaultGuard, WorkingGuard, DateWorked ) " & _
        "SELECT p0, p1, FullName, FullName, p2 " & _
        "FROM [Guard Information Query] " & _
        "WHERE Active")
    With qdf
        .Parameters("p0") = bulk_add_name
        .Parameters("p1") = bulk_add_weight
        .Parameters("p2") = bulk_add_date
        .Execute dbFailOnError
        .Close
    End With
            
End Sub

. . . see if that makes any sense. It's a bit more verbose of a methodology, but it's much more reliable, and all the delimiters are handled automatically, and it looks like delimiters were part of the problem. Looks like you had delimited school name with "#" which are date delimiters.

 

[lightkeepr]

That is considerably better and it works like a charm! THANKS!!