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? - Option Explicit
-
Option Compare Database
-
-
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
-
'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 & " is not a valid box number."
-
Else
-
Me.txtScan_Box_Num = Me.txtScanCapture
-
'Box exists.
-
'Set received date=now
-
strSQL = "UPDATE tblBOX " & _
-
"SET [DATE_BOX_RETURN]=Date() " & _
-
"WHERE ([BOX_NUM]='" & Me.txtScanCapture & "')"
-
DoCmd.SetWarnings (False)
-
DoCmd.RunSQL strSQL
-
DoCmd.SetWarnings (True)
-
Me.subfrmBOX_RECEIVING.Requery
-
'Use the box_num to obtain the cust_num & order_num from tblBOX
-
'but I don't know how to store the values to use in the .FindFirst below
-
strSQL = "SELECT * " & _
-
"FROM [tblBOX] " & _
-
"WHERE [BOX_NUM]='" & Me.txtScanCapture & "'"
-
With db.OpenRecordset(strSQL, dbOpenSnapshot)
-
If .RecordCount = 0 Then
-
MsgBox "There is no record of this box shipping"
-
'Do whatever you want to handle this case
-
Else
-
Me.txtScan_Box_Num = !BOX_NUM
-
Me.tb_Cust_Num = !CUST_NUM
-
Me.Max_ORDER_NUM = !ORDER_NUM
-
End If
-
Call .Close
-
End With
-
End If
-
-
'Update the DATE_RET in tblOrders where necessary
-
With db.OpenRecordset("tblORDERS", dbOpenDynaset)
-
Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
-
If Not .NoMatch Then
-
If IsNull(![DATE_RET]) Then
-
Call .Edit
-
![DATE_RET] = Date
-
Call .Update
-
End If
-
End If
-
Call .Close
-
End With
-
-
Case Else
-
'Some sort of error or user error
-
MsgBox "Box Numbers can only be 3 or 4 digits."
-
End Select
-
-
Me.txtScanCapture = ""
-
End Sub
-
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.)
8 1328
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
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.
Danica, assuming that I am reading your Request correctly: -
'*********************************** CODE INTENTIONALLY REMOVED ***********************************
-
'Update the DATE_RET in tblOrders where necessary
-
With db.OpenRecordset("tblORDERS", dbOpenDynaset)
-
Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
-
If Not .NoMatch Then
-
Dim intNumOfBoxes As Integer 'Don't normally place
-
Dim intBoxesReturned As Integer 'Declarations In-Line
-
-
'Determine the Total Number of Boxes for a specific Order
-
intNumOfBoxes = DCount("[BOX_NUM]", "tblBOX", "[ORDER_NUM] = '" & Me!Max_ORDER_NUM & "'")
-
-
'Determine the Total Number of Boxes returned for a specific Order. This can be indicated
-
'by the number of Non NULL Vales in the [DATE_BOX_RETURN] Field for a given Order
-
intBoxesReturned = DCount("*", "tblBOX", "[DATE_BOX_RETURN] Is Not Null And " & _
-
"[ORDER_NUM] = '" & Me!Max_ORDER_NUM & "'")
-
-
If intNumOfBoxes = intBoxesReturned Then 'ALL Boxes returned
-
Call .Edit
-
![DATE_RET] = Date
-
Call .Update
-
End If
-
End If
-
Call .Close
-
End With
-
'*********************************** CODE INTENTIONALLY REMOVED ***********************************
-
NeoPa 32,556
Expert Mod 16PB
I would suggest some code to follow the logic below : - 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.
- If it is greater than zero then at least one box has not yet been returned so exit this process.
- If it is zero then that means all boxes have been returned and we proceed.
- Run some SQL to update the value of [DATE_RET] in the relevant [tblORDERS] record to Date().
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) : -
Option Compare Database
-
Option Explicit
-
-
Private db As DAO.Database
-
-
Private Sub Form_Open(Cancel As Integer)
-
Set db = CurrentDb
-
End Sub
-
-
Private Sub txtScanCapture_AfterUpdate()
-
Dim strSQL As String, strCustNo As String, strOrderNo As String
-
-
Select Case Len(Me.txtScanCapture)
-
Case 3, 4
-
'Box
-
'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 & " is not a valid box number."
-
Else
-
Me.txtScan_Box_Num = Me.txtScanCapture
-
'Box exists.
-
'Set received date=now
-
strSQL = "UPDATE [tblBOX] " & _
-
"SET [DATE_BOX_RETURN]=Date() " & _
-
"WHERE ([BOX_NUM]='" & Me.txtScanCapture & "')"
-
DoCmd.SetWarnings (False)
-
DoCmd.RunSQL strSQL
-
DoCmd.SetWarnings (True)
-
Me.subfrmBOX_RECEIVING.Requery
-
'Use the box_num to obtain the cust_num & order_num from tblBOX
-
'but I don't know how to store the values to use in the .FindFirst below
-
strSQL = "SELECT * " & _
-
"FROM [tblBOX] " & _
-
"WHERE [BOX_NUM]='" & Me.txtScanCapture & "'"
-
With db.OpenRecordset(strSQL, dbOpenSnapshot)
-
If .RecordCount = 0 Then
-
MsgBox "There is no record of this box shipping"
-
'Do whatever you want to handle this case
-
Else
-
Me.txtScan_Box_Num = !BOX_NUM
-
strCustNo = !CUST_NUM
-
strOrderNo = !ORDER_NUM
-
Me.tb_Cust_Num = strCustNo
-
Me.Max_ORDER_NUM = strOrderNo
-
End If
-
Call .Close
-
End With
-
End If
-
-
'Update the DATE_RET in tblOrders where necessary
-
strSQL = "([CUST_NUM] = '" & strCustNo & "') AND " & _
-
"([DATE_BOX_RETURN] Is Null)"
-
If DCount("*", "[tblBOX]", strSQL) = 0 Then
-
strSQL = "UPDATE [tblORDERS] " & _
-
"SET [DATE_RET] = Date() " & _
-
"WHERE ([CUST_NUM] = '" & strCustNo & "')" & _
-
" AND ([ORDER_NUM] = '" & strOrderNo & "')"
-
Call db.Execute(strSQL)
-
End If
-
-
Case Else
-
'Some sort of error or user error
-
MsgBox "Box Numbers can only be 3 or 4 digits."
-
End Select
-
-
Me.txtScanCapture = Null
-
End Sub
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!!
NeoPa 32,556
Expert Mod 16PB
Always pleased to help. Let us know when you've tested and how it went :-)
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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 ...
|
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...
|
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)...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
|
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...
| |