How to get scanned data into my forms?

[DanicaDear]

Background: We test and send out dielectric rubber gloves to customers in fancy boxes and they are to return the boxes for the next customer's shipment. We like to track the boxes so we can know which boxes are missing, if any.

Form frmOrders
CUST_NUM, text, will have data in it already
ORDER_NUM, text, will have data in it already
SHIP_DATE, date/time, will have data in it already
BOX_NUM_1, BOX_NUM_2, ….BOX_NUM_10, text
RETURN_1, RETURN_2, ….RETURN_10, yes/no

Each shipping box will be equipped with an address card which has has CUST_NUM barcode on it. Each box will also have a barcode for the BOX_NUM_X. We'll be doing two things with our boxes: 1. Shipping. 2. Receiving.

Other helpful info:
-- No customer has ever had an order so large he used all 10 boxes. It’s usually 3 or 4.
--BOX_NUM will have 3 or 4 digits numeric digits.
--CUST_NUM will have 5 numeric digits.
--If you aren't familiar with a scanner, it basically works like a keyboard without having to type keys. It will enter an "ENTER" at the end of each scan.

Shipping
1. I've somehow got to tell Access I'm about to enter some information and Access needs to know where to put it. That's my first major hurdle because I don't have any idea where to begin.
2. Scan barcode. Check number of digits. It is 5 digits. It knows it has just read a CUST_NUM. (The user should always scan the CUST_NUM first.)
3. Look for that CUST_NUM's LastOrder. This might be a start. (thanks topher23, previous post)

Dim LastOrder As Long
 
    LastOrder = Nz(DMax("ORDER_NUM", "tblOrders", "CUST_NUM='" & Me.CUST_NUM & "' AND ORDER_NUM < '" & Me.ORDER_NUM & "'"), 0)

4. Scan another number. Check number of digits. It is <5. Access knows it has just read a BOX_NUM. Put the number it just read into BOX_NUM_1 of the LastOrder called up in the last step. Repeat Step 4 (BOX_NUM_2 thru 10) until another 5 digit number is scanned, then start over with Step 1. (At this point Access will have to go to a new record because that's a new customer??)
5. Continue until user goes back to computer and tells Access he is done (btnStopScanning, Escape, etc)

Here is an example of what I'll be scanning, just for reference. (To simulate scanner with keyboard, type first number, enter, second number, enter, again and again)

10001     826     10001    1028    10001    1246    10005    1054   10005    896    10008    890

The purpose is to get these box numbers into the forms/tables so a box number is linked to an order number which is related to the customer number.

Receiving
1. A BOX_NUM has previously been assigned to a customer in Shipping.
2. Scan the box number. Access should see it is checked out because that value is in a BOX_NUM_1 (1 used for example, could be any value 1-10) with RETURN_1 = No. Each record has a BOX_NUM_1 but only one will contain the value just scanned.
3. Set RETURN_1 = Yes for that BOX_NUM_1 with value matching what was just scanned.
The purpose is to give the customer credit for returning the box.

I’m sure my steps are oversimplified. But I'm trying to contribute and show I am thinking and not just dumping questions on Bytes that have no thought put into how they can be accomplished. The steps are just my idea. Someone else may have a different or better idea (and I'm sure you will, lol). I'm open to all ideas, nothing is off the table, even redesigning tables, etc.

If you can help me or advise me on how I can accomplish what I want to do, please feel free. Thanks in advance.

[TheSmileyCoder]

That certainly sounds like an interesting challenge. You have given a very good description of what happens and what you want to happen.

The part im unfamiliar with is how the scanner works as input. If I understand your description correct, access will interpret the scanner as if it was a keyboard entering the numbers?

So that means the whole form should be about catching "keystrokes" which is perfectly possible, but before I go down this path, I just want to make sure about the above question.

Edit: It would also be possible for a user to scan 1 customer and then 5 boxes without scanning a customer?

Edit: When a box is reused will it always go to the same customer? Will it get a new Customer barcode and a new box barcode?

[DanicaDear]

