Multiple insert problem

Question

I am doing a multiple insert in vba access query as below:

Private Sub btnSubmit_Enter()
DoCmd.RunSQL ("insert into tblAutonumber (Dummy)values ('DummyValue')")
Dim lastAutonumber As Long
lastAutonumber = DMax("Autonumber", "tblAutonumber")
txtAutoNumber.Value = lastAutonumber
DoCmd.RunSQL ("insert into tbltesting " & _ 
    "(Empid, TestScenario, testid, owner, event, version, " & _ 
    "expresult, variation, status, homestore) values ('" & _
        Me.txtEmpNo.Value & "','" & _
        Me.txtTestScenario.Value & "','" & _
        Me.txtAutoNumber.Value & " ' ,'" & _
        Me.txtOwner.Value & "','" & _
        Me.txtEvent.Value & "', '" & _
        Me.txtVersion.Value & "','" & _
        Me.txtExpectedResult.Value & "', '" & _
        Me.txtVariation.Value & "', '" & _
        Me.txtStatus.Value & "','" & _
        Me.txtHomeStore.Value & "')")

DoCmd.RunSQL ("insert into tblContract " & _ 
    "(Empid, testid, Day, Start1, Finish1, Store1," & _ 
    "Start2, Finish2, Store2 ) values ('" & _
        Me.txtEmpNo.Value & "','" & _
        Me.txtAutoNumber.Value & "','Sunday ','" & _
        Me.txtContSunStart1.Value & "', '" & _
        Me.txtContSunFinish1.Value & "','" & _
        Me.txtContSunStore1.Value & "','" & _
        Me.txtContSunStart2.Value & "', '" & _
        Me.txtContSunFinish2.Value & "','" & _
        Me.txtContSunStore2.Value & "')")

DoCmd.RunSQL ("insert into tblContract " & _ 
    "(Empid, testid, Day, Start1, Finish1, Store1, " & _ 
    "Start2, Finish2, Store2 ) values ('" & _
        Me.txtEmpNo.Value & "','" & _
        Me.txtAutoNumber.Value & "','Monday ','" & _
        Me.txtContMonStart1.Value & "', '" & _
        Me.txtContMonFinish1.Value & "','" & _
        Me.txtContMonStore1.Value & "','" & _
        Me.txtContMonStart2.Value & "', '" & _
        Me.txtContMonFinish2.Value & "','" & _
        Me.txtContMonStore2.Value & "')")

DoCmd.RunSQL ("insert into tblContract " & _ 
    "(Empid, testid, Day, Start1, Finish1, Store1," & _ 
    "Start2, Finish2, Store2 ) values ('" & _
        Me.txtEmpNo.Value & "','" & _
        Me.txtAutoNumber.Value & "','Tuesday','" & _
        Me.txtContTueStart1.Value & "', '" & _
        Me.txtContTueFinish1.Value & "','" & _
        Me.txtContTueStore1.Value & "','" & _
        Me.txtContTueStart2.Value & "', '" & _
        Me.txtContTueFinish2.Value & "','" & _
        Me.txtContTueStore2.Value & "')")

DoCmd.RunSQL ("insert into tblContract " & _ 
    "(Empid, testid, Day, Start1, Finish1, Store1, " & _ 
    "Start2, Finish2, Store2 ) values ('" & _
        Me.txtEmpNo.Value & "','" & _
        Me.txtAutoNumber.Value & "','Wednesday','" & _
        Me.txtContWedStart1.Value & "', '" & _
        Me.txtContWedFinish1.Value & "','" & _
        Me.txtContWedStore1.Value & "','" & _
        Me.txtContWedStart2.Value & "', '" & _
        Me.txtContWedFinish2.Value & "','" & _
        Me.txtContWedStore2.Value & "')")

