Update query not pulling criteria from form

[Chris115]

Hi, i'm new to access so please bear with me!

I'm trying to update a record in a table, from a query that is run as part of an event from a command button on a form.

I have a table called 'Assets', a table called 'Disposals', and a form called 'Disposal Entry'. I would like the user to select an Asset ID from a combobox on the form, then when the button is clicked it adds a record to the 'Disposals' table, and updates the Status for that specific Asset in the 'Assets' table to "Disposed".

It adds to the Disposal table fine, but I can't get it to update the Asset table.

My query looks like:
Field: Status Asset ID
Table: Assets Assets
Update To: "Disposed"
Criteria: [Forms]![Disposals Entry]![Asset ID]

I’ve checked the spelling and everything looks ok.

The [Asset ID] control on the form is bound to the Assets table. If I edit the control and clear out what is in Control Source, then it updates the table and works fine.

However, I want to keep it bound as I have a subform on my home page showing the latest disposals.

Any ideas on how I can get the query to use the Asset ID on the form as the Criteria?

Thanks in advance.

Chris

 

[SOS]

Check to see that the combo box is returning the value you think it is. I wonder if it is returning an ID instead of the text value which you think it should. And, if so, you really be storing the statusID in the Assets table instead.

You can check to see what value you are getting from the combo by going to the VBA Window while the form is open and the combo has a selection, and then paste this into the IMMEDIATE WINDOW and hit enter.

?Forms![Disposals Entry]![Asset ID]

 

[Chris115]

Thanks for the tip, I've tried the printing the results in the immediate and window and it looks correct, as it returns: R001 (which is the Asset ID).

However, the Status field on the Assets table still doesn't update.
I'm expecting the Status to update to "Disposed", but it still says "1st line".

Could it be something wrong in the event on the command button?

 

[pr2-eugin]

If the control is bound, could it not simply be updated like..

Me.[[COLOR=black][FONT=Verdana]Status Asset ID[/FONT][/COLOR]] = "[COLOR=black][FONT=Verdana]Disposed[/FONT][/COLOR]"

Or am I missing something vital here?

 

[SOS]

