473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

How can I change this code to update a table only on the last of a series of entries?

269 256MB
I have a piece of code that is working great but we want to change the way the DB works just a little bit. This seems complicated to me so I'm hoping someone with more experience can help me figure this out.

In a particular form we are tracking boxes as they come back in (our plastic boxes are returned after shipment) and then using that to give the customer account credit for returning it. tblBOX contains the box information. For each customer, we will always look at the most recent order (the MAX ORDER_NUM attached to each CUST_NUM). That order usually has multiple box numbers associated with it. Currently, the code (posted below), accepts a box number, makes sure it is 3 or 4 characters (5 characters would be a customer number), then assigns today's date to that box number in the field DATE_BOX_RETURN. There is another date in tblORDERS called DATE_RET. Upon the first box number returned in a customer's order, the DATE_RET is updated in tblORDERS. **I want to change this so that DATE_RET accepts today's date only if ALL of the boxes for that customer are returned.**

Take notice: We are dealing with two dates. One date is associated with each individual box (DATE_BOX_RETURN). Another date is associated with the order itself (DATE_RET). DATE_RET is my area of concern.

Here is an example:
Customer 55555 is assigned boxes 111, 222, & 333.
He returns boxes 111 & 222. Those boxes get today's date assigned to them when they come in. They way it works currently is tblORDERS.DATE_RET would be updated upon acceptance of 111. However I don't want to set DATE_RET in tblORDERS until box 333 comes in, indicating all his boxes are back and he's clear.

Here is the entire code, but the part that deals specifically with my problem is lines 51-62. Can someone help me manipulate it so that tblORDERS.DATE_RET wouldn't get updated until box 333 were scanned?

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Public db As DAO.Database
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7.     Set db = CurrentDb
  8. End Sub
  9.  
  10. Private Sub txtScanCapture_AfterUpdate()
  11.     Dim strSQL As String
  12.  
  13.     Select Case Len(Me.txtScanCapture)
  14.     Case 3, 4
  15.         'Box
  16.         'Is box registered in database?
  17.         If DCount("BOX_NUM", _
  18.                   "tblBOX", _
  19.                   "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
  20.             'Box does not exist in DB
  21.             MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
  22.         Else
  23.             Me.txtScan_Box_Num = Me.txtScanCapture
  24.             'Box exists.
  25.             'Set received date=now
  26.             strSQL = "UPDATE tblBOX " & _
  27.                      "SET    [DATE_BOX_RETURN]=Date() " & _
  28.                      "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  29.             DoCmd.SetWarnings (False)
  30.             DoCmd.RunSQL strSQL
  31.             DoCmd.SetWarnings (True)
  32.             Me.subfrmBOX_RECEIVING.Requery
  33.             'Use the box_num to obtain the cust_num & order_num from tblBOX
  34.             'but I don't know how to store the values to use in the .FindFirst below
  35.             strSQL = "SELECT   * " & _
  36.                      "FROM     [tblBOX] " & _
  37.                      "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'"
  38.             With db.OpenRecordset(strSQL, dbOpenSnapshot)
  39.                 If .RecordCount = 0 Then
  40.                     MsgBox "There is no record of this box shipping"
  41.                     'Do whatever you want to handle this case
  42.                 Else
  43.                     Me.txtScan_Box_Num = !BOX_NUM
  44.                     Me.tb_Cust_Num = !CUST_NUM
  45.                     Me.Max_ORDER_NUM = !ORDER_NUM
  46.                 End If
  47.                 Call .Close
  48.             End With
  49.         End If
  50.  
  51.         'Update the DATE_RET in tblOrders where necessary
  52.         With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  53.             Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
  54.             If Not .NoMatch Then
  55.                 If IsNull(![DATE_RET]) Then
  56.                     Call .Edit
  57.                     ![DATE_RET] = Date
  58.                     Call .Update
  59.                 End If
  60.             End If
  61.             Call .Close
  62.         End With
  63.  
  64.     Case Else
  65.         'Some sort of error or user error
  66.         MsgBox "Box Numbers can only be 3 or 4 digits."
  67.     End Select
  68.  
  69.     Me.txtScanCapture = ""
  70. End Sub
  71.  
Thanks to the lovely NeoPa for helping me write this code to start with. I can take no credit for how lovely this is. (It is still working great we just want to make a change.)
Aug 18 '11 #1
8 1328
patjones
931 Expert 512MB
Hi Danica,

How exactly is tblBOX setup? I'm imagining that there is one column that stores the order number, a column that stores the box number, and a column that indicates what date the box was returned (is this DATE_BOX_RETURN?).

My reason for asking is that we'll probably want to modify lines 52 and 53 (which taken together is essentially a query) to determine all the boxes associated with an order, and check for the existence of null entries in the DATE_BOX_RETURN field.

So in your example, if DATE_BOX_RETURN has entries for boxes 111 and 222, but is null for box 333...we would leave DATE_RET in tblOrders null. On the other hand, if all three boxes have entries for DATE_BOX_RETURN, then we can fill in DATE_RET with the current date.

This is the logic as I see it, but I wanted to lay it out conceptually and make sure I understand your table structure before suggesting a code modification.

Pat
Aug 19 '11 #2
NeoPa
32,556 Expert Mod 16PB
NeoPa:
Busy weekend coming up Danica, but I've seen this and I'll look into posting something when I can.
Danica, Still no time just now to work on this, but in light of Pat's interest too, it may make sense to attach a sanitised copy of your database to the thread (if you're comfortable with that) otherwise you can email me a copy if you prefer. Attach Database (or other work) may prove helpful.

