Help With a Query Pop Up

[Robert T]

I designed a database that tracks my family's auto maintenance. I created a
tabbed SubForm that works well. The tabs are Oil Changes, Maintenance, Tire
Rotation, and Emission Control. Except for the Maintenance Tab, I would like
a query to pop up on entering each tab that tells me the LAST [DateService]
and [Mileage].

The query should then tell me the NEXT Date or Mileage that service should
be performed. Here's an example of what I would like on the tabbed subform.

Oil Changes Tab
------------------
I would like the query to
1. Automatically run upon entering the tab.
2. Find the most recent DateService and Mileage record.
3. Then add 120 days to the [DateService] field and 5,000 miles to the
[Mileage] field.

That way I will know when the next oil change is due by either Time or
Mileage.

Thanks,
Robert

 

[Arvin Meyer [MVP]]

Bind a subform to an aggregate (totals) query that selects the TOP 1 record,
with the datefield descending. Add calculated unbound textboxes to the form
which use [DateField] + 120 and [Mileage] + 5000

 

[Robert T]

Arvin:

In retrospect, I didn't explain myself properly. In addition, maybe I don't
quite understand what you're suggesting.

The subforms are already bound to a query which shows every oil change for
that vehicle. However, if I understand what your suggesting, I will only see
the LAST oil change plus the date and time of the next one. Is that correct?
If so, that's not what I want Arvin.

What I want to see is:

1. All of the oil changes for each vehicle.
2. In addition to seeing every oil change, I want to click on a button [or
if it's possible, have it happen automatically upon entering each tab] and
see a a Pop Up form that shows when the next oil change is due either by
mileage or by time.

Robert

Bind a subform to an aggregate (totals) query that selects the TOP 1 record,
with the datefield descending. Add calculated unbound textboxes to the form
which use [DateField] + 120 and [Mileage] + 5000
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I designed a database that tracks my family's auto maintenance. I created a
tabbed SubForm that works well. The tabs are Oil Changes, Maintenance,
Tire
Rotation, and Emission Control. Except for the Maintenance Tab, I would
like
a query to pop up on entering each tab that tells me the LAST
[DateService]
and [Mileage].

The query should then tell me the NEXT Date or Mileage that service should
be performed. Here's an example of what I would like on the tabbed
subform.

Oil Changes Tab
------------------
I would like the query to
1. Automatically run upon entering the tab.
2. Find the most recent DateService and Mileage record.
3. Then add 120 days to the [DateService] field and 5,000 miles to the
[Mileage] field.

That way I will know when the next oil change is due by either Time or
Mileage.

Thanks,
Robert

 

[Arvin Meyer [MVP]]

Either make a second subform showing only the last one, or you will need to
build a recordset on the form's Current event and display a textbox with
both the result, and the calculation (here's some untested code, you need to
substitute the correct field names and add error handling):

Sub Form_Current()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select TOP 1 from MyTable Order By OilChangeDate DESC;"

Set rst = db.OpenRecordset (strSQL)

With rst
Me.txtChangeDate = .OilChangeDate
Me.txtMileage = .Mileage
Me.txtNextChangeDate = Me.txtChangeDate + 120
Me.txtNextMileage = Me.txtMileage + 5000
End With

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin:

In retrospect, I didn't explain myself properly. In addition, maybe I
don't
quite understand what you're suggesting.

The subforms are already bound to a query which shows every oil change for
that vehicle. However, if I understand what your suggesting, I will only
see
the LAST oil change plus the date and time of the next one. Is that
correct?
If so, that's not what I want Arvin.

What I want to see is:

1. All of the oil changes for each vehicle.
2. In addition to seeing every oil change, I want to click on a button [or
if it's possible, have it happen automatically upon entering each tab] and
see a a Pop Up form that shows when the next oil change is due either by
mileage or by time.

Robert

Bind a subform to an aggregate (totals) query that selects the TOP 1
record,
with the datefield descending. Add calculated unbound textboxes to the
form
which use [DateField] + 120 and [Mileage] + 5000
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I designed a database that tracks my family's auto maintenance. I
created a
tabbed SubForm that works well. The tabs are Oil Changes, Maintenance,
Tire
Rotation, and Emission Control. Except for the Maintenance Tab, I
would
like
a query to pop up on entering each tab that tells me the LAST
[DateService]
and [Mileage].

The query should then tell me the NEXT Date or Mileage that service
should
be performed. Here's an example of what I would like on the tabbed
subform.

Oil Changes Tab
------------------
I would like the query to
1. Automatically run upon entering the tab.
2. Find the most recent DateService and Mileage record.
3. Then add 120 days to the [DateService] field and 5,000 miles to the
[Mileage] field.

That way I will know when the next oil change is due by either Time or
Mileage.

Thanks,
Robert

 

[Robert T]

Arvin:

Thanks a lot. I'll be out of the office for almost 2 weeks so I probably
won't have an opportunity to provide any feedback.

Thanks again,
Robert

Either make a second subform showing only the last one, or you will need to
build a recordset on the form's Current event and display a textbox with
both the result, and the calculation (here's some untested code, you need to
substitute the correct field names and add error handling):

Sub Form_Current()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "Select TOP 1 from MyTable Order By OilChangeDate DESC;"

Set rst = db.OpenRecordset (strSQL)

With rst
Me.txtChangeDate = .OilChangeDate
Me.txtMileage = .Mileage
Me.txtNextChangeDate = Me.txtChangeDate + 120
Me.txtNextMileage = Me.txtMileage + 5000
End With

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin:

In retrospect, I didn't explain myself properly. In addition, maybe I
don't
quite understand what you're suggesting.

The subforms are already bound to a query which shows every oil change for
that vehicle. However, if I understand what your suggesting, I will only
see
the LAST oil change plus the date and time of the next one. Is that
correct?
If so, that's not what I want Arvin.

What I want to see is:

1. All of the oil changes for each vehicle.
2. In addition to seeing every oil change, I want to click on a button [or
if it's possible, have it happen automatically upon entering each tab] and
see a a Pop Up form that shows when the next oil change is due either by
mileage or by time.

Robert

Bind a subform to an aggregate (totals) query that selects the TOP 1
record,
with the datefield descending. Add calculated unbound textboxes to the
form
which use [DateField] + 120 and [Mileage] + 5000
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I designed a database that tracks my family's auto maintenance. I
created a
tabbed SubForm that works well. The tabs are Oil Changes, Maintenance,
Tire
Rotation, and Emission Control. Except for the Maintenance Tab, I
would
like
a query to pop up on entering each tab that tells me the LAST
[DateService]
and [Mileage].

The query should then tell me the NEXT Date or Mileage that service
should
be performed. Here's an example of what I would like on the tabbed
subform.

Oil Changes Tab
------------------
I would like the query to
1. Automatically run upon entering the tab.
2. Find the most recent DateService and Mileage record.
3. Then add 120 days to the [DateService] field and 5,000 miles to the
[Mileage] field.

That way I will know when the next oil change is due by either Time or
Mileage.

Thanks,
Robert