DoCmd.RunSQL ("insert into tblContract " & _ 
    "(Empid, testid, Day, Start1, Finish1, Store1," & _ 
    "Start2, Finish2, Store2 ) values ('" & _
        Me.txtEmpNo.Value & "','" & _
        Me.txtAutoNumber.Value & "','Thursday','" & _
        Me.txtContThuStart1.Value & "', '" & _
        Me.txtContThuFinish1.Value & "','" & _
        Me.txtContThuStore1.Value & "','" & _
        Me.txtContThuStart2.Value & "', '" & _
        Me.txtContThuFinish2.Value & "','" & _
        Me.txtContThuStore2.Value & "')")

DoCmd.RunSQL ("insert into tblContract " & _ 
    "(Empid, testid, Day, Start1, Finish1, Store1," & _ 
    "Start2, Finish2, Store2 )values ('" & _
        Me.txtEmpNo.Value & "','" & _
        Me.txtAutoNumber.Value & "','Friday','" & _
        Me.txtContFriStart1.Value & "', '" & _
        Me.txtContFriFinish1.Value & "','" & _
        Me.txtContFriStore1.Value & "','" & _
        Me.txtContFriStart2.Value & "', '" & _
        Me.txtContFriFinsh2.Value & "','" & _
        Me.txtContFriStore2.Value & "')")

DoCmd.RunSQL ("insert into tblContract " & _ 
    "(Empid, testid, Day, Start1, Finish1, Store1," & _ 
    "Start2, Finish2, Store2 )values ('" & _
        Me.txtEmpNo.Value & "','" & _
        Me.txtAutoNumber.Value & "','Saturday','" & _
        Me.txtContSatStart1.Value & "', '" & _
        Me.txtContSatFinish1.Value & "','" & _
        Me.txtContSatiStore1.Value & "','" & _
        Me.txtContSatStart2.Value & "', '" & _
        Me.txtContSatFinish2.Value & "','" & _
        Me.txtContSatStore2.Value & "')")

End Sub

First two queries works fine (i.e. till Sunday)

But from Monday the execution breaks

I checked all variable in the query , there is not nulls there

Answer 1

Without knowning more about how the program "breaks" my guess is that one of the values for Monday contains characters that SQL Server is interpreting as syntactic elements. In otherwords you are injecting unescaped strings into the query string. (Incidentally, this is the cause of SQL Injection Attacks). If possible, you should prefer parameters over building up querys like this.

Answer 2

print your concatenations out, I'll bet you can see your error then...

Answer 3

As expressed above by Colin Mackay, your specific problem is likely to be an invalid query in the bunch.

Clear it up by...Making it visible and repeatable..and then offload the diagnostics to msaccess by ....

Building your queries in a variable so that you may easily dump them out...

You will encounter the problem:

1. Dump out the query text ...(cntrl-g, then type ? sqltxt )
2. Use access itself to diagnose. Do this by simply copying the querytext into a new query (sql view) and either switching to design mode or attempting to run the query... Access will complain and usually give you enough of a hint to fix the query...

For the future consider adding appropriate error handling for each query.

Also consider using currentdb.execute instead of docmd, because, for the latter, you may have to take the additional step of suppressing confirmation boxes (as per my foggy memory of msaccess2k)

Also dlookup and dmax (+1) on the current are handy function for getting a unique key without having to resort to an autonumber. I only say this because it appears you are using a table to generate keys...

Good luck!

Answer 4

If your query "breaks" by going to the code window with the yellow line and arrow but does not give you any error message then it is a good symptom that the compiled code is somewhat corrupted.

If this is your symptom then a decompile & recompile would probably correct your problem.

You can decompile by adding the switch /decompile when you call Access with your database.

"c:\Program Files\Microsoft office\office\msaccess.exe" 
                                  /decompile "c:\My Documents\MyDatabase.mdb"

And recompile by going to the code windows within Access and using the menu Debug->Compile...

Answer 5

While you are in break mode, press Ctl+G to open the debug window, and type:

? txtContTueStart1, txtContTueFinish1

etc..to make sure those values are ok.