I say this as I may get a chance to play with it in small gaps even if I don't have enough time in one go to put a proper response together.

PS. I'll delete my other post now as it adds nothing material to the thread.
Aug 19 '11 #3
ADezii
8,834 Expert 8TB
Danica, assuming that I am reading your Request correctly:
Expand|Select|Wrap|Line Numbers
  1. '*********************************** CODE INTENTIONALLY REMOVED ***********************************
  2. 'Update the DATE_RET in tblOrders where necessary
  3. With db.OpenRecordset("tblORDERS", dbOpenDynaset)
  4.   Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
  5.     If Not .NoMatch Then
  6.       Dim intNumOfBoxes As Integer          'Don't normally place
  7.       Dim intBoxesReturned As Integer       'Declarations In-Line
  8.  
  9.       'Determine the Total Number of Boxes for a specific Order
  10.       intNumOfBoxes = DCount("[BOX_NUM]", "tblBOX", "[ORDER_NUM] = '" & Me!Max_ORDER_NUM & "'")
  11.  
  12.       'Determine the Total Number of Boxes returned for a specific Order. This can be indicated
  13.       'by the number of Non NULL Vales in the [DATE_BOX_RETURN] Field for a given Order
  14.       intBoxesReturned = DCount("*", "tblBOX", "[DATE_BOX_RETURN] Is Not Null And " & _
  15.                                 "[ORDER_NUM] = '" & Me!Max_ORDER_NUM & "'")
  16.  
  17.       If intNumOfBoxes = intBoxesReturned Then      'ALL Boxes returned
  18.         Call .Edit
  19.           ![DATE_RET] = Date
  20.         Call .Update
  21.       End If
  22.     End If
  23.       Call .Close
  24. End With
  25. '*********************************** CODE INTENTIONALLY REMOVED ***********************************
  26.  
Aug 19 '11 #4
NeoPa
32,556 Expert Mod 16PB
I would suggest some code to follow the logic below :
  1. Using DCount(), determine if the number of [tblBOX] records that match the customer and that have not been returned is zero or not. This can be done in a single line of code.
  2. If it is greater than zero then at least one box has not yet been returned so exit this process.
  3. If it is zero then that means all boxes have been returned and we proceed.
  4. Run some SQL to update the value of [DATE_RET] in the relevant [tblORDERS] record to Date().
