Using Variables in a MSAccess Function with SQL

Question

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?

`"UPDATE LoginDetails SET EmployeeID =" & index & " WHERE Username = '" & frm.txt_username.Value & "'"` — Kostas K., Nov 26 '21 at 11:18

PBD · Accepted Answer · 2021-11-26T11:27:38.310

2

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

edited Nov 26 '21 at 11:27

answered Nov 26 '21 at 11:24

PBD

51
4

1

You're missing the `&` in username... – Kostas K. Nov 26 '21 at 11:26
Amazing thank you. I know VBA but not SQL as much. Are you able to tell me why I need to put the textbox.value in 2 quotation marks? Is one for the textbox and the other for SQL? – Tom TK Nov 26 '21 at 12:00
The value is of string type – Nathan_Sav Nov 26 '21 at 12:06
Single quotes are identifiers for strings in SQL. They (mostly) serve the same purpose as double quotes in VBA. – PBD Nov 26 '21 at 12:08
1

Beware of errors if the concatenated value itself contains `'`. Use parameters or use Gustav's [`CSql()` function](https://stackoverflow.com/a/36494189/3820271) when concatenating variables with SQL. – Andre Nov 26 '21 at 12:49

score 0 · Answer 2 · answered Dec 03 '21 at 23:26

0

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

answered Dec 03 '21 at 23:26

Sola Oshinowo

519
4
13

Using Variables in a MSAccess Function with SQL

2 Answers2