If I understand your description correct, access will interpret the scanner as if it was a keyboard entering the numbers? Yes, that is correct. You can program them to accept "Enter" or "Tab" after the each reading. Right now it's set up to "Enter."

It would also be possible for a user to scan 1 customer and then 5 boxes without scanning a customer? Yes, this would be totally possible and we considered this. However, we will ship and receive multiple customer boxes at once. If one customer's box got in with another customer's while we were entering, and we didn't scan the customer number each time, then the wrong box might get assigned to the wrong customer. So this was a safety check. Our processes are negotiable, especially if it makes a big impact on the ease of software development.

When a box is reused will it always go to the same customer? Will it get a new Customer barcode and a new box barcode? No, the boxes will go to multiple customers. Customers reorder gloves every 90 days so we use the box for a customer, get it back about a week later, use it on another customer, get it back a week later, etc. A particular customer does not get the same boxes. It's random.
The customer barcode is their customer account number. That does not change for a customer. We have index cards that slide in and out of the boxes and that allows us to put a new customer into the box for shipment. That is how a new CUST_NUM would be assigned to a BOX_NUM.
The box's barcode also does not change. The BOX_NUM barcode is somewhat permanently stuck to the box. The only thing that changes are the assignment of the boxes to the customers.

I appreciate you being willing to look at this. This is the climax of my DB writing for the 3 programs I am responsible for developing. I've been knowing this was coming for a year and if I can't get this to work there will be major disappointment.
I appreciate you noting my question was well documented. It only took me 5 hours to compose. LOL!!

[TheSmileyCoder]

A well thought out and described question can often make the difference between someone like me taking the time to go the extra mile, or just dismissing the question with a "If he doesnt care enough to describe his problem, why should I care enough to help him solve it". This is certainly not a "simple question, simple answer thing". I will look into it.

[TheSmileyCoder]

This is what I could come up with. It handles the shipping part of the boxes, but I am confident that you can work out a receiving part based on the code in here.

The form to be used is called frm_Shipping, and I've made comments in my code. If anything is unclear, please dont be afraid to ask.

[NeoPa]

If you had a main input control on your form that the bar-code reader would populate, you have some AfterUpdate code which :

1. Determined which type of bar-code is being presented. Five characters would imply a customer number . Anything less a box number.
2. If a customer number, then the current data in the form would be cleared down (and any potential final processing of any possible previous customer executed).
3. The data would then be moved to a TextBox for the customer number.
4. If a box number, then the number would be moved to the next available TextBox for the box numbers.
5. You may want to provide a more manual mechanism for retracing your steps in case of operator error. Some sort of box and/or customer reset.

Clearly the code would be responsible for apportionning the data to the right places, but the form would be very easy to use - at least as far as the data entered via the bar-code reader was concerned.

PS. I haven't looked at Smiley's suggestion, so I cannot tell if there is any overlap, but this is my thinking on the matter, put forward as a potential alternative, not as a better solution or indicating there may be anything wrong with what has already been proposed.

[DanicaDear]

Smiley, I like what you've done here. I've been studying your code for a couple hours. I can't read code like a newspaper so it takes a while for it to sink in. But I'm totally on board. I like how you have the Box Numbers in individial records rather than having Box_num_1, 2, 3 like I currently have. I'm going to move in your direction on that. The only thing I initially see is my LastOrder (Max order number for a customer number) not going in. I'm going to try to play around with what I posted above. I don't know where it should go in your code but if I can't get it then I'll let you know. I don't want to ask until I've at least tried it out myself. Thanks very much for your handy solution. I hope I can get this working in my DB.

[DanicaDear]

In the following code, I see Case 4 and Case 5. I want "Case 4" to say "Case 3 or 4" I tried copying the Case 4 code and repasting it with the "4" changed to a "3". That didn't work out too good. How can I account for the 3 digit boxes?
(I know I have to change the "4" on line 3 to a "3".)

