Using Variables in a MSAccess Function with SQL

I’m struggling with entering a custom made ID number on all my tables to link all records together, trying out normalization.

I have an EmployeeDetails table and a LoginDetails table. Its my understanding that I need a field called EmployeeID on both and I can use the SQL Select code I have to pull the data like on other forms. Username is the field name also in the LoginDetails table and txt_Username is the username’s textbox on the login page.

For now I just put this on the login screen to troubleshoot, but it will end up being called when a new employee is created. Only problem is my knowledge on how to include a variable within the SQL line, ie:

Function newID(frm As Form)

    Dim db As DAO.Database
    Set db = CurrentDb

    index = 12345
         
    db.Execute "UPDATE LoginDetails " & "SET EmployeeID = index " & "WHERE Username = frm.txt_username.Value;"

End Function

I’ve tried for about 2-3 days, maybe a couple of hours here and there to get it to work but I’m starting to get demotivated. I managed to write a line of SQL that had a static value as the EmployeeID, but as soon as you add VB Variables in the mix it dosen’t like it.

Can anyone help me please?

Answer

Think of the SQL line as a string that contains the SQL command.

The SQL part (table name, column name) is static , and you need to concatenate the variable you get from access to it. So your line would be:

db.Execute "UPDATE LoginDetails SET EmployeeID = " & index & " WHERE Username = '" & frm.txt_username.Value & "';"

Also, it’s good to leave a space before SQL keywords when concatenating (prevents problem at execution time).