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?

>Solution :

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).