Running query with parameter from Inputbox 2

[TrekBiker]

This is a follow up to a previous question, again exposing my lack of knowledge.

The first part below to update a table worked properly, thanks for that.

Dim NewCaskID As Long
NewCaskID = InputBox("Enter Cask Number")
strSQL = "UPDATE [Casks Racked/Sold] SET OrderDetailID = " & forms!frmBookOut.OrderDetailID & " WHERE CaskID = " & NewCaskID
Debug.Print strSQL
DoCmd.RunSQL strSQL

I now want to use NewCaskID from the InputBox as criterion for a query, to extract a related value GyleID.

The SELECT statement for the query in normal Design View is

SELECT [Casks Racked/Sold].GyleID, [Casks Racked/Sold].CaskID
FROM [Casks Racked/Sold];

It needs the condition " WHERE CaskID = " & NewCaskID

A final step would be to display this on the form initiating the whole procedure, in an unbound field [ExpectedGyle]

 

[dhookom]

How is the new query used as the recordsource of a form or report? [pre][/pre]

I would have placed a text box on your form for the user to enter or select the new cask ID. I don’t care for parameters or InputBox().

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Looks like your [tt]NewCaskID[/tt] already does exist in your [tt]Casks Racked/Sold [/tt]table (based on your Update statement), so I would NOT ask a user to type it (if you allow them to type, and you expect a valid NewCaskID number, what will stop them from typing: "Marry had a little lamb"?)

I would display what's available (in a combo...?) and allow user to Select a NewCaskID

---- Andy

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

 

[TrekBiker]

Thanks so much for responses, which make a lot of sense. It's getting to a time here where I'll be unpopular if continuing to work so will resume tomorrow, incorporating your advice. More when I've progressed.

 

[TrekBiker]

Andy & Duane, thanks again for your suggestions, which I've implemented.

When the barcode readers don't work they can now take a cask from stock for the required beer. The cask label shows the CaskID and what beer is inside. They enter the CaskID, with the combo checking this is already recorded in the Casks Racked/Sold table. This guards against trying 'Mary had a little lamb', thanks Andy!
After entering the CaskID a query looks up the expected GyleID, ie the batch number of the beer in thiis cask) as a check that this is what's on the cask label.

The Update Tables procedure then does this

Private Sub cboUpdateDetails_Click()

On Error GoTo Err_cmdUpdateDetails_Click

   'Check if Casks Assigned = 0, ie all items haven't yet been booked out    
    If Me.Remainder = 0 Then
        MsgBox "Already booked out", vbExclamation
        Exit Sub
    End If
   
    Dim NewCaskID As Long
   'Get CaskID from driver's note
    NewCaskID = Me.Cask

    Dim SQL As String
    
   'Turn off warnings so it doesn't keep asking if it's ok to update the three tables
    DoCmd.SetWarnings False
    
    SQL = "UPDATE [Casks Racked/Sold] SET [OrderDetailID]=forms!frmBookOut.[OrderDetailID] WHERE [CaskID]= " & NewCaskID & ""
    DoCmd.RunSQL SQL
    
    SQL = "UPDATE [Cask Population] SET [Cask Population].Status = 'Dispatch' WHERE [CaskID]= " & NewCaskID & ""
    DoCmd.RunSQL SQL
    
    SQL = "UPDATE [Order Details] SET [CasksAssigned] = [CasksAssigned]+1 WHERE [OrderDetailID]= " & OrderDetailID & ""
    DoCmd.RunSQL SQL
    
    SQL = "UPDATE [Casks Racked/Sold] SET [DateSold]=forms!frmBookOut.[ShipDate] WHERE [CaskID]= " & NewCaskID & ""
    DoCmd.RunSQL SQL
    
    DoCmd.SetWarnings True

   'Update the Booking Out form to show now done, ie Remainder has been reduced by 1
    Me.Requery

   'When done, show the updated Casks Racked/Sold table    
    DoCmd.OpenTable "Casks Racked/Sold"
    
Exit_cmdUpdateDetails_Click:
    Exit Sub

Err_cmdUpdateDetails_Click:
    MsgBox Err.Description
    Resume Exit_cmdUpdateDetails_Click

End Sub

This probably could be neater but it does the job. Thanks again.

 

[Andrzejek]

The very first field: "Enter the CaskID" - can they enter/type whatever they want? Or only numeric value is allowed? Or is it: "Select the CaskID"?

    ...
    SQL = "UPDATE [Casks Racked/Sold] SET " & _ 
          "     OrderDetailID = forms!frmBookOut.OrderDetailID " & _[blue]
          " AND DateSold      = forms!frmBookOut.ShipDate " & _[/blue]
          " WHERE CaskID      = " & NewCaskID 
    DoCmd.RunSQL SQL
    ...

You don't have to use [] if you do not use special characters or reserved words.

---- Andy

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

 

[TrekBiker]

The manual process starts by picking a cask with the required beer from all that are in stock.
The cask will have a numeric CaskID that they make a note of and either type into the new Book Out form or use the drop-down list to scroll through the candidates in stock, picking the one they want.
If they type in the number, which I suspect they will, the combo will only allow what's on the list, to prevent mistakes.
Are you concerned about terminology, ie select from the list only, or get it right if entering? Can I enforce selecting from the list?

 

[Andrzejek]

Can I enforce selecting from the list?

You can if you want to. Check the properties of your combo.
If that was me, it would depend of how many ID's I have to select from. If I only have a few, selecting from the drop-down short list is not a big deal. But if you have 100's, searching for it would be a nightmare - so I would allow typing.

---- Andy

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

 

[TrekBiker]

Thanks, there can be 10s of IDs so I'll allow typing and just check what's entered is in the list. Delighted with the full process, which will save the company a lot of manual updating.