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

Where in this code should I add another update? I want my code to do more. :-)

P: 256
Hello my Bytes friends! I'm back. :-)

I have a very nice piece of code written by the Lovely NeoPa and I would like it to do one more thing for me. The code currently works beautifully; I don't want to change anything, just add to it.

Currently it checks a bunch of jazzy stuff then ultimatley it updates two tables. (Begin line 29, then line 40).
So first you enter a 5-digit CUST_NUM, ENTER followed immediately by 3 or 4 digit BOX_NUM, ENTER. It stores this in tblBOX along with a date, and also stores a date in tblORDERS. Wonderful!

I added 30 fields to tblORDERs.
When I enter a 5 digit customer number followed by 3 or 4 digit box number, I want it to store the box number in BOX_1 and put today's date in SHIPPED_1, matching it to the same customer number entered AND in the MAX Order_Num for that customer, as there will be multiple past orders also with the same customer number (see code starting line 40, it does this for DATE_SHIP already). Then the user will enter another 5 digit customer number followed by a box number. If it is the same customer, it will go in BOX_2 and put today's date in SHIPPED_2. If it is another customer it will put it in the first null BOX_X and put today's date in the corresponding SHIPPED_X. One customer can have up to 10 boxes but generally much less. A customer number is ALWAYS entered before a box number.

I am not good at VBA although I have learned a lot. The codes beginning on lines 29 and 40 were not close enough for me to figure this out on my own. However if someone will help me out, I will try to do it on RECEIVING before posting another question. (However don't be alarmed if I have to come back for more help. lol)

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
Jan 18 '12 #1
Share this Question
Share on Google+
6 Replies

P: 200
So what happens when you have more than 10 BOXES, 10 SHIPPED, or 10 RECEIVED? I would create two additional tables: tblSHIPPED and tblRECEIVED and link those to a field in tblBOX. This way you create relationships between the tables and are not limited to the number of boxes, shipped or received. It may serve you well to read up on data structures and normalization. Neo has a link where you can learn all you want and more on how to properly setup your data.

Good luck!
Jan 18 '12 #2

P: 256
I suppose I could do it that way. I was just trying to avoid another table with the same customer number and order number already stored in tblOrders...
(The most boxes we have ever shipped to a customer is 8.)
I will try out what you are saying. However I think I'd need only one table...Shipped and Received can go in the same table by box number. Customer number be entered upon shipping, max order_num will be a lookup, box number will be entered, SHIPPED will be dated automatically by the code. On receiving, only the box number will be entered to fill that last RECEIVED field with a date. Then I'll link it to the form by order_num. Essentially we will be sending the same exact data to two tables, one that acts like a checkout system and one that holds the data long term. Does that sound like a decent plan?
Jan 18 '12 #3

P: 200
I would work at designing this so there is no duplication of data. Could you add a boolean field (yes/no) to the table running the checkout system that if checked, means the order is received (or whatever you are considering final or closed out)? Seems like a cleaner design.
Jan 18 '12 #4

Expert Mod 15k+
P: 31,276
If you send me Skype message tomorrow early then we can talk about this. It's too involved a structure to do anything that doesn't quite fit properly. I'll go through it with you then.
Jan 19 '12 #5

Expert Mod 15k+
P: 31,276
This has been updated somewhat as the requirement has been clarified.

To explain, the [tblBOX] and [tblORDERS] tables contain the following data :
Table Name=[tblBOX]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. Autonumber      AutoNumber    PK    'May be renamed to BoxID
  3. BOX_NUM         String        FK    'Unique
  4. ORDER_NUM       String        FK
  5. CUST_NUM        String        FK
  6. DATE_BOX_SHIP   Date/Time
  7. DATE_BOX_RETURN Date/Time
Table Name=[tblORDERS]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. ORDER_NUM       AutoNumber    PK
  3. CUST_NUM        String        FK
  4. DATE_ORDERED    Date/Time
  5. DATE_SHIP       Date/Time     FK
  6. DATE_RET        Date/Time           'Set only when last box of order is returned.
  7. ...                                 'Various less relevant fields
[tblBOX] records indicate which customer (and even which order number) the box was last used for, and whether or not it's been returned (If [DATE_BOX_RETURN] not null). If the box is found to be available, however (by being used to ship new gloves out in), then the [tblBOX] record needs to reflect this (clearly), but also the [tblORDERS] table needs to (and before the data in [tblBOX] is overwritten to reflect the box being used in it's new order).
Jan 20 '12 #6

P: 256
I concur with NeoPa's synopsis of my problems. The last sentence is of the utmost importance....the DB is rewriting data BEFORE a human error problem is being discovered and fixed. We are in process of updating....will post update when it is final. THANKS NEOPA!!
Jan 20 '12 #7

Post your reply

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