By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

How to get scanned data into my forms?

P: 260
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.

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)
Expand|Select|Wrap|Line Numbers
  1. Dim LastOrder As Long
  3.     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)
Expand|Select|Wrap|Line Numbers
  1. 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.

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.
Apr 22 '10 #1
Share this Question
Share on Google+
34 Replies

Expert Mod 100+
P: 2,321
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?
Apr 23 '10 #2

P: 260
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!!
Apr 23 '10 #3

Expert Mod 100+
P: 2,321
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.
Apr 23 '10 #4

Expert Mod 100+
P: 2,321
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.
Attached Files
File Type: zip (38.4 KB, 214 views)
Apr 23 '10 #5

Expert Mod 15k+
P: 31,768
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.
Apr 26 '10 #6

P: 260
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.
Apr 26 '10 #7

P: 260
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".)

Expand|Select|Wrap|Line Numbers
  1. If KeyAscii = 13 Then
  2.         'Scan is complete (we hope)
  3.         If Len(Me.tb_ScanID) < 4 Or Len(Me.tb_ScanID) > 5 Then
  4.             'Some sort of error or user error
  5.             MsgBox "Input error, resetting"
  6.             GoTo doClear
  7.         End If
  9.         Select Case Len(Me.tb_ScanID)
  10.             Case 4
  11.                 'Box
  12.                 If Not bCustomerScanned Then
  13.                     MsgBox "A customer ID must be scanned first before scanning boxes"
  14.                     GoTo doClear
  15.                 End If
  16.                 'Is box registered in database?
  17.                 If DCount("*", "tbl_Box", "KEY_Box=" & Me.tb_ScanID) = 0 Then
  18.                     'Box does not exist in DB
  19.                     MsgBox "Box " & Me.tb_ScanID & " not recognized in tool"
  20.                     GoTo doClear
  21.                 Else
  22.                     'Box exists.
  24.                     'Assign box to current customer, set shipping date=now, and received date to null and bMissing to false
  25.                     Dim strSQL As String
  26.                     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" & _
  27.                             " WHERE (((tbl_Box.KEY_Box)=" & Me.tb_ScanID & "));"
  28.                     DoCmd.SetWarnings (False)
  29.                         DoCmd.RunSQL strSQL
  30.                     DoCmd.SetWarnings (True)
  31.                     Me.subFrm_Boxes.Requery
  32.                     GoTo doClear
  35.                 End If
  37.             Case 5
  38.                 'Customer
  39.                 'Lets find customer entered
  40.                 Me.Recordset.FindFirst "KEY_Customer=" & Me.tb_ScanID
  41.                 If Me.Recordset.NoMatch Then
  42.                     MsgBox "Customer barcode not recognized"
  43.                     'Do whatevter you want to handle this case
  44.                 End If
  45.                 bCustomerScanned = True
  46.                 GoTo doClear
  47.         End Select
  48.     End If
Apr 26 '10 #8

P: 260
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. 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.
Apr 26 '10 #9

Expert Mod 100+
P: 2,321
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:

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

Expert Mod 15k+
P: 31,768
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.
Apr 27 '10 #11

