Previous records help!

[Guest]

Thanks so much for your help!

We are using Access 2003 and have a form (based on an underlying query) that
is used to keep track of 5 vehicles being used by any number of people on
any number of days. Here are the different fields.
Vehicle# (txt box) End Date (txt box)
Org (combo box) End Time (txt box)
DeptNo (combo box) Begin Miles (txt box)
Destination (txt box) End Miles (txt box)
Start Date (txt box) Driver (combo box)
Start Time (txt box)

What we would like the form to do is when a vehicle is requested (there
could be three requests on three different days) and we enter any of the 5
vehicle numbers in the form, then the last or current End Miles automatically
becomes the new Begin Miles for that vehicle as well as updates the other
vehicle requests we have for the future.

Thanks so much for providing this service, it has helped me in a few
different instances.
MADBLover

 

[Al Campagna]

JoJo,
First, better not to use "#" in control names. The "#" is used as a date
signifier, and may cause problems down the line. I"ll use VehicleNo.
We're at a new record, and we'll use the VehicleNo AfterUpdate event to
trigger the determination of say... Vehicle 3's last EndMiles

Private Sub VehicleNo_AfterUpdate()
BeginMiles = DMax("[EndMiles]","tblYourTable","VehicleNo = " &
VehicleNo)
End Sub

Didn't test the syntax, but that should do it...
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Good morning/evening Al,

Thanks so much for your quick reply! I tried the code in the VehicleNo
AfterUpdate event as well as BeginMiles AfterUpdate event and in the forms
AfterUpdate event but to no avail. I checked everything I could think of to
check and all seems good. Here's what I entered in the events,

Private Sub VehicleNo_AfterUpdate() (unless in different field)
BeginMiles = DMax("[EndMiles]","DispatchTBL","VehicleNo = "& VehicleNo)
End Sub

The only thing I changed was the "tblYourTable", I put our "DispatchTBL." I
must be missing something but not sure what it is. Oh, and by the way, the
field I said was named Vehicle# was actually the label name, the field is
named VehicleNo.

Thanks for taking your time to help me, it is greatly appreciated. I'm
moving the end of July and want to make sure I leave the database (my baby)
in good shape!

MADBLover

JoJo,
First, better not to use "#" in control names. The "#" is used as a date
signifier, and may cause problems down the line. I"ll use VehicleNo.
We're at a new record, and we'll use the VehicleNo AfterUpdate event to
trigger the determination of say... Vehicle 3's last EndMiles

Private Sub VehicleNo_AfterUpdate()
BeginMiles = DMax("[EndMiles]","tblYourTable","VehicleNo = " &
VehicleNo)
End Sub

Didn't test the syntax, but that should do it...
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

Thanks so much for your help!

We are using Access 2003 and have a form (based on an underlying query)
that
is used to keep track of 5 vehicles being used by any number of people on
any number of days. Here are the different fields.
Vehicle# (txt box) End Date (txt box)
Org (combo box) End Time (txt box)
DeptNo (combo box) Begin Miles (txt box)
Destination (txt box) End Miles (txt box)
Start Date (txt box) Driver (combo box)
Start Time (txt box)

What we would like the form to do is when a vehicle is requested (there
could be three requests on three different days) and we enter any of the 5
vehicle numbers in the form, then the last or current End Miles
automatically
becomes the new Begin Miles for that vehicle as well as updates the other
vehicle requests we have for the future.

Thanks so much for providing this service, it has helped me in a few
different instances.
MADBLover

 

[John W. Vinson]

Good morning/evening Al,

Thanks so much for your quick reply! I tried the code in the VehicleNo
AfterUpdate event as well as BeginMiles AfterUpdate event and in the forms
AfterUpdate event but to no avail. I checked everything I could think of to
check and all seems good. Here's what I entered in the events,

Private Sub VehicleNo_AfterUpdate() (unless in different field)
BeginMiles = DMax("[EndMiles]","DispatchTBL","VehicleNo = "& VehicleNo)
End Sub

The only thing I changed was the "tblYourTable", I put our "DispatchTBL." I
must be missing something but not sure what it is. Oh, and by the way, the
field I said was named Vehicle# was actually the label name, the field is
named VehicleNo.

If VehicleNo is a Text type field, you need some quotemarks. You'll also need
some provision for the very first time a vehicle is entered into the table -
as written the code will store a NULL value in BeginMiles. You can make
BeginMiles a Required field in the table to force the user to enter the
initial odometer reading in this case.

If it's a text field try

BeginMiles = DMax("[EndMiles]","DispatchTBL","VehicleNo = '"& VehicleNo & "'")

Spacing it out for clarity, that's

"VehicleNo = ' "& VehicleNo & " ' ")

but don't put the spaces in your code.

John W. Vinson [MVP]

 

[Guest]

Thank you so much guys!!!!!

After looking over the code again I realized I needed brackets around both
the VehicleNo and I added the extra quotes. You guys are awesome!!
--
MADBLover

Good morning/evening Al,

Thanks so much for your quick reply! I tried the code in the VehicleNo
AfterUpdate event as well as BeginMiles AfterUpdate event and in the forms
AfterUpdate event but to no avail. I checked everything I could think of to
check and all seems good. Here's what I entered in the events,

Private Sub VehicleNo_AfterUpdate() (unless in different field)
BeginMiles = DMax("[EndMiles]","DispatchTBL","VehicleNo = "& VehicleNo)
End Sub

The only thing I changed was the "tblYourTable", I put our "DispatchTBL." I
must be missing something but not sure what it is. Oh, and by the way, the
field I said was named Vehicle# was actually the label name, the field is
named VehicleNo.

If VehicleNo is a Text type field, you need some quotemarks. You'll also need
some provision for the very first time a vehicle is entered into the table -
as written the code will store a NULL value in BeginMiles. You can make
BeginMiles a Required field in the table to force the user to enter the
initial odometer reading in this case.

If it's a text field try

BeginMiles = DMax("[EndMiles]","DispatchTBL","VehicleNo = '"& VehicleNo & "'")

Spacing it out for clarity, that's

"VehicleNo = ' "& VehicleNo & " ' ")

but don't put the spaces in your code.

John W. Vinson [MVP]