SQL in VBA to Update a Table 2

[08211987]

Hi,
I want to code inline sql in VBA to update a table that has one field to a variable.
Table name: tbl_User_ID
Field name: Login
My SQL:
strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = " & hold_logon_id
DoCmd.RunSQL strSQL

In debug mode when I hover over the variable hold_logon_id it shows "cfresh", when I hover over strSQL it shows strSQL = UPDATE tbl_User_ID SET tbl_User_ID.Login = "cfresh" but when I step to the DoCmd.RunSQL a dialog displays
"Enter Parameter Value"
cfresh

Can you tell what I am missing?
Thanks!

 

[PWise]

strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = '" & hold_logon_id [highlight #CC0000]& "'"[/highlight]

 

[Andrzejek]

when I hover over strSQL it shows strSQL = UPDATE tbl_User_ID SET tbl_User_ID.Login = "cfresh"

I don't think it does say that.
I think it says:[tt]
UPDATE tbl_User_ID SET tbl_User_ID.Login = cfresh[/tt]
and since there are no single quotes around [tt]cfresh[/tt], your statement crashes.

Consider this in your code:

strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = " & hold_logon_id[blue]
Desbug.Print strSQL[/blue]
DoCmd.RunSQL strSQL

This way you can SEE when you want to execute.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[08211987]

Thanks both for your suggestions.
I tried all of the statements below and the last one did not give me the parameter dialog, but it also did not update the table. Is there another statement to save the update in the table?
strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = " & hold_logon_id
Debug.Print "SQL: "; strSQL
strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = " & hold_logon_id & ""
Debug.Print "SQL: "; strSQL
strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = " & " & hold_logon_id & """
Debug.Print "SQL: "; strSQL
strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = "" & hold_logon_id & """
Debug.Print "SQL: "; strSQL

DoCmd.RunSQL strSQL

Debug.Print displays:
SQL: UPDATE tbl_User_ID SET tbl_User_ID.Login = cfresh
SQL: UPDATE tbl_User_ID SET tbl_User_ID.Login = cfresh
SQL: UPDATE tbl_User_ID SET tbl_User_ID.Login = & hold_logon_id & "
SQL: UPDATE tbl_User_ID SET tbl_User_ID.Login = " & hold_logon_id & "

Thanks!

 

[Andrzejek]

Looks like you did try a lot of ways, except the one PWise gave you :-(

strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = '" & hold_logon_id & "'"
Debug.Print strSQL[green]
'UPDATE tbl_User_ID SET tbl_User_ID.Login = 'cfresh'[/green]
DoCmd.RunSQL strSQL

Hint - look at the placement of double quotes and single quotes in your UPDATE string and how it is concatenated

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

I know it could be confusing at the beginning – all that single quotes, double quotes, & signs, but it is not that hard. What I do is:
Start with the 'hard coded' statement:[tt]

strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = '[blue]cfresh[/blue]'"[/tt]

(You do know [blue]cfresh[/blue] should be in single quotes, right?)) So those single quotes need to be left with your final statement. You just need to replace what's in [blue]BLUE[/blue]
[tt]
strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = '[red]" & hold_logon_id & "[/red]'"
[/tt]
So now the [red]RED[/red] part replaces the [blue]BLUE[/blue] part

You leave alone the single quote after the = sign, add double quote because that's the end of your string (for now), use & to add to your string, place your variable, use & to add some more string, and another double quote.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[08211987]

Thanks for clarifying! I did get past the parameter dialog but I am not seeing where the table is being updated. Maybe it is because I am stopping the debugger immediately after the statement DoCmd.RunSQL strSQL and looking at the table? Or is there another statement that is needed to save or refresh the table so I can see the record?
Thanks!

 

[Andrzejek]

If you are looking at the table in Access before you update the record, then run your code, you need to refresh the table to see the changes. But no 'save or refresh' statement needed in the code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[08211987]

It's still not updating the table, can you think of anything else I could be missing? It is a table with 1 text field: "Login", seems too simple!

What I coded:
strSQL = "UPDATE tbl_User_ID SET tbl_User_ID.Login = '" & hold_logon_id & "'"
Debug.Print "SQL: "; strSQL

What Debug.Print displayed:
SQL: UPDATE tbl_User_ID SET tbl_User_ID.Login = 'cfresh'

I also added the following code:
Set db3 = CurrentDb()
Set rst3 = db3.OpenRecordset("tbl_User_ID", dbOpenDynaset)
hold_user_id = rst3![Login] 'errors here with No current record

I received an error in my error procedure "No current record." on last statement.
Thanks!

 

[Andrzejek]

>It is a table with 1 text field: "Login"
>hold_user_id = rst3![Login] 'errors here with No current record

Looks to me you have an empty table, no records at all. Is that the case?
If so, there is nothing to update.

You may consider:
[tt]
strSQL = "Insert Into tbl_User_ID (Login) Values ('" & hold_logon_id & "')"
Debug.Print "SQL: " & strSQL
DoCmd.RunSQL strSQL
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[08211987]

That did it! Guess I didn't think about it not updating a blank record.
Thanks for you persistence.