Update strsql does not work...

[childrenfirst]

Hi,

I have a sql string for a form in Access 2003, and it's just not working (no error message though):

Dim strUpdateEmail

strUpdateEmail = "Update [User] SET Email = '" & txt.FillEmail & "' WHERE WindowsUser = '" & Me.txtFillWindowsUser.Value & "'"

DoCmd.RunSQL strUpdateEmail


Any idea what I might have done wrong?

Thank you, thank you!

 

[JoeAtWork]

Are you sure there is a record in the table "User" that has WindowsUser value that matches whatever happens to be in the txtFillWindowsUser textbox?

 

[childrenfirst]

Yes, there is a record in the table "User" that has WindowsUser value that matches whatever happens to be in the txtFillWindowsUser textbox.

 

[TheAceMan1]

How are ya childrenfirst . . .

What is the source of [blue]txt.FillEmail[/blue] (are you sure about the dot)? . . . [purple]txtFillEmail[/purple] makes more sense!

See Ya! . . . . . .

 

[childrenfirst]

haha... you are right, I meant to type txtFillEmail but had a typo. It is txtFillEMail in the actual code though.

The txtFillEmail is a textbox on the form. Users enter their email address and the sql string is supposed to update the Email column in the User table according to the value in txtFillEmail.

I hope I answered your question.

Thank you!

 

[childrenfirst]

Hi,

I pasted the actual code for your information. The only part that currently does not work is the strUpdate string. Other Msgboxes and Dlookup all work fine though:

Private Sub cmdAddUser_Click()

Dim strUpdateEmail As String

'Check to see if data is entered into txtFillEmail and if the User 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

Thank you!!

 

[TheAceMan1]

childrenfirst . . .

Try this:

[blue]   Dim FE As Control, FWU As Control, SQL As String
   Dim Msg As String, Style As Integer, Title As String
   Dim SL As String, DL As String, Criteria As String
   
   Set FE = Me!txtFillEmail
   Set FWU = Me!txtFillWindowsUser
   Criteria = "WindowsUser ='" & FWU & "'"
   SL = vbNewLine
   DL = SL & SL

   If Trim(FE & "") = "" Then 'txtFillEmail has no data?
      Msg = "You must enter the new user's email address! . . ."
      Style = vbCritical + vbOKOnly
      Title = "Required Data! . . ."
      MsgBox Msg, Style, Title
      FE.SetFocus
   ElseIf Nz(DLookup("Email", "User", Criteria), "") <> FE Then
      Msg = "The email address you entered," & FE & ", " & _
            "is different from the user's default email address " & _
            "in our database." & DL & _
            "Do you want to replace the user's email address " & _
            "in our database with your new entry?"
      Style = vbInformation + vbYesNo
      Title = "Data Integrity! . . ."
      
      If MsgBox(Msg, Style, Title) = vbYes Then
         SQL = "UPDATE [User] SET Email = '" & FE & "' WHERE WindowsUser ='" & FWU & "';"
         DoCmd.RunSQL SQL
      Else
         FE = DLookup("Email", "User", "WindowsUser ='" & FWU & "'")
      End If
   Else
      [green]'what to do if [u]txtFillEmail[/u] has a value and
      '[u]DLookup("Email", "User", Criteria)[/u] has a value . . .[/green]
   End If
   
   Set FWU = Nothing
   Set FE = Nothing[/blue]

See Ya! . . . . . .