If KeyAscii = 13 Then
        'Scan is complete (we hope)
        If Len(Me.tb_ScanID) < 4 Or Len(Me.tb_ScanID) > 5 Then
            'Some sort of error or user error
            MsgBox "Input error, resetting"
            GoTo doClear
        End If
 
        Select Case Len(Me.tb_ScanID)
            Case 4
                'Box
                If Not bCustomerScanned Then
                    MsgBox "A customer ID must be scanned first before scanning boxes"
                    GoTo doClear
                End If
                'Is box registered in database?
                If DCount("*", "tbl_Box", "KEY_Box=" & Me.tb_ScanID) = 0 Then
                    'Box does not exist in DB
                    MsgBox "Box " & Me.tb_ScanID & " not recognized in tool"
                    GoTo doClear
                Else
                    'Box exists.
 
                    'Assign box to current customer, set shipping date=now, and received date to null and bMissing to false
                    Dim strSQL As String
                    strSQL = "UPDATE tbl_Box SET tbl_Box.ID_Customer = " & Me.tb_KEY_Customer & ", tbl_Box.dt_Shipped = Now(), tbl_Box.dt_Received = Null, tbl_Box.bMissing = False" & _
                            " WHERE (((tbl_Box.KEY_Box)=" & Me.tb_ScanID & "));"
                    DoCmd.SetWarnings (False)
                        DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings (True)
                    Me.subFrm_Boxes.Requery
                    GoTo doClear
 
 
                End If
 
            Case 5
                'Customer
                'Lets find customer entered
                Me.Recordset.FindFirst "KEY_Customer=" & Me.tb_ScanID
                If Me.Recordset.NoMatch Then
                    MsgBox "Customer barcode not recognized"
                    'Do whatevter you want to handle this case
                End If
                bCustomerScanned = True
                GoTo doClear
        End Select
    End If

[DanicaDear]

Oh my good grief my head is about to explode. LOL. Sometimes you have to just sit back and laugh at yourself.

I have been trying for 8 hours now to get this going...first studying, then doing. I know I must be getting close. My first step is to get this working, then begin changing what I want to. I have all my forms and tables set up similar to the example provided. However, when I try to scan, the customer number will not update on my version. :-( One big question is have is this: At the top of the code, I see this:

Option Compare Database
Option Explicit
Private bCustomerScanned As Boolean

I can't figure out what bCustomerScanned is or where it is located. Smiley's properties sheet on the Shipping Form made reference to a module. I can't find a module anywhere. It is possible that this alone is my problem but I'm not betting that I won't have more questions.

[TheSmileyCoder]

A module can actually mean 2 things. A module can be attached to a form, and if your looking at the shipping form it has a properties: HasModule=True, which basicly means that the form has code attached (As opposed to nothing or macros). A forms module can only be accessed by the form itself, and thus the code in the form is per default private. Think of a form with code, as a page in a book, with scriblings on the backside :P You can also have standanlone modules, where you put code you use alot, or put code you need to access from within several different forms, or perhaps in querys.

The bCustomerScanned was something I put into the form, because when you first open the form, it will be on the first customer, so I wanted to ensure that a customer had been scanned before allowing a box scan to happen. There are more elegant ways of doing this though, but I didn't want to get into those at the moment. The variable is used during the Case 5.

Now about the case 3 or 4, the problem is that computers are a bit more literal then we are. Im guessing you have written
Case 3 or 4
That has to be written: Case 3,4
The comparision must evaluate to TRUE. 4 is not an evaluation and 4 is not by itself TRUE.
Its kinde like writing:
X=1 or 2 or 3 or 4
which Access wont like. What access can understand is a series of expressions which each individually can be evaluated to either true or false. Example:
X=1 or X=2 or X=3 or X=4
Now the notation for SELECT CASE is bit different, so there you write:
SELECT CASE 3,4.

Sorry for typos, its really late here, I just wanted to get this off, before going to bed. :)

[NeoPa]

Smiley has your Case answer well explained. I would add that for this particular situation you may choose to say write it as Case 5 followed by the rest, which would be Case Else.

If you put the cursor on the word Case and press F1 for Context-sensitive help, you will get a pretty good explanation of all your options within the Select Case Statement. This is a worthwhile option to use for handling multiple different options. Well worth your while learning about.

[DanicaDear]

