A field on frmBOX_SHIPPING receives a 5 digit CUST_NUM (text field), pulls up that customer's most recent order (ORDER_NUM (text field), and then accepts box numbers (BOX_NUM, text field)assigned to this order. During this process a DATE_BOX_SHIP = date() is assigned to the box number.
tblORDERS contains the ORDER_NUM and has its own place for a date: DATE_SHIP. This is not redundant information because it's possible the boxes could get seperated and ship out on different dates. So here's what I want to do:
When frmBOX_SHIPPING receives the 5 digit CUST_NUM and pulls up the ORDER_NUM, a box number will be entered and a date will be assigned. I'd like an after_update event, attached to the tblBOX.BOX_NUM to look in tblORDERS for the matching ORDER_NUM, and if the corresponding tlbORDERS.DATE_SHIP is null, set it to Date(). If it is not null, do nothing (that would imply a previous run of the code set the date).
If I'm not providing enough information, please let me know.
Thanks in advance!
Danica
Two questions on Bytes were answered with this same piece of code. Initially these were two separate pieces of code. But here it is nonetheless, in case someone can gain something from it in the future. - 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
-
Thanks to everyone who helped out!
14 2092
Something like this should do the trick, may want to check the field/table names to be sure they are correct. - Dim rs as DAO.Recordset
-
Set rs = CurrentDb.OpenRecordset("tblOrders", dbOpenDynaset)
-
-
rs.Findfirst = "[Order_Num] = '" & me.order_num & "'"
-
-
If rs.nomatch = false then
-
-
If IsNull(rs![Date_Ship]) Then
-
rs.edit
-
rs![Date_Ship] = Date()
-
rs.update
-
End If
-
-
End If
-
-
Set rs = Nothing
-
Wow Megalog, that was fast. All I did was go to lunch and get a sandwich. And, boom, SQL. ;-)
I'll try this and let you know if it works.
Thanks,
Danica
Megalog,
There is a problem with line 2. I have tried - Set rs = CurrentDb.OpenRecordset("tblOrders", dbOpenDynaset)
-
Set rs = OpenRecordset("tblOrders", dbOpenDynaset)
The original you provided and both of these two allow the subform itself to update, but it's not changing the SHIP_DATE in the tblORDERS.
All of the field names are correct. Any other ideas?
Thanks. :-)
Danica
Good catch, I totally screwed up that line. I updated my previous post with the correct recordset statement.
As far as the update not working, my guess is it's not finding a match, therefore skipping the whole update block. Stepping through lines 4 to 12 should give you a good clue.
This is set up to match text for text.. so be sure both fields are formatted as text and not numbers (you did specify before it was a text field), and that there is a value for me.order_num being returned in line 4.
NeoPa 32,556
Expert Mod 16PB
Watching Man City v Spurs ATM, but I noticed some points missing in your code so I grabbed the following for you to check through. When posting any code on here please :- Ensure you have Option Explicit set (See Require Variable Declaration).
- Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
- Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
- Ensure that the code in your post is enveloped within CODE tags (For more on this see BB Code List). The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.
If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question. NeoPa 32,556
Expert Mod 16PB
I meant to look at the code for this first, but I fell over the other one and did some work on that instead - and now it's quite late.
I'll try to look at it again tomorrow for you. Megalog's idea of using VBA instead of SQL is a good one. I would probably proceed along the same lines.
I was studying about the Option Explicit awhile ago before some stuff at work hit the fan and I had to abandon it. I set the "require variable declaration" tool but it didn't change my code. From what I understand so far, this just helps you type things correctly. But if I post code from Bytes that doesn't have it to start with, does that mean it's too late to use it? I tried to do new code with it in there, and even though I had it selected, it didn't put that at the top of my code. Most likely I just need to play with this some more. I'll work on that. Might be a few days....
I meant to look at the code for this first, but I fell over the other one and did some work on that instead - and now it's quite late.
Don't rush yourself, I'm going to be out of the office for a few days! :-)
NeoPa 32,556
Expert Mod 16PB
This can be added to any module at any time. Subsequent compilations will pick up any references which are not already Dimmed.
The Require Variable Declaration option just ensures that any modules created after that point get that created with that option already in place. It won't change any existing modules.
I will not get to look at your code at work due to my not having your database here to hand (my policy decision not to work on non-work databases at work). This evening will be my next opportunity. If I forget myself as far as not doing so before you return from your beach holiday (burning with envy) then drop in a quick reminder for me at that time.
NeoPa 32,556
Expert Mod 16PB
I've done most of this from MegaLog's starting code to be honest, but there are a few minor changes, and it does compile. - Option Compare Database
-
Option Explicit
-
-
Private Sub BOX_NUM_AfterUpdate()
-
Dim db As DAO.Database
-
-
Set db = CurrentDb
-
With db.OpenRecordset("tblORDERS", dbOpenDynaset)
-
Call .FindFirst("[Order_Num]='" & Me.ORDER_NUM & "'")
-
If (Not .NoMatch) _
-
And (IsNull(!DATE_SHIP)) Then
-
Call .Edit
-
!DATE_SHIP = Date
-
Call .Update
-
End If
-
End With
-
End Sub
See if this works for you. If not, then let's see where it fails or stops or, failing either of those we'll look at breakpointing the code to see what's happening that we don't expect ( Debugging in VBA can help with this).
Well....this code is running (compiling?) but it's still not updating my table. :-( I have investigated some on debugging previously but I definetely will not say I know how to do it. Usually when my code is "bad" it won't run at all. So this will be a new challenge for me.
I did also try to attach it to the ORDER_NUM after update just to be sure if was seeing the Order_Num before trying to update the table. Got the same results....none.
If it's running but nothing happens then it's probably failing on the match. - Call .FindFirst("[Order_Num]='" & Me.ORDER_NUM & "'")
Put stops in the code on this line and after to see if it's passing through NeoPa 32,556
Expert Mod 16PB
If I'm on Skype when you get to look at this then you can try giving me a call Danica. I'd be happy to take you through a debugging session. We could work on this code together, and you would : - Find the problem.
- Get some proper debugging experience under your belt.
Two questions on Bytes were answered with this same piece of code. Initially these were two separate pieces of code. But here it is nonetheless, in case someone can gain something from it in the future. - 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
-
Thanks to everyone who helped out!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jozef |
last post by:
Hello,
I'm trying to check for and add a field to a table to a back end database
through code. The problem I've been faced with is changing permissions,
because I have to use administer...
|
by: Brett |
last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a
record in table2, then the not null fields in table1 that correspond to
the records in table1 needs to be updated to match the...
|
by: ormor |
last post by:
Hi friends,
I am new to MS Access. I have desiged a Form wherein I would like to update the field based on some calculation derived from the previous fields. How this can be done.
I want...
|
by: Colleen |
last post by:
Hello All,
I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would...
|
by: kunal0101 |
last post by:
Hi,
I am a new user to access and am building a database to capture the
rotation dates for employees.
There is a table called "Rotation schedule" which contains "name",
"Duration (in weeks) and...
|
by: dee |
last post by:
I have a table 'LeadHistory' which has among others, the following
fields.
Salesman(Text)
SalesmanAssmntDate(Date/Time)
Disposition(Text)
I also have a table 'LookUpSalesman' which has among...
|
by: roveagh1 |
last post by:
Hi
I've been using the 2 year old link below to repeat values from previous record field into current corresponding field. It's worked fine for text but the last piece of advice was to use the same...
|
by: Splattman |
last post by:
How do I use VBA Code to update a field in a table in Access? I am trying select case queries and do while loop queries. I don't know how to open the source and then update the field I need to.
|
by: saagardn |
last post by:
I am using Access 2007 on an XP machine. Is it possible to update one field with multiple values using a single SQL statement? I have tired to do this with the query builder, but keep getting a...
|
by: =?Utf-8?B?Umljaw==?= |
last post by:
I need to enable or disable a Textbox field in the ReportViewer based on the
value of another textbox field (field value is from the dataset ) on the
report (rdlc).
How do I dynamically update...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| | |