P: 260
Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private bCustomerScanned As Boolean
  4. Private Sub Form_Open(Cancel As Integer)
  5. Dim varLastOrderNumForCust As Variant
  8. 'Last/MAX Order Number for Customer
  9. varLastOrderNumForCust = DMax("[ORDER_NUM]", "tblOrders", "[CUST_NUM] = '" & Me![CUST_NUM] & "'")
  12. End Sub
  14. Private Sub Form_KeyPress(KeyAscii As Integer)
  15.     'A key has been pressed.
  18.     'If key pressed is numeric, add it to the ID
  19.         If IsNumeric(Chr(KeyAscii)) Then
  20.             'Concatenate it to the end of the ID, and then convert _
  21. back to long (In theory you could keep it as a string all the time I guess)
  22.             Me.tb_Scan_Cust_Num = Me.tb_Scan_Cust_Num & _
  23. Chr(KeyAscii)
  24.             Exit Sub
  25.         End If
  29.     If KeyAscii = 13 Then
  30.         'Scan is complete (we hope)
  31.         If Len(Me.tb_Scan_Cust_Num) < 4 Or Len(Me.tb_Scan_Cust_Num) > 5 Then
  32.             'Some sort of error or user error
  33.             MsgBox "Input error, resetting"
  34.             GoTo doClear
  35.         End If
  37.         Select Case Len(Me.tb_Scan_Cust_Num)
  38.             Case 4
  39.                 'Box
  40.                 If Not bCustomerScanned Then
  41.                     MsgBox "A customer ID must be scanned first _
  42. before scanning boxes"
  43.                     GoTo doClear
  44.                 End If
  45.                 'Is box registered in database?
  46.                 If DCount("*", "tblBOX", "BOX_NUM=" & Me.tb_Scan_Cust_Num) = 0 Then
  47.                     'Box does not exist in DB
  48.                     MsgBox "Box " & Me.tb_Scan_Cust_Num & " not recognized in tool"
  49.                     GoTo doClear
  50.                 Else
  51.                     'Box exists.
  53.                     'Assign box to current customer, set shipping date=now, and _
  54. received date to null and bMissing to false
  55.                     Dim strSQL As String
  56.                     strSQL = "UPDATE tblBOX SET tblBOX.CUST_NUM = " & _
  57. Me.tbCUST_NUM & ", tblBOX.DATE_BOX_SHIP = Date(), _
  58. tblBOX.DATE_BOX_RETURN = Null, tblBOX.Missing = False" & _
  59.                             " WHERE (((tblBOX.BOX_NUM)=" & Me.tb_Scan_Cust_Num & "));"
  60.                     DoCmd.SetWarnings (False)
  61.                         DoCmd.RunSQL strSQL
  62.                     DoCmd.SetWarnings (True)
  63.                     Me.subFrm_Boxes.Requery
  64.                     GoTo doClear
  67.                 End If
  69.             Case 5
  70.                 'Customer
  71.                 'Lets find customer entered
  72.                 Me.Recordset.FindFirst "CUST_NUM=" & Me.tb_Scan_Cust_Num
  73.                 If Me.Recordset.NoMatch Then
  74.                     MsgBox "Customer number not recognized"
  75.                     'Do whatevter you want to handle this case
  76.                 End If
  77.                 bCustomerScanned = True
  78.                 GoTo doClear
  79.         End Select
  80.     End If
  85. Exit Sub
  86. doClear:
  87.     Me.tb_Scan_Cust_Num = ""
  88.     Me.tb_FocusKeeper.SetFocus
  90. End Sub
  93. Private Sub Form_Load()
  94.     Me.tb_FocusKeeper.SetFocus 'Access must have an active _
  95. textbox in order to properly recieve keypress _
  96. (At least I couldnt get it to work without)
  97.     bCustomerScanned = False
  98. 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. :-)
Attached Files
File Type: zip (111.2 KB, 119 views)
Apr 27 '10 #12

Expert Mod 100+
P: 2,321
Expand|Select|Wrap|Line Numbers
  1.             Case 5 
  2.                 'Customer 
  3.                 'Lets find customer entered 
  4.                 Me.Recordset.FindFirst "CUST_NUM='" & Me.tb_Scan_Cust_Num &"'"
  5.                 If Me.Recordset.NoMatch Then 
  6.                     MsgBox "Customer number not recognized" 
  7.                     'Do whatevter you want to handle this case 
  8.                     bCustomerScanned = False
  9.                 Else
  10.                   bCustomerScanned = True                   
  11.                 End If 
  13.                 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.
Apr 27 '10 #13

Expert Mod 15k+
P: 31,768
Those ridiculous little tick marks are getting me again!!!!!
Check out Quotes (') and Double-Quotes (") - Where and When to use them.
Apr 27 '10 #14

Expert 100+
P: 234
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.
Attached Files
File Type: zip GloveProgram042710[1].zip (104.5 KB, 122 views)
Apr 29 '10 #15

P: 260
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. :-)

