Question Forms (1 Viewer)

Russell08

Registered User.
Local time
Today, 02:40
Joined
Feb 21, 2018
Messages
13
I have a database with a calibration form for water quality meters. Every 8 weeks a new meter is calibrated and sent to the end user. An email is auto generated to inform the end user that a new meter is on its way.
The email contains the Serial No of the new meter and asks the end user to return the previous one for calibration. Each end user appears many times in the database.
I need to insert into the email the serial no for the previous meter that the end user had as each user may have more than one meter.

Fields contained on the Form are:- END USER NAME, meter serial number split into two Fields OWNER and NWQIS, and a DEPLOYMENT DATE.
I have tried Queries, but cannot get the
field data ( serial number ) out of the query.
I was thinking maybe have a textbox on the form that when the end user is selected it fills in the textbox with the previous serial number.
Or maybe there is a way with VBA.
Any ideas would be appreciated.
 

Ranman256

Well-known member
Local time
Yesterday, 22:40
Joined
Apr 9, 2015
Messages
4,339
a query should show you the previous meter
and the current new meter.
select * from tMeters where [ownerID]=forms!myForm!ownerID

if you do not want data in the email, remove it from the query.
 

Russell08

Registered User.
Local time
Today, 02:40
Joined
Feb 21, 2018
Messages
13
I have tried a query and manage to get the relevant data, but how do I get the data out of the query so that I can populate the email with the previous serial number automatically.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:40
Joined
May 7, 2009
Messages
19,169
if there is a User Name field in your form, you can code its After Update Event to get the
last serial of the meter:

Private Sub userName_AfterUpdate()
dim meterSerial As String
dim dt As Variant
dt = DMax("[Deployment Date]","yourMeterTable","Owner='" & Me.userName & "'")
If IsNull(dt)=False Then
meterSerial= DLookup("NWQIS","yourMeterTable","Owner='" & Me.userName & "' And [Deployment Date]=#" & Format(dt, "mm/dd/yyyy") & "#")
Me.serialTextbox = meterSerial
End If
End Sub
 

Ranman256

Well-known member
Local time
Yesterday, 22:40
Joined
Apr 9, 2015
Messages
4,339
if using forms, get the data via textboxes:

forms!myform!txtMeter
 

Russell08

Registered User.
Local time
Today, 02:40
Joined
Feb 21, 2018
Messages
13
Arnelgp
thanks for your reply,

I have tried your code in the afterupdate event of the "END USER NAME", it compiled ok but does not appear to work. I have added a textbox named serial to the main form" calibration sheet".
I also already have code in the after update.
This is what I have now:-

Private Sub END_USER_NAME_AfterUpdate()
Me.EMAIL_ADDRESS_ONE = END_USER_NAME.Column(1)
Me.EMAIL_ADDRESS_TWO = END_USER_NAME.Column(2)
Me.USER_PHONE_NUMBER = END_USER_NAME.Column(3)

Dim meterSerial As String
Dim dt As Variant
dt = DMax("[deployment no]", "calibration sheet", "owner='" & Me.END_USER_NAME & "'")

If IsNull(dt) = False Then

meterSerial = DLookup("NWQIS", "calibration sheet", "Owner= '" & Me.END_USER_NAME & "'")
[DEPLOYMENT DATE] = ("#" & Format(dt, "mm/dd/yyyy") & "#")

Me.serial = meterSerial


End If
End Sub
Any Idea?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:40
Joined
May 7, 2009
Messages
19,169
Dim meterSerial As String
Dim dt As Variant
dt = DMax("[deployment no]", "[calibration sheet]", "owner='" & Me.END_USER_NAME & "'")

If IsNull(dt) = False Then

meterSerial = DLookup("NWQIS", "[calibration sheet]", "Owner= '" & Me.END_USER_NAME & "' and " & _
"[DEPLOYMENT DATE] = #" & Format(dt, "mm/dd/yyyy") & "#")

Me.serial = meterSerial
End if

' use Deployment date not the number.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:40
Joined
Feb 19, 2002
Messages
42,981
Let's step back a minute and think about what is actually causing the swap.

-- The old meter has reached 8 weeks in use and must be recalabrated

Shouldn't that query (the one that determines which meters are expiring next week) be the one that is powering the form? In that case, the combo on the form would select the REPLACEMENT meter rather than the one currently in place.

Select * from your table where Date() between DeploymentDate + 49 AND DeploymentDate +56

If you run this every Monday, it will select all the meters that will expire the following week. Adjust the date range to accommodate your processing cycle. But always run the process on the same day to avoid missing anyone. In fact, the whole process can be completely automated. You can schedule a task using Windows Scheduler, it runs the query to find the meters that need replacing, it can send the emails, and it can assign replacement meters and create the shipping documents so they get sent.

When the old meter is received, someone needs to update the record saying it was returned and put it in the to be celebrated section of the warehouse.
 

Russell08

Registered User.
Local time
Today, 02:40
Joined
Feb 21, 2018
Messages
13
arnelgp,
Thanks for your patience.
Still cant get this to work.
Don't know what I am doing wrong.
I have a textbox Named Serial with a Control Source of meterSerial and nothing appears in it when I enter an END USER NAME to start a new record.
Here is the code now:-