Go to SQL View and then copy and paste the SQL string here. To get to SQL View go into the query in design view and then on the top left (I'm using 2007 so it may be slightly different for you based on version) and click the arrow below the spreadsheet icon that has the word VIEW below it. SQL View should show there.

 

[Chris115]

You are probably not missing anything as I'm still trying to get to grips with the basics.

Would the

Me.[Status Asset ID] = "Disposed"

code line go in my event on the command button? I've produced this using the macro builder, so not sure how/where to put it.

OnError
  Goto Next
  Macro Name
GoToRecord
  Object Type
  Object Name
  Record New
  Offset
OpenQuery
  Query Name Assets Query to Disposed
  View Datasheet
  Data Mode Edit
Close Window
  Object Type Form
  Object Name Disposals Entry
  Save Yes
Requery
Control Name

 

[Chris115]

My current SQL query looks like this:

UPDATE Assets SET Assets.Status = "Disposed"
WHERE (((Assets.[Asset ID])=[Forms]![Disposals Entry]![AssetID]));

I've tried replacing it with Me.[Status Asset ID] = "Disposed" but don't think I have the correct syntax?

 

[SOS]

Can't see anything wrong with that myself. I guess I'd use Paul's method if you can.

For Macros you would use the SetValue command.

 

[Chris115]

What does the Me. line do? How does it know how to update the 'Status' field in the 'Assets' table? No matter where I put it in the SQL in the query I can't get it to work.

I can't see a SetValue command in the Macro builder in the command button event, is it the SetLocalVar?

Apologies for having to be spoon fed on this, but you guys are helping me learn a lot!

 

[SOS]

What does the Me. line do? How does it know how to update the 'Status' field in the 'Assets' table? No matter where I put it in the SQL in the query I can't get it to work.

I can't see a SetValue command in the Macro builder in the command button event, is it the SetLocalVar?

Apologies for having to be spoon fed on this, but you guys are helping me learn a lot!

1. Me. is for VBA only, not macros. It refers to the current class object (form, report, or other class).

2. If you haven't clicked the Show All Actions button on the Ribbon you won't see it. Click that button and it should show up for you.

 

[JHB]

Try the below.

WHERE (((Assets.[Asset ID])=[Forms]![Disposals Entry]![AssetID].[Text]));

 

[Chris115]

Thanks JHB but when I change the query to add the .[Text], it comes up with the Enter Parameters Value box after I run the command button entry. (If I enter the asset ID in there, e.g. R001, it does then update the table, but I would like to do it without this input box).

I've converted the macro on the form command button to VBA.
I would like to update the 'Status' field in the 'Assets' table to "Disposed" where the 'Asset ID' in the 'Assets' table matches what is in a combobox control called 'Asset ID' on the form 'Disposal Entry'.

I've tried putting the Me.[Status Asset ID] = "Disposed" line in, but it didn't work. Do you know what is the correct command and where to put it?

 [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub Command40_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]On Error GoTo Command40_Click_Err[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]On Error Resume Next[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]DoCmd.GoToRecord , "", acNewRec[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]DoCmd.OpenQuery "Assets Query to Disposed", acViewNormal, acEdit[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Beep[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox "Disposal succesfully saved", vbInformation, "Disposal"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]DoCmd.Close acForm, "Disposals Entry"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]DoCmd.Requery ""[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If (MacroError <> 0) Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Beep[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox MacroError.Description, vbOKOnly, ""[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Command40_Click_Exit:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exit Sub[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Command40_Click_Err:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox Error$[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Resume Command40_Click_Exit[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

 

[Chris115]

Slight error in my last post, it doesn't come up with the Enter Parameter Value box after I corrected a typo!

However, it still doesn't update the Status field in the Assets table, even with the .[text] at the end of the SQL statement.

Am I missing something obvious? I've tried printing a msgbox at the start of the vba code to check waht is in [Forms]![Disposals Entry]![Asset ID], and it returns "R001".

If I hard code "R001" into the SQL statement it works ok, but everytime I change it back to read from the combobox the table doesn't update.

My SQL statement is:

UPDATE Assets SET Assets.Status = "Disposed"
WHERE (((Assets.[Asset ID])=[Forms]![Disposals Entry]![Asset ID]));

 

[pr2-eugin]

So is Asset ID type Text?

 

[Chris115]

Yes, Asset ID has a Data Type of Text in the 'Assets' table.

The control name (also called Asset ID) has
Control Source: Asset ID
Row Source: SELECT Assets.[Asset ID] FROM Assets;

But I can't see a format type on that actual combobox control?

 

[pr2-eugin]

Think there is some error, but it is hidden because of the On Error Resume Next statement.. Try..

Private Sub Command40_Click()
On Error GoTo Command40_Click_Err
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.OpenQuery "Assets Query to Disposed", acViewNormal, acEdit

    MsgBox "Disposal succesfully saved", vbInformation, "Disposal"
    
    DoCmd.Close acForm, "Disposals Entry"
    
    DoCmd.Requery
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
Command40_Click_Exit:
    Exit Sub
Command40_Click_Err:
    MsgBox Error$
    Resume Command40_Click_Exit
End Sub

You should be getting some error..

 

[Chris115]

Ok, I've tried your code but no error messages. It displays the message box saying "Disposal successfully saved" so think it must be getting that far. But it still doesn't update the table.

It must be trying to run the query as if I edit the SQL statement to hardcode in "R001" it definitiely updates the table.

I tried just putting a text box on the form and setting the value to "R001" and that works. Could it be something obvious?

 

[Chris115]

I moved the DoCmd.OpenQuery to above the On Error line and it's worked!!

Many thanks for you help. Am I correct in presuming that it was clearing the combobox when it was going to a new record, so was just the order in which I'd built my macro?