Combo box

[sorcha999]

I have created a form which the user can enter Architect Firms and a subform for Architect Contacts. I have another table Projects where I would like the user to be able to choose the Architect Firm from a combo box and in another combo box below I would like to show just the contacts from within the particular architect's firm they selected in the previous field. At this point I am showing all the architect contacts for all the different offices.

Any help would be greatly appreciated!

 

[DougP]

This is done by passing the results of one item to another

The second item or combo box in your case has its recordsource as so.

Select * From [Architect Contacts] Where [Architect Firm-uniqueID] = '" & me![Architect Firm_Combobox].column(0) & "';"
'Column 0 has the Architect Firm's unique ID

So the "Architect Contacts" table has the following

ContactID ' uniquely identifies the contact.
Architect Firm-uniqueID 'uniquely identifies the Firm they are with.
ContactName 'their name and so on...
Contact Address
etc.

Also you have to requery the contacts Combo box in the Firms After update event
So when you pick a firm the Contacts will reflect the new data.

sub Architect Firm_Combobox_afterupdate
me![Architect Contacts_combobox].requery
end sub
DougP, MCP

 

[aliendan]

OK, if you have this, hopfully:

1.The architect firms table has a unique identifier field for each record.
2.The architect contacts table has this same architect firm's unique identifier field and the tables are related one-to-many with referential integrity enabled and cascade update and cascade delete enabled in the relationship.

The combo for the architect firms should have the unique field included in the sql for the combo and this column will be selected and the bound column for the combo box.

The architect contacts combo box should also have the archtect firm unique field in the sql for same. In the sql for this combo the criteria for the architect firm unique identifier should point to the architect firm combo box.

Include a requery for the architect contacts combo in the afterupdate event for the architect firm combo box.

This will make the architect contact combo show only the contacts related to the firm that you choose.
Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[sorcha999]

I did this and received the following error:
"Data type mismatch in critical expression"

The ArchitectFirm table has the following fields:
ArchitectFirmNo --> Autonumber (Field size: Long integer)

The ArchitectContact table has the following fields:
ArchitectContactNo --> Autonumber (field size: Long integer)
ArchitectFirmNo --> Number (Field size: Long integer)

Could this error be occuring because the Autonumber and Number are conflicting? If so, how do I get around it?

 

[aliendan]

If you have the one-to-many relationship set and Access accepts it then there is no conflicts between those fields.

Go into design view of the form and click on the architect contact combo box to bring the properties for it. Click in the Row Source then click the button to the far right of the syntax to bring up the query design window for the combo box. In the Criteria line for the Architect Firm ID field type in this:

[Forms]![Form1]![cboArchitectFirm]

Where cboArchitectFirm will be the name you gave the Architect Firm combo box and Form1 will be the name of the form you're working in. If you have the right field in the Architect Firm combo box set as the Bound Column then you will have the correct value given from the Architect Firm combo when you click in it to tell the Architect Contact combo box what records to display.
Dan Rogotzke
Dan_Rogotzke@oxy.com