How to pull field from related table

[Paul B.]

Hello all....

I need to pull the field [ServiceCentreName] from the tblServiceCentreInfo.
On my form, I have a reference to the field [ServiceCentreNumber] in the
tblVehicleInfo.

I want to display the [ServiceCenterName] on the form instead of the
[ServiceCenterNumber]

I am current using:
DLast("[ServiceCenterNumber]","tblVehicleInfo","[VehicleNumber]="_
&[VehicleNumber])

to get the ServiceCenterNumber onto the form.

Thanks in advance

 

[Marshall Barton]

I need to pull the field [ServiceCentreName] from the tblServiceCentreInfo.
On my form, I have a reference to the field [ServiceCentreNumber] in the
tblVehicleInfo.

I want to display the [ServiceCenterName] on the form instead of the
[ServiceCenterNumber]

I am current using:
DLast("[ServiceCenterNumber]","tblVehicleInfo","[VehicleNumber]="_
&[VehicleNumber])

A DLookup would be a little clearer than DLast.

It looks like you're trying to get the name from the wrong
table. Try something more like:

DLookup("ServiceCentreName", "tblServiceCentreInfo",
"ServiceCentreNumber=" & Me.ServiceCentreNumber)

If you're using that as a text box expression, don't forget
the = sign in front.

Alternatively, it would probably be faster and easier if you
joined the two tables in the form's record source query:

SELECT tblVehicleInfo.*,
tblServiceCentreInfo.ServiceCentreName
FROM tblVehicleInfo INNER JOIN tblServiceCentreInfo
ON tblVehicleInfo.ServiceCentreNumber =
tblServiceCentreInfo.ServiceCentreNumber

 

[Paul B.]

Thanks Marshall

I need to pull the field [ServiceCentreName] from the tblServiceCentreInfo.
On my form, I have a reference to the field [ServiceCentreNumber] in the
tblVehicleInfo.

I want to display the [ServiceCenterName] on the form instead of the
[ServiceCenterNumber]

I am current using:
DLast("[ServiceCenterNumber]","tblVehicleInfo","[VehicleNumber]="_
&[VehicleNumber])

A DLookup would be a little clearer than DLast.

It looks like you're trying to get the name from the wrong
table. Try something more like:

DLookup("ServiceCentreName", "tblServiceCentreInfo",
"ServiceCentreNumber=" & Me.ServiceCentreNumber)

If you're using that as a text box expression, don't forget
the = sign in front.

Alternatively, it would probably be faster and easier if you
joined the two tables in the form's record source query:

SELECT tblVehicleInfo.*,
tblServiceCentreInfo.ServiceCentreName
FROM tblVehicleInfo INNER JOIN tblServiceCentreInfo
ON tblVehicleInfo.ServiceCentreNumber =
tblServiceCentreInfo.ServiceCentreNumber