Im guessing you have written "Case 3 or 4" 

How did you know? LOL. Thanks for the explanations. They are easy to understand. I will make this change when I get the form actually working and scanning.

I have been trying really, really hard to get this to work. I think I am very close. But I can't find my error(s). Two things bothering me. (I'm sure there are more but two are obvious.)

1. In the code below, lines 4-12 are what I did to try to make Order_Num show only the max Order_Num for each customer in the form. It isn't working...I assume it's in the wrong place (I have tried many) or in the wrong format. The form will at least load. :-)

2. The DB that Smiley provided for me scanned items exactly like I was wanting it to. When I open my form (frmBOX_SHIPPING), the scanner reads the Cust_Num but it doesn't update the form to show the Cust_Num just scanned. It isn't updating correctly. I've spent 6 hours trying to find out why and as usual when I type these questions out on Bytes I get some more ideas. I just noted that Smiley's code was based on fields that were mostly number types. My fields are mostly text types. (The only field of mine in the pasted code below that is a number type is "Missing". All the rest are text.) Those ridiculous little tick marks are getting me again!!!!! Of course, it might be worse than that.... I'm willing to try to do to tick marks but I'm afraid I'll muck it up so badly you won't be able to make heads or tails of it. I'll try that on my own without showing the world my stupidity. If it works, I'll report back. LOL.

Option Compare Database
Option Explicit
Private bCustomerScanned As Boolean
Private Sub Form_Open(Cancel As Integer)
Dim varLastOrderNumForCust As Variant
 
 
'Last/MAX Order Number for Customer
varLastOrderNumForCust = DMax("[ORDER_NUM]", "tblOrders", "[CUST_NUM] = '" & Me![CUST_NUM] & "'")
 
 
End Sub
 
Private Sub Form_KeyPress(KeyAscii As Integer)
    'A key has been pressed.
 
 
    'If key pressed is numeric, add it to the ID
        If IsNumeric(Chr(KeyAscii)) Then
            'Concatenate it to the end of the ID, and then convert _
back to long (In theory you could keep it as a string all the time I guess)
            Me.tb_Scan_Cust_Num = Me.tb_Scan_Cust_Num & _
Chr(KeyAscii)
            Exit Sub
        End If
 
 
 
    If KeyAscii = 13 Then
        'Scan is complete (we hope)
        If Len(Me.tb_Scan_Cust_Num) < 4 Or Len(Me.tb_Scan_Cust_Num) > 5 Then
            'Some sort of error or user error
            MsgBox "Input error, resetting"
            GoTo doClear
        End If
 
        Select Case Len(Me.tb_Scan_Cust_Num)
            Case 4
                'Box
                If Not bCustomerScanned Then
                    MsgBox "A customer ID must be scanned first _
before scanning boxes"
                    GoTo doClear
                End If
                'Is box registered in database?
                If DCount("*", "tblBOX", "BOX_NUM=" & Me.tb_Scan_Cust_Num) = 0 Then
                    'Box does not exist in DB
                    MsgBox "Box " & Me.tb_Scan_Cust_Num & " not recognized in tool"
                    GoTo doClear
                Else
                    'Box exists.
 
                    'Assign box to current customer, set shipping date=now, and _
received date to null and bMissing to false
                    Dim strSQL As String
                    strSQL = "UPDATE tblBOX SET tblBOX.CUST_NUM = " & _
Me.tbCUST_NUM & ", tblBOX.DATE_BOX_SHIP = Date(), _
tblBOX.DATE_BOX_RETURN = Null, tblBOX.Missing = False" & _
                            " WHERE (((tblBOX.BOX_NUM)=" & Me.tb_Scan_Cust_Num & "));"
                    DoCmd.SetWarnings (False)
                        DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings (True)
                    Me.subFrm_Boxes.Requery
                    GoTo doClear
 
 
                End If
 
            Case 5
                'Customer
                'Lets find customer entered
                Me.Recordset.FindFirst "CUST_NUM=" & Me.tb_Scan_Cust_Num
                If Me.Recordset.NoMatch Then
                    MsgBox "Customer number not recognized"
                    'Do whatevter you want to handle this case
                End If
                bCustomerScanned = True
                GoTo doClear
        End Select
    End If
 
 
 
 
