Populating Values

[Abba Cohen]

I will try to make it as concise as possible.

I got three tables

tblBrands
-------------
BrandID (PK
BrandName

(Each Brand has different series. So I created a series table)

tblSeries
-------------
SeriesID (PK)
BrandName
SeriesName


tblWatches
---------------
WatchID (PK)
BrandName (*)
SeriesName (**)

(*) populated list box with query SELECT BrandName FROM tblBrands ORDER BY BrandName - This worked fine

here where it gets tricky

(**) this column I need to only show the series under the Brand that I chose. So I use this line SELECT [Series].[SeriesName] FROM Series WHERE [Brands].[BrandName] = [Series].[BrandName];

It works in a wierd way where it opens an alert asking me to type the Brand value. But I want it to take the value of BrandName automatically from the current record.

Who can help me to solve this?

Thanks,
AC

[pod]

You must include all tables (Series and Brands) that are involved in your query ...

SELECT [Series].[SeriesName] 
FROM Series, Brands
WHERE [Brands].[BrandName] = [Series].[BrandName]

P:oD

[mshmyob]

Actually the first thing you should do is normalize your database.

You do not link tables by descriptive names, they should be linked via the PK/FK.

So for instance in your tables tblBrands and tblSeries you are linked via the BrandName when you should change the BrandName field in the tblSeries to BrandID and link it to the BrandID field of tblBrand.

Same goes for tblWatches - it looks to be a bridge table.

cheers,

[pod]

@mshmyob: how gauche of me not to have mentionned the linking by the PKs...that is why I do not have a E beside my avatar ;)

[mshmyob]

What do you expect from someone from Ottawa :) now if you were from the centre of the Universe (Toronto) you would have mentioned it. (lol).

I have seen many posts from you and they are always spot on. Besides I had to pay big bucks to Neo to get that "E" :)

cheers,

[Abba Cohen]

Thanks for your quick response.

I have solved the problem.

I have created a form and synchronized the combo boxes via VBA using this code.

Private Sub BrandName_AfterUpdate()
    Me.SeriesName.RowSource = "SELECT SeriesName FROM" & _
                            " tblSeries WHERE BrandName = '" & Me.BrandName & _
                            "' ORDER BY SeriesName"
    Me.SeriesName = Me.SeriesName.ItemData(0)
End Sub