Trouble with adding new records in Rental System

[Guest]

I currently have a FORM that, using combo boxes, enables the user to select a Inventory Item from the Inventory Table (using the Lookup function, this combo box selects the Inventory ID and corresponding Inventory Name), while also allowing the user to select a Renter (using a combo box, the user selects the RenterID and Renter Name). I then have a button which records this information into a table called tblTransaction. No problem there

The problem arises as when the user presses the Add New Record button, I also want a field in the Inventory Table called Rented (yes or No) to be checked based on the Inventory Name value selected from the combo box. So basically, this button records the transaction in the tblTransaction, but also need it to check off the Rented field in the tblInventory based on the Inventory Name selected from the combo box

I tried the following

Private Sub cmdRentInventory_Click(
On Error GoTo Err_cmdRentInventory_Clic


DoCmd.GoToRecord , , acNewRe

InventoryID = InventoryName.Valu

DoCmd.RunSQL "UPDATE tblINVENTORY SET rented = true WHERE InventoryID = ' InventoryName.Value '" ' InventoryName is the name of the combo box

Exit_cmdRentInventory_Click
Exit Su

Err_cmdRentInventory_Click
MsgBox Err.Descriptio
Resume Exit_cmdRentInventory_Clic

End Su

Any ideas??

 

[John Nurick]

So what happens when you try it?

I currently have a FORM that, using combo boxes, enables the user to select a Inventory Item from the Inventory Table (using the Lookup function, this combo box selects the Inventory ID and corresponding Inventory Name), while also allowing the user to select a Renter (using a combo box, the user selects the RenterID and Renter Name). I then have a button which records this information into a table called tblTransaction. No problem there.

The problem arises as when the user presses the Add New Record button, I also want a field in the Inventory Table called Rented (yes or No) to be checked based on the Inventory Name value selected from the combo box. So basically, this button records the transaction in the tblTransaction, but also need it to check off the Rented field in the tblInventory based on the Inventory Name selected from the combo box.

I tried the following:

Private Sub cmdRentInventory_Click()
On Error GoTo Err_cmdRentInventory_Click



DoCmd.GoToRecord , , acNewRec

InventoryID = InventoryName.Value

DoCmd.RunSQL "UPDATE tblINVENTORY SET rented = true WHERE InventoryID = ' InventoryName.Value '" ' InventoryName is the name of the combo box)

Exit_cmdRentInventory_Click:
Exit Sub

Err_cmdRentInventory_Click:
MsgBox Err.Description
Resume Exit_cmdRentInventory_Click

End Sub


Any ideas??