VBA Dlookups and SQL inserting into another table

[rob0r]

Hello,

I’m basically attempting to lookup values from one table (from multiple records) and insert them into one record in another table. I’ve attempted to do this with dlookups and dmaxs (which pick up exactly what I want) and using an SQL statement to insert them into another table. My code is below.


Sub Instance()
Dim offtime As Date
Dim userdatetime As Date
Dim userdate As Date
Dim userevent As String
Dim userid As Single
Dim prevuserdatetime As Date
Dim rawtime As Single

userid = DMax("ID", "Q_syslog")
userdatetime = DLookup("convdatetime", "q_syslog", "id=" & userid)
userevent = DLookup("event", "Q_syslog", "id=" & userid)
prevuserdatetime = DLookup("convdatetime", "q_syslog", "id=" & userid - 1)
rawtime = (DateDiff("s", prevuserdatetime, userdatetime)) / 86400
offtime = Format(rawtime, "HH:MM:SS")
InstanceID = Nz(DMax("Instance", "Instance"), 0) + 1

DoCmd.RunSQL "INSERT INTO Instance ([Instance], [Logout], [Login],[Event],stoppagetime) VALUES (Instanceid, prevuserdatetime,userdatetime,userevent,offtime);"



I was aware I might have issues due to compatibility issues with the dlookups and the SQL statement. The dlookups work, and the insert statement works, but not with each other! When it inserts it treats the values as blank parameters.

Any suggestions would be great,

Thanks

 

[DreamGenius]

You're all but there. Your SQL string should actually be something more like this though:

[FONT=Courier New]DoCmd.RunSQL "INSERT INTO Instance ([Instance], [Logout], [Login],[Event],stoppagetime) VALUES (" & Instanceid & ", " & prevuserdatetime & ", " & userdatetime & ", " & userevent & ", " & offtime & ");"[/FONT]

For what it's worth, I tend to construct my SQL in a string, debug.print it and then call it, like this:

[FONT=Courier New]Dim strSQL as String[/FONT]
[FONT=Courier New]strSQL = "INSERT INTO Instance ([Instance], [Logout], [Login],[Event],stoppagetime)" & vbCrLf & _[/FONT]
[FONT=Courier New]"VALUES (" & InstanceID & ", #" & PrevUserDateTime & "#, #" & UserDateTime & "#, """ & UserEvent & """, #" & OffTime & "#);"[/FONT]
[FONT=Courier New]Debug.Print strSQL[/FONT]

[FONT=Courier New]DoCmd.RunSQL strSQL[/FONT]

This enables you to trouble-shoot the SQL easier without upsetting the user experience with message boxes; you get to see it in the Immediate window in the Visual Basic Editor. Note that you also have to 'tell' MS Access what your data types are in the query by encapsulating them:

• Dates and Times with #, thus #01/02/03# or #04:05:06#
• Strings with quotes, remembering to double them in VBA, thus ""Hello Mum""
• Numeric values can be left as are, thus 123

Also, if you capitalise your variables when you define them, later spelling mistakes will be easier to spot because, as you type in lower case, a successful variable will be automatically corrected to the correct case.

 

[rob0r]

You sir are a legend! Brilliant advice and it works perfectly!

Thank you very much.

Rob