Apr 29 '10 #16

P: 260
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!
Apr 29 '10 #17

P: 260
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. :-)
May 2 '10 #18

Expert Mod 15k+
P: 31,768
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.
May 2 '10 #19

P: 260
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. :-)
May 2 '10 #20

Expert Mod 15k+
P: 31,768
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.
May 3 '10 #21

P: 260
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?
May 3 '10 #22

Expert Mod 15k+
P: 31,768
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.
May 3 '10 #23

P: 260
Trigger. LOL.
May 4 '10 #24

Expert Mod 15k+
P: 31,768
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).
May 4 '10 #25

P: 260
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!!!!!
May 4 '10 #26

Expert Mod 15k+
P: 31,768
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.
May 4 '10 #27

Expert 100+
P: 234
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
May 4 '10 #28

Expert Mod 15k+
P: 31,768
I've just checked over your code in Form_frmBOX_SHIPPING. Would you like to check over this slightly amended version of it :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  4. Private Sub txtScanCapture_AfterUpdate()
  5.     Dim strSQL As String
  6.     Dim rs As DAO.Recordset
  8.     Select Case Len(Me.txtScanCapture)
  9.     Case 3, 4
  10.         'Box
  11.         If Nz(Me.tb_Scan_Cust_Num) = "" Then
  12.             MsgBox "A customer ID must be scanned first before scanning boxes."
  13.         Else
  14.             'Is box registered in database?
  15.             If DCount("BOX_NUM", _
  16.                       "tblBOX", _
  17.                       "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  18.                 'Box does not exist in DB
  19.                 MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
  20.             Else
  21.                 Me.txtScan_Box_Num = Me.txtScanCapture
  22.                 'Box exists.
  23.                 'Assign box to current customer, set shipping date=now, and received date to null
  24.                 strSQL = "UPDATE tblBOX " & _
  25.                          "SET [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'," & _
  26.                              "[ORDER_NUM]='" & Me.Max_ORDER_NUM & "'," & _
  27.                              "[DATE_BOX_SHIP=Date()," & _
  28.                              "[DATE_BOX_RETURN=Null " & _
  29.                          "WHERE ([BOX_NUM]='" & Me.txtScanCapture & "')"
  30.                 DoCmd.SetWarnings (False)
  31.                 DoCmd.RunSQL strSQL
  32.                 DoCmd.SetWarnings (True)
  33.                 Me.subfrmBOX_SHIPPING.Requery
  34.                 'Me.tb_Scan_Cust_Num = ""
  35.                 'Me.Max_ORDER_NUM = ""
  36.             End If
  37.         End If
  39.     Case 5
  40.         'Customer
  41.         'Lets find customer entered
  42.         strSQL = "SELECT   [CUST_NUM]," & _
  43.                           "Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
  44.                  "FROM     tblORDERS " & _
  45.                  "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'"
  46.         Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
  47.         If rs.RecordCount = 0 Then
  48.             MsgBox "Customer number not recognized"
  49.             'Do whatever you want to handle this case
  50.         Else
  51.             Me.tb_Scan_Cust_Num = rs!CUST_NUM
  52.             Me.Max_ORDER_NUM = rs!MaxOfORDER_NUM
  53.         End If
  55.     Case Else
  56.         'Some sort of error or user error
  57.         MsgBox "Input error, resetting"
  58.     End Select
  60.     Me.txtScanCapture = ""
  61. 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.
May 5 '10 #29

P: 260
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!
May 5 '10 #30

Expert Mod 15k+
P: 31,768
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.
May 6 '10 #31

P: 260
Somewhere along in here (code below) I get an error.
Expand|Select|Wrap|Line Numbers
  1. Case 5
  2.         'Customer
  3.         'Lets find customer entered
  4.         strSQL = "SELECT   [CUST_NUM]," & _
  5.                           "Max([ORDER_NUM] As MaxOfORDER_NUM " & _
  6.                  "FROM     tblORDERS " & _
  7.                  "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'"
  8.         Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
This is what the error says:
Expand|Select|Wrap|Line Numbers
  1. Run-time error '3075':
  2. Syntax error (missing operator) in query expression 
  4. [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.
May 20 '10 #32

Expert Mod 15k+
P: 31,768
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).
May 21 '10 #33

Expert Mod 15k+
P: 31,768
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.
May 21 '10 #34

P: 260
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!
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  4. Public strLastScan As String
  5. Public db As DAO.Database
  7. Private Sub Form_Open(Cancel As Integer)
  8.     Set db = CurrentDb
  9. End Sub
  11. Private Sub txtScanCapture_AfterUpdate()
  12.     Dim strSQL As String
  14.     Select Case Len(Me.txtScanCapture)
  15.     Case 3, 4
  16.         'Box
  17.         If strLastScan <> "Customer" Then
  18.             MsgBox "A customer ID must be scanned first before scanning boxes."
  19.         Else
  20.             'Is box registered in database?
  21.             If DCount("BOX_NUM", _
  22.                       "tblBOX", _
  23.                       "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  24.                 'Box does not exist in DB
  25.                 MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
  26.             Else
  27.                 Me.txtScan_Box_Num = Me.txtScanCapture
  28.                 'Box exists.
  29.                 'Assign box to current customer, set shipping date=now, and received date to null
  30.                 strSQL = "UPDATE tblBOX " & _
  31.                          "SET    [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'" & _
  32.                               ", [ORDER_NUM]='" & Me.Max_ORDER_NUM & "'" & _
  33.                               ", [DATE_BOX_SHIP]=Date()" & _
  34.                               ", [DATE_BOX_RETURN]=Null " & _
  35.                          "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  36.                 DoCmd.SetWarnings (False)
  37.                 DoCmd.RunSQL strSQL
  38.                 DoCmd.SetWarnings (True)
  39.                 Me.subfrmBOX_SHIPPING.Requery
  40.                 'Update the DATE_SHIP in tblOrders where necessary
  41.                 With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  42.                     Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
  43.                     If Not .NoMatch Then
  44.                         If IsNull(![DATE_SHIP]) Then
  45.                             Call .Edit
  46.                             ![DATE_SHIP] = Date
  47.                             Call .Update
  48.                          End If
  49.                     End If
  50.                     Call .Close
  51.                 End With
  52.             End If
  53.             strLastScan = "Box"
  54.             Me.tb_Scan_Cust_Num.BackStyle = 1
  55.             Me.txtScan_Box_Num.BackStyle = 0
  56.         End If
  58.     Case 5
  59.         'Customer
  60.         'Lets find customer entered
  61.         strSQL = "SELECT   [CUST_NUM]" & _
  62.                         ", Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
  63.                  "FROM     tblORDERS " & _
  64.                  "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'" & _
  65.                  "GROUP BY [CUST_NUM]"
  66.         With db.OpenRecordset(strSQL, dbOpenSnapshot)
  67.             If .RecordCount = 0 Then
  68.                 MsgBox "Customer number not recognized"
  69.                 'Do whatever you want to handle this case
  70.             Else
  71.                 strLastScan = "Customer"
  72.                 Me.tb_Scan_Cust_Num.BackStyle = 1
  73.                 Me.txtScan_Box_Num.BackStyle = 1
  74.                 Me.tb_Scan_Cust_Num = !CUST_NUM
  75.                 Me.Max_ORDER_NUM = !MaxOfORDER_NUM
  76.             End If
  77.             Call .Close
  78.         End With
  80.     Case Else
  81.         'Some sort of error or user error
  82.         MsgBox "Input error, resetting"
  83.     End Select
  85.     Me.txtScanCapture = ""
  86. End Sub
Jun 1 '10 #35

Post your reply

Sign in to post your reply or Sign up for a free account.