Exit Sub
doClear:
    Me.tb_Scan_Cust_Num = ""
    Me.tb_FocusKeeper.SetFocus
 
End Sub
 
 
Private Sub Form_Load()
    Me.tb_FocusKeeper.SetFocus 'Access must have an active _
textbox in order to properly recieve keypress _
(At least I couldnt get it to work without)
    bCustomerScanned = False
End Sub

One final note: I have made some somewhat serious changes to my DB to try to get this to work, including table changes, new forms, queries, the whole bit. Just want ya'll to know this is not just a copy/paste forum for me where I lack thought of my own. I do rely heavily on your expertise but it is not because I'm lazy. My DB and my skills are evolving as we go. Thanks to you all. :-)

[TheSmileyCoder]

            Case 5 
                'Customer 
                'Lets find customer entered 
                Me.Recordset.FindFirst "CUST_NUM='" & Me.tb_Scan_Cust_Num &"'"
                If Me.Recordset.NoMatch Then 
                    MsgBox "Customer number not recognized" 
                    'Do whatevter you want to handle this case 
                    bCustomerScanned = False
                Else
                  bCustomerScanned = True                   
                End If 
 
                GoTo doClear 

I added the ' where appropriate, and moved the bCustomerScanned a bit around as well. If CUST_NUM is NOT a numeric type field, then yes, you need the tickmarks around it to make sure access understands it is a string.

[NeoPa]

Those ridiculous little tick marks are getting me again!!!!!

Check out Quotes (') and Double-Quotes (") - Where and When to use them.

[topher23]

Danica - I looked at what you had put together and realized that this was very similar to somthing I built for one of my DB's. An issue we had to address was "What if the scanner breaks? How would the operator know what was happening?" The changes I've made address that by using a text box to capture the data rather than using a keypress capture on the form. The form can be operated both using keyed entry and scanned entry with equal functionality.

[DanicaDear]

topher23,
I appreciate your input. Thanks for your time; your point is well taken and very valid.
I have been so completely stumped over this silly thing. 30 hours later it turns out I had my "Key Preview" in the properties set to "No" and when I set it to "Yes" everything worked better. I still had to deal with the ticks and quotes. I had to read a book first. So here I am...rocking and rolling again. :-)

Thanks!!

[DanicaDear]

topher23,
I'm really impressed with what you did. You don't know how much I appreciate it. I can see you worked hard on this as all the things mentioned in the post were addressed in your version. You are amazing! It took me 30 hours to get mine running not even as good as what you have proposed. LOL (Why I'm laughing I have no idea. I bit off all my fingernails and pulled out my eyebrows during those 30 hours.)

With that said, I do have one question. On the form, it has a list of "all" the boxes and the scanner/keyboard updates those fields as necessary. The problem is in the real DB we will have about 200 boxes (with no more than 10 assigned to a single customer), so there's no way they can all show on the form in the way that it is set up now. I tried to fix this by linking Master/Child fields, but I got a warning that it "can't build a link between unbound forms." I am unfamiliar with unbound forms but will begin studying that now. In the meantime, is there a way that I can only show the boxes that I scan, instead of "all" the boxes? For example, scan customer 10000...the max order number pops up, perfect. The subform containing the box numbers would be empty. Then scan box number 888. Box number 888 shows up on the subform. Scan customer 10000 again. Box number 888 is still there, and add box number 840. Both boxes show on the subform, add another...until the customer number is changed. When customer 11111 is entered, the subform clears and is ready for more box numbers. This would be similar to the DB uploaded by TheSmileyOne, but with all the changes you have proposed. Is this possible?

Thanks in advance!

[DanicaDear]

I am happy to report that becuase of Smiley's and Topher's excellent examples here, I have been able to create my BOX_RECEIVING form. It is currently running. I thank you all...the most difficult peice to my puzzle has been solved!

I do still have the issue mentioned in the last post--the subform shows too many fields and it becomes user unfriendly. However, I think this will be easy in comparison to the other things we have accomplished in this post. If you can give me any pointers on this front, please chime in. :-)