Me.EMAIL_ADDRESS_ONE = END_USER_NAME.Column(1)
Me.EMAIL_ADDRESS_TWO = END_USER_NAME.Column(2)
Me.USER_PHONE_NUMBER = END_USER_NAME.Column(3)
Dim meterSerial As String
Dim dt As Variant
dt = DMax("[deployment no]", "[calibration sheet]", "owner='" & Me.END_USER_NAME & "'")
If IsNull(dt) = False Then
meterSerial = DLookup("NWQIS", "[calibration sheet]", "Owner='" & Me.END_USER_NAME & "'and" & "[deployment date] = #" & Format(dt, "mm/dd/yyyy") & "#")
Me.serial = meterSerial
End If
End Sub
Is this correct?
what did you mean by use Deployment date no the number?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:40
Joined
May 7, 2009
Messages
19,169
On the dmax function we need to get the latest date and pass it to dlookup.
Do you have date field on your table?
If you have that use it here

dt = DMax("[dateFieldHere]", "[calibration sheet]", "owner='" & Me.END_USER_NAME & "'")
 

Russell08

Registered User.
Local time
Today, 02:40
Joined
Feb 21, 2018
Messages
13
arnelgp,
This is the code now and still nothing appears:-

Private Sub END_USER_NAME_AfterUpdate()
Me.EMAIL_ADDRESS_ONE = END_USER_NAME.Column(1)
Me.EMAIL_ADDRESS_TWO = END_USER_NAME.Column(2)
Me.USER_PHONE_NUMBER = END_USER_NAME.Column(3)
Dim meterSerial As String
Dim dt As Variant
dt = DMax("[DEPLOYMENT DATE]", "[CALIBRATION SHEET]", "OWNER='" & Me.END_USER_NAME & "'")
If IsNull(dt) = False Then
meterSerial = DLookup("NWQIS", "[CALIBRATION SHEET]", "OWNER='" & Me.END_USER_NAME & "'and" & "[DEPLOYMENT DATE] = #" & Format(dt, "mm/dd/yyyy") & "#")
Me.serial = meterSerial
End If
End Sub

Sorry to be a pain
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:40
Joined
May 7, 2009
Messages
19,169
Add msgbox after:

Dt=dmax()

Also add space to "and"

meterSerial = DLookup("NWQIS", "[CALIBRATION SHEET]", "OWNER='" & Me.END_USER_NAME & "' and [DEPLOYMENT DATE] = #" & Format(dt, "mm/dd/yyyy")
 

Mark_

Longboard on the internet
Local time
Yesterday, 19:40
Joined
Sep 12, 2017
Messages
2,111
Russell,

He is recommending putting it AFTER
Code:
dt = DMax("[DEPLOYMENT DATE]", "[CALIBRATION SHEET]", "OWNER='" & Me.END_USER_NAME & "'")
but before
Code:
If IsNull(dt) = False Then

This is the only place in the code in post #12 where dt = DMAX() is used.
 

Russell08

Registered User.
Local time
Today, 02:40
Joined
Feb 21, 2018
Messages
13
Hi Mark,
I get this error. As per attachment. Screenshot (8).png
 

Russell08

Registered User.
Local time
Today, 02:40
Joined
Feb 21, 2018
Messages
13
Hi Mark,
I get this error.
 

Attachments

  • Screenshot (8).png
    Screenshot (8).png
    93.3 KB · Views: 77

Mark_

Longboard on the internet
Local time
Yesterday, 19:40
Joined
Sep 12, 2017
Messages
2,111
Russell,

Arnel an myself were not saying to add extra code to assign a value to dt. In his original post he used dt=Dmax() to indicated the line ABOVE where you have put 'dt=Dmax()' without fully spelling out everything you had.

Often you will see a reference such as this that is not intended to CHANGE the line being addressed, but to simply address the only instance of dt=Dmax(ANYTHING YOU HAVE) without retyping everything that would be within the call to DMax(All of your stuff).

This is most common when someone is replying, but what is within the function call is neither relevant to their response nor likely to add to the discussion. He could have also type it out fully as

"Put in a msgbox AFTER you use the DMax() function to return a value that you store in the variable dt", but that itself would also be terribly cumbersome.

Now the relevant question would be "What should I display with the msgbox. That line, due to the nature of your questions would be
Code:
msgbox "DMax returned :" & dt & ": for the last meter"
or similar verbiage so that you KNOW if you have an issue with how you are calling the DMax function. For myself, I'd also put your criteria into a string variable you would declare just for this call. I'd show that and see if the criteria is other than what you need.
 

Russell08

Registered User.
Local time
Today, 02:40
Joined
Feb 21, 2018
Messages
13
Jon,
I think I need more info on this, I fact I know I do.
Maybe someone could talk me through it.
Or just amend my code so I could just cut and paste.
This is driving me crazy.
I have amended the code many times and still getting nothing in the textbox.
 

Users who are viewing this thread

Top Bottom