How to pull field from related table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Paul said:
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])
Click to expand...


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
 
Thanks Marshall



Marshall Barton said:
Paul said:
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])
Click to expand...


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
Click to expand...
 
Back
Top