Update string in VBA 2

[childrenfirst]

Hi,

I have tried to debug the code without success for a couple days. For some reason, the update string is not updating the User table, and ended up erroring out. The code checks if the WindowsUser (based on the value in txtFillWindowsUser) is on the User table first, and then check if the WindowsUser's email column is Null. If email column for this WindowsUser is null, update the email for this WindowsUser on the User table with the value in the text box, txtFillEmail.

Any help or advice will be greatly appreciated!

Private Sub cmdUpdateEmail_Click()

Dim strUpdateEmail As String
Dim strWindowsUser As String
Dim strEmail As String

Dim db As Database

strWindowsUser = Me.txtWindowsUser.Value
strEmail = Me.txtFillEmail.Value

If DCount("WindowsUser", "User", "WindowsUser= '" & Me.txtFillWindowsUser.Value & "'") <> 0 And IsNull(DLookup("Email", "User", "WindowsUser ='" & Me.txtFillWindowsUser.Value & "'")) Then
strUpdateEmail = "Update User SET Email = '" & strEmail & "' WHERE Windowsuser = '" & strWindowsUser & "'"
MsgBox strUpdateEmail
DoCmd.RunSQL strUpdateEmail
Else
End If

End Sub

 

[Zion7]

What error are you getting & where?

Is this control
strWindowsUser = Me.txtWindowsUser.Value

same as

Me.txtFillWindowsUser.Value
?

 

[childrenfirst]

Hi,

I did not get an error message. The update string was just not executed while other lines of code worked perfectly...

Sorry about the typo. Yes, strWindowsUser = Me.txtFillWindowsUser.Value

 

[randy700]

What happens if you copy the string shown in the message box into a query and attempt to run it?


Randy

 

[Zion7]

Obviously, I assume you're reaching the RunSQL method?
In other words, the record you're on, is meeting your
criteria to run the update?

BTW condensed version....

strCriteria= "WindowsUser ='" & Replace(txtFillWindowsUser,"'","''") & "'"
strEmail = Me.txtFillEmail.Value

If DCount("WindowsUser", "User", strCriteria )<> 0 And IsNull(DLookup("Email", "User", strCriteria)) Then
strUpdateEmail = "Update User SET Email = '" & strEmail & "' WHERE " & strCriteria
MsgBox strUpdateEmail
DoCmd.RunSQL strUpdateEmail
Else
End If

 

[PHV]

Anyway, why not simply this ?

Private Sub cmdUpdateEmail_Click() 
Dim strUpdateEmail As String
strUpdateEmail = "UPDTE [User] SET Email='" & Me!txtFillEmail & "'" _
 & " WHERE WindowsUser='" & Me!txtFillWindowsUser & "' AND Trim(Email & '')=''"
DoCmd.RunSQL strUpdateEmail
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[childrenfirst]

Thank you for your help, guys! Your advices helped and it's working now It turned out that Access does required [] around reserved words!

Thank you. Have a great day!

 

[Zion7]

...and just to emphasize, if you don't mind childrenfirst,
PHV did point out the redundancy of the
"If statements & DCount()..."
your code will run faster without these methods.

You may want to add though

....
Docmd.SetWarnings False
Docmd.RunSQL
Docmd.SetWarnings True

...just food for thought!

 

[childrenfirst]

Cool! I will add the setwarnings to my code. Is setwarnings good for every module that has a Docmd.RunSQL command?

 

[Zion7]

Yes, provided you DO, want to supress the Confirmation dialog, in EVERY procedure.

To run SQL action commands, you can also use;
CurrentProject.Connection.Execute _
"INSERT INTO tbl...",,adExecuteNoRecords
or
CurrentDB.Execute "ALTER TABLE tblCity ADD COLUMN txtZip..."

Neither of these give warnings.
And they can return the number of records,
that were affected by running the query.


if interested, look up 'Execute' command in VBA help.

good luck!