Help with lookup

[jcrispy3]

Hi all. I'm a noob to access so bare with me...



I want to create a lookup menu that is determined by two fields. Here is an example of what I have:

I have one table with say car manufacturers.
I have another table with say the make and year of cars. The manufacturer field is linked between the two tables.

I'm trying now to create a third table to add specific details about each car. But I only want to add specific details for cars that are in the previous two tables (that is why I created those two tables, I want to control what car manufacturers, makes and years are allowed).

In my new table, if the field in row A is "Ford", and the field in row B is "F-150", how can I provide a dropdown list of only the years entered for Ford F-150's from the previous table?

[Robeen]

In the Row Source Property for the ComboBox, you can put a Select Statement something like this:

This assumes that:
1. Your Make/Year Table is named Make_Year,
2. That you have a field in that Table named ModelYear,
3. That you have a field in that Table named Manufacturer &
4. That you have a field in that Table named Model.
(I think I got it all!! )

 
SELECT DISTINCT [Make_Year].[ModelYear] FROM [Make_Year] Where [Make_Year].[Manufacturer] = "Ford" And [Make_Year].[Model] = "F-150" ORDER BY [Make_Year].[ModelDate];

You'll have to change the field names according to what you've named the fields in your table.

I hope this helps.

[jcrispy3]

Thanks Robeen. This is helping but not there yet.

Instead of saying "Ford", I want to use data from the table that I am entering data into. So say I have table called [CarManuf] which just has car manufacturers. Then I have another table called [CarModel]. In this table, I have two fields: one with a dropdown list of manufacturers pulled from [CarManuf], and another with the model name. So what I want to do is...

1. I create a third table, called [CarYear]
2. I have a field in the table called [Manuf] which is linked to the [CarManuf] table
3. My next field is [Model]. How can I create a drop down list that only gives me [Model] items that match the [Manuf] that I already entered?

[RayMilhon]

Not sure you need a manufacturer table as No Car Model will have 2 manufacturers. car Model table can include a field called manufacturer. You can create a drop down that has the following SQL for the row Source
Select Manufacturer From tbl_Model Group By Manufacturer. Will give you a distinct list of the Manufacturers.
Then in the On change event of the manufacturer combo box you can put the following.

cbo_models.Rowsource = "Select Models From tbl_models where manufacturer = cbo_manufacturer Group By Models
cbo_models.requery.

[Robeen]

Sorry.

I misunderstood your requirement.

I was assuming that you would be entering data into your Table using a Form.

But it seems to me that you are talking about entering data directly into the Table - is that correct?

[jcrispy3]

For my case, pretend that a car model can have 2 manufacturers. Here is what I have so far for the Row Source:

SELECT DISTINCT [CarModels].[Model] FROM [CarModels] Where [CarModels].[Manuf] = [CarYear].[Manuf] ORDER BY [ModelName];

However, when I go from design view to datasheet view, it asks me for [Manuf] and only provides me with models that match that manufacturer.

@Robeen - Yeah, I'm not using a form (right now). It's just easier to enter my data right into the table for now.

[Robeen]

I think you might need to have this as your SQL statement:

 
SELECT DISTINCT [CarModels].[Model] FROM [CarModels], [CarYear] Where [CarModels].[Manuf] = [CarYear].[Manuf] ORDER BY [ModelName];

[jcrispy3]

Ok. Now it is only providing me with models that match the manufacturer in my first entry in the table. How do I get it to be dependent only for the data entered in the row?

[Robeen]

Honestly, I seldom do data-entry directly into a Table - unless I'm modifying one or more values for testing purposes.

I'm just more used to doing the kind of thing you're trying to do in a Form - where I can grab the values that are on the Form for the current record and pass them around to other controls [like Drop-Down Lists for example].

I'm not sure how you would pass your values for the current row to the drop-down-list in your table so that it gives you the correct values in your drop-down list.

Have you researched 'Calculated Fields'?

Sorry if I'm not helping much!! My tables are usually just simple data repositories - no fancy stuff going on in them.

I'll poke around if I get a chance and see if I can come up with a solution.

If you don't hear anything for a while, you might try re-posting.

All the best!!

[RayMilhon]

I also thought you were talking about entering data on a form. I have never found a valid reason to store a calculated field in a table.