Aug 19 '11 #5
NeoPa
32,556 Expert Mod 16PB
I made some other minor changes in the code Danica, but mostly just fixed the stuff to handle your request (See lines #54 through #62 particularly) :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private db As DAO.Database
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7.     Set db = CurrentDb
  8. End Sub
  9.  
  10. Private Sub txtScanCapture_AfterUpdate()
  11.     Dim strSQL As String, strCustNo As String, strOrderNo As String
  12.  
  13.     Select Case Len(Me.txtScanCapture)
  14.     Case 3, 4
  15.         'Box
  16.         'Is box registered in database?
  17.         If DCount("[BOX_NUM]", _
  18.                   "[tblBOX]", _
  19.                   "[BOX_NUM]='" & Me.txtScanCapture & "'") = 0 Then
  20.             'Box does not exist in DB
  21.             MsgBox "Box " & Me.txtScanCapture & " is not a valid box number."
  22.         Else
  23.             Me.txtScan_Box_Num = Me.txtScanCapture
  24.             'Box exists.
  25.             'Set received date=now
  26.             strSQL = "UPDATE [tblBOX] " & _
  27.                      "SET    [DATE_BOX_RETURN]=Date() " & _
  28.                      "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
  29.             DoCmd.SetWarnings (False)
  30.             DoCmd.RunSQL strSQL
  31.             DoCmd.SetWarnings (True)
  32.             Me.subfrmBOX_RECEIVING.Requery
  33.             'Use the box_num to obtain the cust_num & order_num from tblBOX
  34.             'but I don't know how to store the values to use in the .FindFirst below
  35.             strSQL = "SELECT   * " & _
  36.                      "FROM     [tblBOX] " & _
  37.                      "WHERE    [BOX_NUM]='" & Me.txtScanCapture & "'"
  38.             With db.OpenRecordset(strSQL, dbOpenSnapshot)
  39.                 If .RecordCount = 0 Then
  40.                     MsgBox "There is no record of this box shipping"
  41.                     'Do whatever you want to handle this case
  42.                 Else
  43.                     Me.txtScan_Box_Num = !BOX_NUM
  44.                     strCustNo = !CUST_NUM
  45.                     strOrderNo = !ORDER_NUM
  46.                     Me.tb_Cust_Num = strCustNo
  47.                     Me.Max_ORDER_NUM = strOrderNo
  48.                 End If
  49.                 Call .Close
  50.             End With
  51.         End If
  52.  
  53.         'Update the DATE_RET in tblOrders where necessary
  54.         strSQL = "([CUST_NUM] = '" & strCustNo & "') AND " & _
  55.                  "([DATE_BOX_RETURN] Is Null)"
  56.         If DCount("*", "[tblBOX]", strSQL) = 0 Then
  57.             strSQL = "UPDATE [tblORDERS] " & _
  58.                      "SET [DATE_RET] = Date() " & _
  59.                      "WHERE ([CUST_NUM] = '" & strCustNo & "')" & _
  60.                      "  AND ([ORDER_NUM] = '" & strOrderNo & "')"
  61.             Call db.Execute(strSQL)
  62.         End If
  63.  
  64.     Case Else
  65.         'Some sort of error or user error
  66.         MsgBox "Box Numbers can only be 3 or 4 digits."
  67.     End Select
  68.  
  69.     Me.txtScanCapture = Null
  70. End Sub
Aug 21 '11 #6
DanicaDear
269 256MB
Thanks EVERYONE. I will be out of the office much of this week so I'll need some time to read, comprehend, understand, and test. Thanks so much for everyone's help. I appreciate it more than you know. I do not know what I would do without Bytes and all of you!!
Aug 22 '11 #7
NeoPa
32,556 Expert Mod 16PB
Always pleased to help. Let us know when you've tested and how it went :-)
Aug 22 '11 #8
DanicaDear
269 256MB
Pat, Your assumptions are correct.
ADezii and NeoPa, both of your codes work. Thanks for providing me with not one, but TWO options. It's always great to see more than one way to do the same thing. I hope it helps others, too.

Thanks again for another great solution!
Aug 22 '11 #9

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

Similar topics

2
by: amwi | last post by:
I have tried to solve this on my own for a long time now, so i really need some help here... I use Oracle 10.1 and SQL *plus 10.1. How do i update table a.fkid from table b.pkid with the...
1
by: windandwaves | last post by:
Hi Gurus I am trying to make a table with unique entries. Problem is, the entries are upto 1000 characters long. Can I still apply a unique index and how and if so, what field type should I...
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
9
by: baonks | last post by:
hello all here is my problem: I have 2 table 1: K_POS SALDO_A_D SALDO_A_K 11100 105 5 11200 5 105
2
by: PawelR | last post by:
Hello Group, In my apps I fill DataSet from MSAcces (file db1.mdb) oleDbDataAdapter1.Fill(fromAccessDS,"MyTable") MyTable heve 4 columns, I change in dataGrid...
5
by: SQL Learner | last post by:
Hi Alex (Kuznetsov) and All, This is to follow up with my last post, "Link two tables using partial word match". How can I UPDATE table using partial word match? How can I write a SQL statement...
1
by: adithi | last post by:
My Table Structure is: Table A Table B Table C colA -PK Col B-PK Col C-PK Col B-FK ...
1
by: delusion7 | last post by:
Hi.. I have 2 tables: country and ticket country table contains countryId and countries ticket table contains many fields, and a country field the country table is new and consists of all...
2
by: dragrid | last post by:
Hi Anyone appreciate your help, I have a table with about 7 columns - one is a date column - I would like to update the table and change all dates starting from a certain key (the first column)...
0
by: specialone | last post by:
Hi, I am trying to update a new table which i have created. The purpose is if i change any input parameters and run the code, the table should automatically update itself. This is for 8760 hours...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.