Update strsql does not work...

[egghi]

Hi,

I have a update string for a form in Access 2003, and I just can't get it to work. Please help!!

Dim strUpdateEmail As String

If .... Then
strUpdateEmail = "UPDATE [User] SET Email = '" & Me.txtFillEmail.Value & "' WHERE WindowsUser ='" & Me.txtFillWindowsUser.Value & "';"
DoCmd.RunSQL strUpdateEmail
Else
End If

Thank you, thank you!

[kxcntry99]

Are you getting an error message or its just not doing the update?

[egghi]

I did not get an error message. It is just not updating anything...

[lintz]

One thing to check is the value of "Me.txtFillWindowsUser.Value" as if that doesn't match a value in your database nothing will be updated.

[egghi]

I have a line of code to check there is a match. So, this should not be a problem...

[GaryMazzone]

What is the actual SQL that is being generated by the app? Could you post that here?

[egghi]

Hi,

The line of code which checks whether the windows user is in the User table is ran after users typed in the windows user name. The code below is to see if user enter the email information correctly or at all after confirming that the user is in the User table. Thank you!

Private Sub cmdAddUser_Click()

'Check to see if data is entered into txtFillEmail and if the table needs to be updated

If IsNull(Me.txtFillEmail.Value) Or Me.txtFillEmail.Value = "" Then

MsgBox "You must enter the new user's email address.", vbOKOnly, "Required Data"
Me.txtFillEmail.SetFocus
Exit Sub
Else
If DLookup("Email", "User", "WindowsUser ='" & Me.txtFillWindowsUser.Value & "'") <> Me.txtFillEmail.Value Then
MsgBox ("The email address you entered," & Me.txtFillEmail.Value & ", is different from the user's default email address in our database. Do you want to replace the user's email address in our database with your new entry?"), vbYesNo, "Data Integrity"
If Check = vbYes Then
Yes = True
strUpdateEmail = "UPDATE [User] SET Email = '" & Me.txtFillEmail.Value & "' WHERE WindowsUser ='" & Me.txtFillWindowsUser.Value & "';"
DoCmd.RunSQL strUpdateEmail
ElseIf Check = vbNo Then
No = True
Me.txtFillEmail.Value = DLookup("Email", "User", "WindowsUser ='" & Me.txtFillWindowsUser.Value & "'")
End If
Else
End If
End If

End Sub

[GaryMazzone]

Please use the vbcode tags when you post code. The code is one thing but what is the actuall generated SQL statement that is being run.

[kxcntry99]

strUpdateEmail = "UPDATE [User] SET Email = '" & Me.txtFillEmail.Value & "' WHERE WindowsUser ='" & Me.txtFillWindowsUser.Value & "';"

Have you tried taking the [] away from around the user table? If I remember right access doesn't need the table name in brackets...only field names.

Hope that helps

[egghi]

I did, but it still does not work... Thank you for the idea though!

[kxcntry99]

Have you tried putting the SQL string into a message box after the values are filled in? If not, try that and post the results of exactly what SQL string the app is sending to Access. For example:

If Check = vbYes Then
Yes = True
strUpdateEmail = "UPDATE [User] SET Email = '" & Me.txtFillEmail.Value & "' WHERE WindowsUser ='" & Me.txtFillWindowsUser.Value & "';"
[B]MsgBox strUpdateEmail[/B]
DoCmd.RunSQL strUpdateEmail

[egghi]

Hi,

I entered the msgbox and the weirdest thing is that the msgbox (msgbox strUpdateEmail) was never displayed after I clicked on "Yes"... I think I am going to rewrite the event with a different logic since I have been trapped in this whole for too many days... Thank you for your help!

[kxcntry99]

Hi,

I entered the msgbox and the weirdest thing is that the msgbox (msgbox strUpdateEmail) was never displayed after I clicked on "Yes"... I think I am going to rewrite the event with a different logic since I have been trapped in this whole for too many days... Thank you for your help!

No need to rewrite the event...it looks like you never actually set check = vbyes...your IF THEN statement will not lead to the sql statement because check is never set to yes.

[leinad31]

Your not returning the result of the msgbox or checking the result of the msgbox hence subsequent code fails. Try

Dim lReply As Long
lReply = MsgBox ("blah blah", vbYesNo)
If lReply = vbYes Then

Or

If MsgBox("blah blah", vbYesNo) = vbYes Then
   ...
Else

Or

Select Case MsgBox("blah blah", vbYesNoCancel)
Case vbYes
 
Case vbNo
 
Case vbCancel
 
End Select