[NeoPa]

I'm not sure I have your problem properly understood, but I would guess that whenever a customer is scanned in, the form needs to access the database (.Requery) to load all the related boxes already stored against that customer before proceeding.

[DanicaDear]

Hi NeoPa...didn't expect to see you (or anyone) on a Sunday. What a treat! :-)
I already have things set to .Requery. Let me try to re-state the problem, clearer.

I have a subform that shows every box in my DB. When new boxes are scanned, it overwrites the old data (customer number and date shipped) attatched to the box and requeries. The box numbers show up on the subform in numeric order. Because I'll have about 200 boxes, and my computer screen is only so big, I can only see the first 15-or-so box numbers. So when I scan new data into box number 145 for example, I can't see the data going in. What I would like to do is instead of having all the box numbers showing on the subform is have it clear each time a new 5-digit customer number is scanned. The procedure, in my mind, would go something like this:
Open form+subform. It shows no records.
Scan customer 10001.
Scan box 110.
Those two items now show on the subform.
Scan customer 10001.
Scan box 111.
These two fields add to the one previous, because the customer number is stll the same.
Scan customer 10005.
Scan box 115.
The subform no longer shows the previous two records but instead shows the new one, becuase it's a new customer.
In order to keep the focus (if my terminology is correct) in the correct field and ready for the next scan, subform properties are set to Enabled-No and Locked-Yes. When changing those properties and trying to "fix it" my scanner field lost focus and things got worse.

Opening the DB may take too much of your time, but if you desire to, here is pertinent info: If you open the DB in post #15 and go to frmBOX_SHIPPING you can see the subform. If you want to see it working, as soon as you open the form, type this:
10001, Enter, 110, Enter
10001, Enter, 111, Enter
10005, Enter, 115, Enter.

If I'm still not clear, let me know. :-)

[NeoPa]

When I followed those instructions all boxes showed in the subform at all times.

I got an error message "Box 115 not recognised in tool" when I entered that data, but no change to the data in the subform that I noticed.

Feel free to give me a shout if you see me online.

By the way, this strategy seems very reminiscent of that I suggested in post #6, except that multiple boxes are not allowed after a single customer number. I would still suggest that slight change, for extra usability (unless you feel this may be a problem for the operators), but otherwise this seems a good interface.

[DanicaDear]

Hi NeoPa,
I have my form currently setup like you recommended in Post 6 (if I'm interpreting your instructions correctly). The problem is I don't know how to do what you recommended in Step B, and in fact that is the very question I'm trying to get answered.

Not sure why you are getting that error. Maybe try 112 instead (or just don't worry about it, it seems you see the problem from what you've done already). Perhaps I added box numbers to my DB since I downloaded Topher's sample.

NeoPa wrote: When I followed those instructions all boxes showed in the subform at all times.
Yes, that's what I'm trying to change. Don't want to see *all* the boxes.

NeoPa wrote: Feel free to give me a shout if you see me online.
Is there a way to chat on here? If there is you might regret telling me about it. hahaha. J/K. How is the best way to give you a shout?

[NeoPa]

DanicaDear:
NeoPa wrote: Feel free to give me a shout if you see me online.
Is there a way to chat on here? If there is you might regret telling me about it. hahaha. J/K. How is the best way to give you a shout?

I'm in a rush now, but we have already chatted on Skype from the time of a previous project if you recall. If you don't recall my details then get in touch by PM and I will send them to you again.

PS. I doubt I'll regret talking with you. You seemed very personable and intelligent last time we chatted, and I can't imagine why that would have changed in such a short time ;)

PPS. Reply to this (or PM) to trigger my attempt to fill out point B a little more clearly. I'm afraid I'm rushing to get away just now.

[DanicaDear]

@NeoPa
Trigger. LOL.

[NeoPa]

First of all, the subform needs to be designed to link (See Link Master Fields & Link Child Fields) to the Customer number control of the main form. That way, only those matching boxes would be shown. A .Requery would probably (test first) be required whenever a new box were added to the customer.

