dlookup on update query

[Darla]

Hi everyone! I am trying to accomplish something, and I'm not sure if an update query is the right way to do it?



I have a form with CheckoutID and Status as fields that are bound to the table tblSMP. I'm trying to run a query that updates the value of Status to "Yes" only on records in the table where CheckoutID is the same as what's on the currently loaded record in the form. I'm trying to add that criteria into the query and it doesn't find any records to update. Is this the wrong way to accomplish this? Is there a better way?

[CJ_London]

I'm trying to add that criteria into the query and it doesn't find any records to update

perhaps provide the code you are using to do this?

[Darla]

I know it's something dumb I'm missing.

UPDATE tblSMP SET tblSMP.Status = "Yes"
WHERE (((tblSMP.checkoutID)=[Forms]![frmCheck]![checkoutID]));

[AccessPower]

Dim strUpdate As String


strUpdate  = "UPDATE tblSMP Set Status='Yes' WHERE CheckoutID='" & Me.CheckoutID.Value & "';"
DoCMD.RunSQL strUpdate

Is this what you're looking for?

[CJ_London]

is the status field a yes/no field? yes/no fields are numeric.

if so then try

UPDATE tblSMP SET tblSMP.Status = Yes

[Darla]

It's not. It can be a number of things, the "yes" really makes no sense but it was like that when I got here

[Micron]

Sounds like the right way to do it.
Are you running this query from the nav pane when the form is open and a value is loaded into the textbox?
When you run the query, are there any prompts or messages?

[Darla]

Sounds like the right way to do it.
Are you running this query from the nav pane when the form is open and a value is loaded into the textbox?

I am, and there are no errors, just a blank datasheet

[Darla]

Dim strUpdate As String


strUpdate  = "UPDATE tblSMP Set Status='Yes' WHERE CheckoutID='" & Me.CheckoutID.Value & "';"
DoCMD.RunSQL strUpdate

Is this what you're looking for?

I get a data type mismatch in criteria expression error, and when I go to debug, the "DoCmd.RunSQL" row is highlighted.

[andy49]

try putting debug.print strupdate in the code before the problem line

You can then copy and paste the SQL into a query in SQL design mode to see what the issue is

[AccessPower]

Ahh.... Maybe try this. I removed the quotes from the CheckoutID criteria.

Dim strUpdate As String
strUpdate = "UPDATE tblSMP Set Status='Yes' WHERE CheckoutID=" & Me.CheckoutID.Value & ";"
DoCMD.RunSQL strUpdate

[Micron]

or place a value you know should be correct in your query statement instead of referring to the form control. If it works, something is wrong with the reference to the form control. Based on your first post, I figure this is not a control on a subform so I've avoided any suggestions for that (the reference would not be valid in that case).

[CJ_London]

Also something else to check - is the status field in the tblSMP table a lookup field? If yes, then you need to be using a different value from 'yes' based on whatever the bound column is.

[Darla]

Nope, not a lookup field. I just can't see the disconnect here.

[Darla]

Ahh.... Maybe try this. I removed the quotes from the CheckoutID criteria.

Dim strUpdate As String
strUpdate = "UPDATE tblSMP Set Status='Yes' WHERE CheckoutID=" & Me.CheckoutID.Value & ";"
DoCMD.RunSQL strUpdate

Perfect!!!! Thank you!!!!!!!!!!!!!