VBA to update filed in tbl, please help with syntax

[mhshane]

I can't seem to get this one to work. I have looked all over the web, found many examples but can't get the syntax right for some reason. Please help.

When I click a button on a particular form, VBA code runs to update several fields on the table behind the form (tblComm).

Private Sub TermUser_Click()

'Add a string variable,
Dim strUN As String

'set the string variable to the contents of the text boxes
strUN = Me![First Name] & " " & Me![Last Name]

'Copy First and Last Name of user to the text box Former User
[Former Employee] = strUN

'clear First Name text box
[First Name] = ""

'Change Last Name text box to Open
[Last Name] = "Open"

[DirectoryYN] = No

End Sub


Now I am trying to add another line that will also update the Status column in another table (tblEmployee) to change the status the employee to "Terminated" using the Employee's ID (EMPLID)

CurrentDb.Execute "UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' " _
"WHERE tblEmployee.[EMPLID] = " & Me![EMPLID]

The VBA worked fine till I added this line.
Now I get a compile error.

I'm sure it is a syntax problem but I'm following the examples from Microsoft. Apparently not well enough though.

Any suggestions would be greatly appreciated.

Thanks.

 

[dhookom]

Try add an ampersand:

CurrentDb.Execute "UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' "[COLOR=#EF2929] &[/color] _
 "WHERE tblEmployee.[EMPLID] = " & Me![EMPLID]

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

In addition to dhookom's suggestion, I would recomend:

Dim strSQL as String
...

strSQL =  "UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' " & _
 "WHERE tblEmployee.[EMPLID] = " & Me![EMPLID] 

Debug.Print strSQL 

CurrentDb.Execute strSQL

This way you can see what you send to your DB

Have fun.

---- Andy

 

[mhshane]

Thanks for the help. That did get me further. Now I get:
Run-time error '3464'

Data type mismatch in criteria expression

Not sure what is a mismatch.
tblEmployee.[Status] is a text field and I am putting the word Terminated in quotes.

For grins I changed the code a little to make it all one line like this:
CurrentDb.Execute "UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' WHERE tblEmployee.[EMPLID] = " & Me![EMPLID]

I still get the Error 3464.


If I run this command:
MsgBox "UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' WHERE tblEmployee.[EMPLID] = " & Me![EMPLID]

Result: UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' WHERE tblEmployee.[EMPLID] = 20609212

So it is reading Me![EMPLID] properly.

 

[PHV]

Perhaps this ?
CurrentDb.Execute "UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' WHERE tblEmployee.[EMPLID] = [!]'[/!]" & Me![EMPLID] & "[!]'[/!]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mhshane]

Much appreciated Andrzejek.

Here is my new code:
Private Sub TermUser_Click()

'Add a string variable,
Dim strUN As String
Dim strSQL As String

'set the string variable to the contents of the text boxes
strUN = Me![First Name] & " " & Me![Last Name]
strSQL = "UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' " & _
"WHERE tblEmployee.[EMPLID] = " & Me![EMPLID]

'View and Run update on tbl Employee
Debug.Print strSQL

CurrentDb.Execute strSQL

'Copy First and Last Name of user to the text box Former User
[Former Employee] = strUN

'clear First Name text box
[First Name] = ""

'Change Last Name text box to Open
[Last Name] = "Open"

[DirectoryYN] = No

End Sub

I still get
Run-time error '3464'
Data type mismatch in criteria expression

Debug highlights [highlight #FCE94F]CurrentDb.Execute strSQL[/highlight]

So something in updating tblEmployee is a mismatch.
Every field in the table is a text field except [Termination Date]
That leads me to believe the problem lies with EMPLID.
I'm asking the system to Update Status to "Terminated" based on EMPLID.
EMPLID is a number in that its all numbers like 123456 but it is treated as text in my table since it has no mathematical value.
I believe my VBA code is treating Me![EMPLID] as a number format thus the error 3464.

So here is my next question and what I am googling now:
How do I make EMPLID be read as TEXT in the following VBA line of code?
strSQL = "UPDATE tblEmployee SET tblEmployee.[Status] = 'Terminated' " & _
"WHERE tblEmployee.[EMPLID] = " & Me![EMPLID]

Thank you for all the help.

 

[dhookom]

I believe PH has hit the nail on the head as usual. Always provide data types of significant fields when you have a query question.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

EMPLID is a number in that its all numbers like 123456 but it is treated as text in my table since it has no mathematical value.

If it is a Number, I would declare it in my tables as a Number. And your SQL will work OK.

But since it is a Text, follow PHV's suggestion and use single quotes around Me![EMPLID] so you will end up with:

[tt]UPDATE tblEmployee
SET tblEmployee.[Status] = 'Terminated'
WHERE tblEmployee.[EMPLID] = [red]'[/red]20609212[red]'[/red]

[/tt]

Have fun.

---- Andy

 

[mhshane]

That was it! Missing the text quotes for EMPLID. Thank you PHV!!

I did include the data type for [Status] but at the time I started the post I didn't think [EMPLID] was a factor. Sorry. Lesson learned on including types for all fields involved.

Thank you to everyone for your help. Have a great day.