Alternatively (I don't have your database available here to check), if you are using a single form (no subform) approach instead, you would apply the filter to the current form whenever the Customer were scanned in.

Let me know if you can make progress with this. The next time I can look at your database properly will probably be tomorrow evening (UK time).

[DanicaDear]

Well this won't be the first time I've made a mountain out of a mole hill. I figured this one out. Although I had tried before to Link Master and Child Fields, I always got (and still get) an error "Can't build a link between unbound forms." It was the main form that was unbound, not the subform. When I typed in the field names I was trying to link, it worked!!! Every time before I had merely tried to click the "..." button out to the side, and it always gave me an error. This problem is solved and this case is closed!!! Thanks to EVERYONE! I'm thrilled!!!!!

[NeoPa]

No need to beat yourself up Danica. We've all been there, one way or another.

A technique I often recommend, especially in the learning stage (I still use this myself of course), is to have a test database where you can play with concepts in a really simple and basic way. One that won't matter if it gets broken. Totally safe. An easy way to learn.

[topher23]

I've been busy and unable to get on here very often - finals week and a transition in job descriptions at work at the same time! But I'm glad you were able to get everything working to your satisfaction with this interface. :D

[NeoPa]

I've just checked over your code in Form_frmBOX_SHIPPING. Would you like to check over this slightly amended version of it :

Option Explicit
Option Compare Database
 
Private Sub txtScanCapture_AfterUpdate()
    Dim strSQL As String
    Dim rs As DAO.Recordset
 
    Select Case Len(Me.txtScanCapture)
    Case 3, 4
        'Box
        If Nz(Me.tb_Scan_Cust_Num) = "" Then
            MsgBox "A customer ID must be scanned first before scanning boxes."
        Else
            'Is box registered in database?
            If DCount("BOX_NUM", _
                      "tblBOX", _
                      "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
                'Box does not exist in DB
                MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
            Else
                Me.txtScan_Box_Num = Me.txtScanCapture
                'Box exists.
                'Assign box to current customer, set shipping date=now, and received date to null
                strSQL = "UPDATE tblBOX " & _
                         "SET [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'," & _
                             "[ORDER_NUM]='" & Me.Max_ORDER_NUM & "'," & _
                             "[DATE_BOX_SHIP=Date()," & _
                             "[DATE_BOX_RETURN=Null " & _
                         "WHERE ([BOX_NUM]='" & Me.txtScanCapture & "')"
                DoCmd.SetWarnings (False)
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings (True)
                Me.subfrmBOX_SHIPPING.Requery
                'Me.tb_Scan_Cust_Num = ""
                'Me.Max_ORDER_NUM = ""
            End If
        End If
 
    Case 5
        'Customer
        'Lets find customer entered
        strSQL = "SELECT   [CUST_NUM]," & _
                          "Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
                 "FROM     tblORDERS " & _
                 "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'"
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If rs.RecordCount = 0 Then
            MsgBox "Customer number not recognized"
            'Do whatever you want to handle this case
        Else
            Me.tb_Scan_Cust_Num = rs!CUST_NUM
            Me.Max_ORDER_NUM = rs!MaxOfORDER_NUM
        End If
 
    Case Else
        'Some sort of error or user error
        MsgBox "Input error, resetting"
    End Select
 
    Me.txtScanCapture = ""
End Sub

I recommend you see where there are changes, and consider why the code is different.

NB. Particularly the Option lines at the top.

[DanicaDear]

Yes, I'd like very much to see what you've proposed. A long while ago you, in your words took my code and "tarted it up a little" and I've been giggling about it ever since.

This serves a great learning opportunity. I appreciate the time and effort.

Please allow me plenty of time to complete this exercise. Thanks!

[NeoPa]

A long while ago you, in your words took my code and "tarted it up a little" and I've been giggling about it ever since.

I hope that's the terms I used rather than my code changes that triggered all the mirth :D

Of course you're welcome to the help - and take as much time as you want. I'm already happy that you've received it well. I just hope you find it helpful.

[DanicaDear]

Somewhere along in here (code below) I get an error.

Case 5
        'Customer
        'Lets find customer entered
        strSQL = "SELECT   [CUST_NUM]," & _
                          "Max([ORDER_NUM] As MaxOfORDER_NUM " & _
                 "FROM     tblORDERS " & _
                 "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'"
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

This is what the error says:

Run-time error '3075':
Syntax error (missing operator) in query expression 
"Max([ORDER_NUM] As MaxOfORDER_NUM FROM tblORDERS WHERE
[CUST_NUM]='10000". 

where 10000 was the Cust_Num scanned.
I haven't invested a lot of time but the few things I tried were unsuccessful.

[NeoPa]

If you look at line #5 of your code you'll notice that the parentheses around [ORDER_NUM] in the Max() function are not matched. You have an opening one, but no closing one.

PS. Probably not an issue now, but your error message doesn't match the code either. There should be a single quote (') before the double quote (") at the end there. IE. The code is good, but your error message indicates another problem (that isn't really there).

[NeoPa]

More general advice for such issues I copy below.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[DanicaDear]

I have wrapped up this problem and just wanted to post my code for future readers. I can't possibly mark a best answer here because everyone's answer was great and it took the contributions of everyone to make this a success. Thanks to everyone who helped out!

Option Explicit
Option Compare Database
 
Public strLastScan As String
Public db As DAO.Database
 
Private Sub Form_Open(Cancel As Integer)
    Set db = CurrentDb
End Sub
 
Private Sub txtScanCapture_AfterUpdate()
    Dim strSQL As String
 
    Select Case Len(Me.txtScanCapture)
    Case 3, 4
        'Box
        If strLastScan <> "Customer" Then
            MsgBox "A customer ID must be scanned first before scanning boxes."
        Else
            'Is box registered in database?
            If DCount("BOX_NUM", _
                      "tblBOX", _
                      "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
                'Box does not exist in DB
                MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
            Else
                Me.txtScan_Box_Num = Me.txtScanCapture
                'Box exists.
                'Assign box to current customer, set shipping date=now, and received date to null
                strSQL = "UPDATE tblBOX " & _
                         "SET    [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'" & _
                              ", [ORDER_NUM]='" & Me.Max_ORDER_NUM & "'" & _
                              ", [DATE_BOX_SHIP]=Date()" & _
                              ", [DATE_BOX_RETURN]=Null " & _
                         "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
                DoCmd.SetWarnings (False)
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings (True)
                Me.subfrmBOX_SHIPPING.Requery
                'Update the DATE_SHIP in tblOrders where necessary
                With db.OpenRecordset("tblORDERS", dbOpenDynaset)
                    Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
                    If Not .NoMatch Then
                        If IsNull(![DATE_SHIP]) Then
                            Call .Edit
                            ![DATE_SHIP] = Date
                            Call .Update
                         End If
                    End If
                    Call .Close
                End With
            End If
            strLastScan = "Box"
            Me.tb_Scan_Cust_Num.BackStyle = 1
            Me.txtScan_Box_Num.BackStyle = 0
        End If
 
    Case 5
        'Customer
        'Lets find customer entered
        strSQL = "SELECT   [CUST_NUM]" & _
                        ", Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
                 "FROM     tblORDERS " & _
                 "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'" & _
                 "GROUP BY [CUST_NUM]"
        With db.OpenRecordset(strSQL, dbOpenSnapshot)
            If .RecordCount = 0 Then
                MsgBox "Customer number not recognized"
                'Do whatever you want to handle this case
            Else
                strLastScan = "Customer"
                Me.tb_Scan_Cust_Num.BackStyle = 1
                Me.txtScan_Box_Num.BackStyle = 1
                Me.tb_Scan_Cust_Num = !CUST_NUM
                Me.Max_ORDER_NUM = !MaxOfORDER_NUM
            End If
            Call .Close
        End With
 
    Case Else
        'Some sort of error or user error
        MsgBox "Input error, resetting"
    End Select
 
    Me.txtScanCapture = ""
End Sub