User entry loop VBA

[315hughes]

Morning all. Hopefully i can explain this issue clearly. In brief i have loop 1 with loop 2 occuring within it. Loop 2 has to continue until the correct value is input but if a incorrect value is input i want the vba to stop let the user enter a new value click ok to check and then continue to complete the rest of loop 1 if the value is correct. I can get this to kinda work by using the following vba for loop 2

Do
strLoc = InputBox("Please scan the location")
Loop Until strLoc = l

The issue i have with this though is that it is a pop up message box i would like this to occur on the form. Hopefully that is clear. below is my entire code anyway just incase

RUN THRU TIL BUTTON
Private Sub btnLocOK_Click()

Dim l As Integer


Dim strQuery As String
strQuery = "SELECT OrderID, ProdID, Quantity, ItemID FROM Item WHERE OrderID = " & Me.cboOrderID
Set db = CurrentDb
Set rs = db.OpenRecordset(strQuery)
While Not rs.EOF



o = rs!OrderID
p = rs!ProdID
q = rs!Quantity
item = rs!ItemID

For i = 1 To q

Me.txtOrder = ""
Me.txtProd = ""
Me.txtLoc = ""
strOrder = ""
strProd = ""
strLoc = ""


        'SQL to find the rack location with the oldest stock what is required for the order
        strOldestStockSQL = "SELECT [Location].RackID" & _
        " FROM Location" & _
        " WHERE [Location].ProdID = " & p & _
        " AND [Location].StockAge = " & _
        " (SELECT MIN(StockAge) FROM Location " & _
        " WHERE [Location].ProdID = " & p & ");"
        
        'takes the first rack location if thier are multiple locations
        l = CurrentDb.OpenRecordset(strOldestStockSQL).Collect(0)
        
        'PRINT VALUES ONTO FORM
        'ORDER
        strOrder = strOrder & o
        Forms!frmPickOrder!txtOrder = strOrder
        'PROD
        strProd = strProd & p
        Forms!frmPickOrder!txtProd = strProd
        'LOCATION
        strLoc = strLoc & l
        Forms!frmPickOrder!txtLoc = strLoc
        


'THE BELOW BIT HERE IS THE LOOP 2
'///////////////////
Do
strLoc = InputBox("Please scan the location")
Loop Until strLoc = l
'//////////////////

'MsgBox "Correct location scanned"

'Prevents any users going any further unless they scan the correct location

'At this point the pallet tag would now be re-written to match the order number
MsgBox "Please scan the pallet"
MsgBox "Tag changed"

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO tblAllocate ([ItemID], [RackID]) VALUES (" & item & ", " & l & ");"

DoCmd.RunSQL "UPDATE [Location] SET [Location].ProdID = 1 WHERE [Location].RackID =" & l
DoCmd.SetWarnings True
  Next i
  
      rs.MoveNext
      
   Wend
   
'When a order is completed the notes will be printed off in the office.
DoCmd.OpenReport "rptPickOrder", acViewPreview, , "OrderID like '*" & o & "*'"

'Completes the pick so it now will not display on the users screen

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE [Order] SET [Order].Picked = True WHERE [Order].OrderID =" & o
    DoCmd.SetWarnings True
    
'REFRESH ALL SCREENS AND DROP DOWNS

Me.cboOrderID.Requery
Me.cboOrderID.Value = Null
Me.txtOrder = ""
Me.txtProd = ""
Me.txtLoc = ""
strOrder = ""
strProd = ""
strLoc = ""

Thanks for any respnce in advance

[nico5038]

Nice requirement, but puzzled about the approach.
E.g. what would happen when a found product in the database isn't found at the recorded location ? And what to do when the qty isn't present ?

Personally I would create an Order form with the locations in a subform. Besides the subform I would have buttons like [Scan] [Record not found].
The Locations in the subform can be presented in descending order of storedate (or ascending StockAge).
You can check on forehand or the stored qty is sufficient for the order, by adding up the number of records on the subform.

Idea?

Nic;o)

[315hughes]

thanks for the responce nic. I would'nt want to offer the users a selection of locations to choose from as this is where stock can get out of date. Its a very linear approach im taking, im trying to remove all options from the end user. although to begin with ill have alot of work getting the database organised hopefully after a month everything sould be in the correct locations.
in your recomendation if i where to have a button next to the subform which was displaying thier current item of that order to pick it would still require a loop within a loop to check the correct location is scanned and then continue the rest of the order.
My appologies if ive got the wrong end of the stick

Kind Regards

[nico5038]

Hi 315hughes,

About your statement:
"I would'nt want to offer the users a selection of locations to choose from as this is where stock can get out of date."
Having a subform with all locations will still enable you to "force" working the products from eldest to newest, but also enables the "not found" to be handled.

I would have next to the subform two buttons:
1) Scan loc####
2) loc### not found
The "loc###" will dynamically hold the location of the first product and the subform is "read-only"
After scanning the refresh of the subform will let the scanned product disappear and the next product will show. This way you have the "nested" loop, but the user has control to scan or indicate the "not found". These "not found" records can be marked and listed in a separate report.

Nic;o)

[315hughes]

Thats for the responce Nic that is a very good idea. Put i might be narrow minded whn thinking about how to accomplish this. I will still have to start a recordset to get the details the belong to a order. These details need to go into a loop iterating through each part of the order until it is completed. But i would still need to have a loop in a loop where the interior second loop would need user input (enter location and click ok) and this is the bit i dont know how to code.

Thanks for your patience

Kind Regards

Rob

[315hughes]

thinking about it all i need is somthing like a wait(stop vba) until a button is clicked then carry on vba. As i said i can do this in a input box but not built into a form

Kind regards

[nico5038]

Hi Rob,

About " I will still have to start a recordset to get the details the belong to a order."
For defining an order I use an Order main form and a Details subform. (You might check this in the free Northwind.mdb that came with the old Access versions, or can be downloaded from the Microsoft site))
A Details subform will enable the data entry of multiple rows.
(No loop needed)

When the tblDetails table is related in the Relationships diagram by the OrderNumber, Access will automatically link the detail records to the mainform's OrderNumber and add that number when entering a new row.

Getting the idea ?

Nic;o)

[zmbd]

315hughes:
It would be helpfull (IMHO) to have a bit of backstory on your poject.
From what little I have been able to gather, it sounds to me like you are needing a "picker" or "fullfillme nt" form, these forms pull a set of products based on a customer's order, is this what you want?

The way you are "appearentl y" approaching this is by "jumping into the pool with both feet." This leads to messy, ineffecent, illogical code - at best.

SO:

Have you tried and put into 1 or 2 sentences each step involved?

Have you sat down with a diagram/flow chart to work thru this? I use >"nassi shneiderman diagram" Type diagrams.


Next I really think we can do your picker using a series of:
Form Filter and Cascasde:
From the Bytes > Sitemap > Microsoft Access / VBA Insights
42.Cascaded Form Filtering
156.Cascading Combo/List Boxes
168.Example Filtering on a Form.

[Rabbit]

I'm jumping in the middle here so I don't know all the intricacies involved in this thread. But instead of looping until the user enters a correct response, you should check right at the beginning of the function and exit if they haven't.