SQL Update Syntax

[usfarang]

Trying to update a table that may not have a value in the field. No errors just no results.

DoCmd.RunSQL "UPDATE tblattempts SET tblattempts.userID = Me.txtUserID " & _
"WHERE (((tblUsers.userID)='" & Me.txtUserID.Value & "'));"

[June7]

Concatenate variables.

If you want to update a table with data from another table, really need a JOIN. Should probably show sample data and desired result.

Is userID a text or number field in each table? Should probably be number. Don't use apostrophe delimiters for number type.

[usfarang]

This is an unbound form. userID is a text field in both tables.

[June7]

I didn't ask if form is unbound - really irrelevant.

You may have read my post before I edited. Review again.

[usfarang]

This is all I have. it is on frmLogin as I said is unbound which you said is irrelevant. But just FYI is all.

tblattemptsAttemptsID PK
userID = TEXT




tblUsers
userID = TEXT




DoCmd.RunSQL "UPDATE tblattempts SET tblattempts.userID = Me.txtUserID " & _
"WHERE (((tblUsers.userID)='" & Me.txtUserID.Value & "'));"

[June7]

Seems to me you really want to INSERT new records into tblAttempts.

As I said, an UPDATE really should have JOIN clause.

Why would UserID be empty in tblAttempts?

[usfarang]

No attempts has been made to enter a wrong password on the form is why!
Unsure about the Insert syntax but yes I agree that is what I need.

Here is my attempt at it, no errors but does not work?

Dim strSQL As String 'Log failed Login Attempts to tblattempts.
strSQL = "INSERT INTO tblattempts (userID,ComputerName ) "
strSQL = strSQL & "VALUES (Me.txtUserID & " & Me.txtComputerName & "')" & _
"WHERE (((tblUsers.userID)='" & Me.txtUserID.Value & "'));"
CurrentDb.Execute strSQL, dbFailOnError

[June7]

Concatenation and apostrophes are not correct. WHERE clause is not needed.

strSQL = "INSERT INTO tblattempts (userID, ComputerName) VALUES('" & Me.txtUserID & "', '" & Me.txtComputerName & "')"

If you bound form to tblAttempts and moved to new record row, SQL would not be needed, just:

Me!UserID = Me.txtUserID
Me!ComputerName = Me.txtComputerName

[Welshgasman]

No attempts has been made to enter a wrong password on the form is why!
Unsure about the Insert syntax but yes I agree that is what I need.

Here is my attempt at it, no errors but does not work?

I am going to go to my grave saying this.

Put the sql text into a sql string. Then you can debug.print it until you get it correct. Then you can use it in the SQL command.

If you still cannot see the error, you can post here or elsewhere?

[usfarang]

Thanks for the help June and Welshgasman. I went a different route and all is well.