Showing a field on a form based on another related field

[Guest]

I am looking to include some text on a form which shows the service interval
miles between services. This is saved in a table ‘Service Intervals’ and is
based on the Make ID of the vehicle-car. For example Renaults are only
serviced every 9000 miles but Vauxhalls 10000.

Service Intervals table set up is as follows:
Make ID (foreign key) (text)
ServiceIntMiles-Hours (text)

My Form is designed mainly on my Vehicles-Cars table:
Registration Number (PK) (text)
Make ID (foreign key) (text)
Model ID (foreign ke) (Text)
Plus other fields associated with the vehicle-car

The Make ID on the form is a combo box which is taken from the ‘Makes table’.

Now, for the hard part (well the bit I’m having trouble with)….

I want the text box on the form to show the ServiceIntMiles. How please?

Surely it needs to look at the make id of the current record displayed then
look at the service interval table to see what the result should be and then
display it.

Can anyone help? Am I explaining myself well enough?

 

[Guest]

I am not quite sure I am following your question, but in general one approach
would be to add the service interval to the "Make" combo box row source query
(I will call the combo box cboMake). If Service Interval appears in the
third column of the query, you would reference Column(2). It would be
Column(2) because the first column is Column(0), although that doesn't need
to be specified in most cases. The After Update event of cboMake could be
Me.txtInterval = cboMake.Column(2), where txtInterval is the text box
containing the service interval. txtInterval could be referenced in the
form's On Current event similarly to the Before Update event for the combo
box, except you could check for a value in txtInterval first:
If Not Isnull me.cboMake Then
me.txtInterval = me.cboMake.Column(2)
End If

You could also store the Service Interval value, depending on the details of
the database. I'm not quite following the purpose of each record in the
Vehicles table. For one thing, the relationships are not clear. Do the
fields you have identified as foreign keys relate to each other? Why is
Model ID identified as a foreign key? These questions may not matter, but if
you need further suggestions then a clearer picture of your project would
help.

 

[Guest]

Is there anyway I can add images to this newsgroup so that I can explain more
clearly my intention.

 

[Guest]

I'm not sure, but I don't think so. Images are not necessary. What problem
are you attempting to solve by making this database? Are customers involved,
or is this a fleet of cars owned by one company, or what? A simple
explanation of the database's general purpose will go a long way.

 

[John Vinson]

I want the text box on the form to show the ServiceIntMiles. How please?

The simplest way would be to include the service miles table in the
Query upon which the form is based, joining it by the model ID. Then
simply add the ServiceIntMiles field as the control source of the
textbox.

Alternatively, you can set the Control Source of a textbox to

=DLookUp("[ServiceIntMiles]", "[Service Intervals]", "[Make ID] = " &
[Make ID])

Note that using blanks in fieldnames is somewhat undesirable; if you
do so, you must always use [brackets] to delimit the fieldnames.

John W. Vinson[MVP]