My brewery client normally uses b 1

[TrekBiker]

My brewery client normally uses barcode readers to feed data into the main database when processing orders. Sometimes the readers fail and they need some manual processes.

One of these is to update a table 'Casks Racked/Sold' that had been part completed previously with details of casks in stock, each having a unique CaskID.
Building an order leads to a new OrderDetailID for the cask to be used, and it's this that needs to be added to the Casks Racked/Sold table.

I'm using an intermediate form frmBookout to assemble all relevant details, including the Cask ID which appears on a label attached to the cask. To initiate booking out the cask this code asks the user to input the CaskID, assigning it to NewCaskID.

Private Sub cboUpdateDetails_Click()

On Error GoTo Err_cmdUpdateDetails_Click
   
    Dim NewCaskID As Long
    NewCaskID = InputBox("Enter Cask Number")
    
    DoCmd.RunSQL "UPDATE [Casks Racked/Sold] SET [OrderDetailID]=forms!frmBookOut.[OrderDetailID] WHERE [CaskID]=NewCaskID"

Exit_cmdUpdateDetails_Click:
    Exit Sub

Err_cmdUpdateDetails_Click:
    MsgBox Err.Description
    Resume Exit_cmdUpdateDetails_Click

End Sub

But this gives a prompt asking for the parameter value NewCaskID, which I was expecting it to already have.

 

[strongm]

UPDATE [Casks Racked/Sold] SET [OrderDetailID]=forms!frmBookOut.[OrderDetailID] WHERE [CaskID]=“ & NewCaskID

 

[TrekBiker]

Thanks Strongm, that nearly got me there, just asked for missing parameter "

But this worked
SQL = "UPDATE [Casks Racked/Sold] SET [OrderDetailID]=forms!frmBookOut.[OrderDetailID] WHERE [CaskID]= " & NewCaskID & ""

Excellent. Have a virtual pint of best Essex beer!

 

[Andrzejek]

To save you a lot of headache:

Dim NewCaskID As Long
NewCaskID = InputBox("Enter Cask Number")

strSQL = "UPDATE [Casks Racked/Sold] SET OrderDetailID = " & forms!frmBookOut.OrderDetailID & " WHERE CaskID = " & NewCaskID
[blue]
Debug.Print strSQL
[/blue]
DoCmd.RunSQL strSQL

BTW, you do not need [red]& ""[/red] at the end

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

In string [tt][CaskID]=“ & NewCaskID[/tt] the ASCII code of “ is 147, you need " with code 34. It is why copy-paste sometimes fails.


combo

 

[TrekBiker]

Thanks Andy and Combo, useful for my syntax learning process.

 

[strongm]

>the ASCII code

Mea culpa. I was using my phone to provide my correction and it looks like autocorrect saw fit to make that change without me noticing