Update/Insert Code

[tucker61]

I have the following code to log who opens a database,

Function LogOn()
Dim sUser As String
Dim sSQL As String
Dim sSQL1 As String
Dim sdb As String

DoCmd.SetWarnings False
sUser = strLogin
sdb = CurrentDb.Name

'sUser = Environ("username")
sSQL = "INSERT INTO tblUserLog ( UserID )" & "SELECT '" & sUser & "' AS [User];"
sSQL1 = "INSERT INTO tblUserLog ( currentdb )" & "SELECT '" & sdb & "' AS [currentdb];"
DoCmd.RunSQL sSQL
DoCmd.RunSQL sSQL1
DoCmd.SetWarnings True
End Function

The issue that i have is that the sSQL1 code inserts the database name onto a separate line in the table, i want it to update the original line, but cannot work out how to do it, Here is my code for logging off the database,
Function LogOff()
Dim sUser As String
Dim sSQL As String
Dim sdb As String
sdb = CurrentDb.Name

DoCmd.SetWarnings False
sUser = strLogin
'sUser = Environ("username")
sSQL = "UPDATE tblUserLog SET tblUserLog.LogOff = Now()" & "WHERE tblUserLog.UserID='" & strLogin & "' AND tblUserLog.LogOff Is Null;"
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Function

Please advise best way to log database name

 

[pbaldy]

Well, you would need an update query, but why not put in both values in one SQL statement?

INSERT INTO tblUserLog ( UserID, currentdb)
SELECT FirstValue, SecondValue

I'll let you work out the correct syntax.