Parent/child relationship in fields?

[Joanne]

Hello,
I have an inventory table that lists all of the software that we own. One
of the fields is chrCompanyName, which is the name of the company that
manufactures the software, i.e. Microsoft, Adobe Systems, etc. The next
field is the name of the specific software that we have from that company,
MSOffice, Acrobat Elements and so on. I want the second field's drop down
list to display only the software that is made by the company listed in the
chrCompanyName field. So if the company name is Adobe Systems, I don't want
the software field to display a choice for Microsoft Access. Is there a way
I can do this? I should add that I'm a relative newbie to Access. Thanks in
advance for any help you can provide.

 

[Jack Leach]

I hope you're doing this in a form and not the actual table (lookup fields in
a table are evil...)

In a form, this is accomplished by setting the second combo box's rowsource
as an sql after the first combo value is selected. To make things a little
cleaner, most people will disable the second combo until the first one is
selected.

So if you've got combo box #1 as ctlCompanyName, and combo box #2 as
ctlSoftwareName, try putting this in the AfterUpdate event of ctlCompanyName:

Private Sub ctlCompanyName_AfterUpdate()
Me.ctlSoftware.Rowsource = "SELECT * FROM YourTableName " _
& "WHERE YourTableName.chrCompanyName = """ & Me![ctlCompanyName] & """"
Me.ctlSoftware.Requery
End Sub

This should return only records that have the same company name as what was
just selected.

Btw, if that happens to be a numeric field, you'll need to change the syntax
of the where statement a bit...

"WHERE YourTableName.chrCompanyName = " & Me![ctlCompanyName]


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)