Composite Parts To Lookup Primary Key

[Survane]

I have a table that lists 400 different cars by manufacturers, makes and models. Each car has a unique ID (autonumber). I am linking this to another table but I only want to store the vehicle ID. I don't want to add three new fields for the manufacturers, makes and models. My user has to enter the manufacturer, make and model of the car and I want to pull the PK from that. I am using a dlookup but I am not sure how to combine the three statements to pull the PK. Each manufacturer is repeated at least 10 times, each make at least 5 times like you can have Ford - Taurus, Focus. I need the combination of all the factors to get the PK. can this be done? Or will I have to add 3 fields?

Thanks,
Survane.

 

[dpimental]

It sounds like you can use a combo box that is set to a query that 3 form controls to get the Manufacturer, Make, and model, which it uses to query the table to get the AutoNumber Id.

Here's how it works.

First, you create a combo box with the following sql ...

SELECT CarInfo.Id
FROM CarInfo
WHERE (((CarInfo.Manufacturer)=[Forms]![MyForm]![Manufactuere]) AND ((CarInfo.Make)=[Forms]![MyForm]![Make]) AND ((CarInfo.Model)=[Forms]![MyForm]![Model]));



A user enters the 3 pieces of information.
Then "clicks" a get my id button

Do a OnClick event to requery the combobox, and then your combobox will be updated with the appropriate id.

David I'm Your Huckleberry!

 

[jebry]

Hi!

If you want to do the DLookUp, you would use it like this:

DLookUp("VIN", "tblAutos", "Manufacturer = '" & txtManufacturer & "' And Make = '" & txtMake & "' And Model = '" & txtModel & "'&quot

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Survane]

I've tried both methods but somewhat to no avail. When I used the SELECT statment, I got the recordsource. I couldn't push the value to cbo box. The dlookups didn't work either. Is there a way to do this in ADO also what if I need to leave the last field blank because the PK is based on the 1st 3 fields how can I do so. My ADO code is attached.

Private Sub cmdVehicleID_Click()
Dim Conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim StrSQL As String
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic

.Open "SELECT tblManufacturer.VehicleTypeID FROM tblManufacturer WHERE (((tblManufacturer.VehicleManufacturer)=[Forms]![frmTeststrVIN]![cboVehicleManufacturer]) AND ((tblManufacturer.VehicleMake)=[Forms]![frmTeststrVIN]![cboVehicleMake]) AND ((tblManufacturer.VehicleModel)=[Forms]![frmTeststrVIN]![cboVehicleModel]));"

Me.txtVehicleTypeID.Text = !VehicleTypeID


End With
rst.Close
End Sub

That's it pretty much. I get the following error when I run the code. "No value given for 1 or more required parameters". When I run the SELECT statement in a query, I get the right PK.

Thanks,
Survane.
---------------------
survane@hotmail.com