Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

My brewery client normally uses b 1

Status
Not open for further replies.

TrekBiker

Technical User
Nov 26, 2010
330
GB

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.

Code:
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.


 
UPDATE [Casks Racked/Sold] SET [OrderDetailID]=forms!frmBookOut.[OrderDetailID] WHERE [CaskID]=“ & NewCaskID
 
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!

 
To save you a lot of headache:

Code:
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
 
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
 

Thanks Andy and Combo, useful for my syntax learning process.
 
>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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top