Conditional Incrementing 1

[infomania]

I am creating an application for barcode scanning and I need to increment the box count when a new box is started (we might pack 10 items per box).

I have an unbound form in which the user enters the order number to get started. A subform (linked by [ORDER_NBR]) is bound to a data table. The box number is defaulted to 1 and the user starts scanning (item and quantity). After scanning the quantity, a new record is started. When the box is full, I want the user to click on a 'CLOSE BOX' button. The action desired is:
1. Go to the next record
2. Set Value of BOX_NBR to Max([BOX_NBR])+1 where Forms!Form!Subform![ORDER_NBR]=DataTable![ORDER_NBR]
3. Subsequent data entry would use this box number until the 'CLOSE BOX' button was clicked again. (i.e. the default for BOX_NBR should be Max([BOX_NBR]), conditional on the order number).

I have tried using a macro but I get a #Name? error.

Any help will be appreciated.

 

[Krystoff]

Hmm, let me see if I can help. These are also just suggestions and might not be what you want.

I am making an assumption that BOX_NBR is on the subform and gets placed in the table.

On the unbound part of your form make three text boxes. One is CurrentBox, then LastBox, and another is CountCurrentBox.

LastBox is: =DMax("BOX_NBR", "YourTableHere&quot

CountCurrentBox is: =DCount("BOX_NBR", "YourTableHere", "BOX_NBR = " & [LastBox])

CurrentBox is a little trickier in that it can have two values. If the form has just been open, it will default to whatever is in LastBox. Then the user can either choose to close the box by hitting the Close Box button or continue to use the Current Box. So what I would do is

CurrentBox at open of form is: =[LastBox]
CurrentBox after Close Box button is: =[LastBox] + 1

The code for Close Box would look something like this:
Me.CurrentBox.ControlSource = "=[LastBox]+1"

Now all you need to do is on the subform's Before Insert event place some code that makes the BOX_NBR = CurrentBox. Then changes CurrentBox back to the LastBox. The code I did looks like this.

Me.BOX_NBR = Me.Parent.CurrentBox
Me.Parent.CurrentBox.ControlSource = "=[LastBox]"
Me.Parent.Requery

This might be a little confusing but it appeared to work on the test DB I just created. I might not have the way you are entering things correct though which might make this a little different. Also, I would make the field BOX_NBR in the subform unenabled (Change the enabled property to NO) that way you are the only one that can make a change to the BOX_NBR.

If you have any questions on the above code or if someone else looks at my solution and says no way, I can do that better please let me know!

Chris

 

[infomania]

Thank you, Chris.

I havern't had a chance to try this but I will in a moment. One thing more... The box number is dependent on the customer order number. When a new order number is entered (where no corresponding data in the data table exists for the order number) the current box number should default to 1. If CurrentBox at open is : =[LastBox], how can I reset it to one?

When the parent form is opened, the order number field is blank (it has an unbound combo box with a query retrieving valid order numbers). When the order number is entered, data entered to date, if any, is shown in a subform (a second subform below the one that is used for the UPC scanning).

 

[infomania]

Thank you, Chris.

I haven't had a chance to try this but I will in a moment. One thing more... The box number is dependent on the customer order number. When a new order number is entered (where no corresponding data in the data table exists for the order number) the current box number should default to 1. If CurrentBox at open is : =[LastBox], how can I reset it to one?

When the parent form is opened, the order number field is blank (it has an unbound combo box with a query retrieving valid order numbers). When the order number is entered, data entered to date, if any, is shown in a subform (a second subform below the one that is used for the UPC scanning).

 

[Krystoff]

Hmm, ok let me see.

I know all of the Dmax and Dcount functions all have a criteria arguement. So we can use that to filter by the order number.

But before we do that, can you have BOX_NBR's that are duplicate? IE for order num 1 you can have box 1, 2, and 3 and for order num 2 you can have box 1, 2 and 3?

 

[infomania]

Yes, each order number starts with box number 1 and increments until the last box (could be 100+) is filled. I'll try the criteria such as:
=DMax("BOX_NBR","tblData","[tblData]![OrderNbr]=Forms![frmScan]![OrderNbr]&quot

That seems to do it. Now I just need some code to set LastBox to 1 if the order number does not in the table.

 

[Krystoff]

This code should work for you, let me know though.

Instead of just the DMax function in the textbox for LastBox, lets use an If statement to determine what to use.

=IIf(IsNull(DMax("BOX_NBR","tblData","[OrderNbr]=Forms![frmScan]![OrderNbr])),1,DMax("BOX_NBR","tblData","[OrderNbr]=Forms![frmScan]![OrderNbr]))

I know its a long statement but what it should do is when it checks to see if there is something in the table for that OrderNbr. If there isn't, then it uses 1. If there is, it uses the Max BOX_NBR. Simple yet effective.

Let me know how your coding is going.

Chris

 

[infomania]

Beautiful! I was just experimenting with the same expression and it works OK. I added an event procedure to the OrderNbr 'On Enter' to do a LastBox.Requery.

Cool!

Now, I'll put back the subform and go on with the design. Thank you for your helop with this tricky, for me, problem.

 

[Krystoff]

No problem, glad I could help!

Take care!

Chris