Combo Box dependent upon another Combo Box entry

[nastar1]

I'm having problems getting the correct configuration for this to work.

I have a form with a combobox called OrgID and another called OffSymID. At present the rowsource for the OffSymID is a fieldname of the same name from a Table called OffSym with criteria as OffSym.* and OffSym in Ascending. This results in a list of repeating Office Symbols were several Organizations uses the same type of Office Symbol. In trying to solve this, I tried to rewrite the rowsource to include the criteria for OffSym as forms!frmAddDelTics.OrgID

This resulted in a drop down with no data when the form frmAddDelTics is run for a new record. It also blanked out the OffSym field for all existing records.

I thought perhaps the problem may be tied to the need for using the Column property, but my initial attempts to use that failed as well. I don't recall the error, but it may have been the syntax I was trying to use.

Any tips on how I can resolve this without major db re-structuring?

 

[TheAceMan1]

How are ya nastar1 . . .

The [green]key to synchronizing[/green] the combo's is [blue]a common field that exist is both RecordSources![/blue]

When the above is true, its a simple matter of requerying [blue]OffSymID[/blue] in the [blue]AfterUpdate[/blue] event of [blue]OrgID[/blue], to get your results in [blue]OffSymID[/blue]! To be sure ... be enlightened about this ... [blue]OffSymID[/blue] shouldn't initially show any records until a selection is made in [blue]OrgID![/blue]

Now as you've mentioned, the data you desire to synchronize with from [blue]OrgID[/blue], may require use of the [blue]Column[/blue] property. Be aware: [purple]you can't use the column property in a query[/purple] and will have to return this value thru a function!

Also, you only include in the RowSource of a combo the fields that you need! Using All (*) only slows down loading of the combo!

Since [blue]pertinent info[/blue] ie yet to be realized, what you need to do is provide the SQL of both combo's, stipilating only those fields that are necessary ([blue]espcially the all important common field and its data type[/blue]). There are several paths this could take depending on your answer.

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Woops! . . .

I meant to say in the beginning:

[blue]The key to synchronizing the combo's is a common field that exist is both [purple]RowSources![/purple][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nastar1]

I understand not using the * in the query, this was the default Access assigned to those cbo's.

The common field between the two combo's is OrgID. It is the primary key in the Orgs table and the foreign key in the OffSym table.

SQL for the cboOrgID is:
SELECT DISTINCTROW Orgs.*, Orgs.Orgs
FROM Orgs
ORDER BY Orgs.Orgs;

SQL for the cboOffSymID is:
SELECT DISTINCTROW OffSym.*, OffSym.OffSym
FROM OffSym
ORDER BY OffSym.OffSym;

Orgs table structure is:
OrgID Autonumber
Orgs Text
GrpID Number

OffSym table structure is:
OffSymID Autonumber
OffSym Text
OrgID Number

The only field I wish to have displayed in the cboOrgID is the Orgs.

The only field I wish to have displayed in the cboOffSymID is the OffSym. And the synchronize would be only display those OffSym entries that relate to the Orgs shown in the cboOrgID.

Let me know if more info is needed. Thanks

 

[nastar1]

I'm getting closer to a workable solution. On new records the cboOffSym now displays only those entries that relate to the cboOrgID. The only problem is that when I cycle back through existing records, all of them show a blank in the cboOffSymID rather than the entry that was previously input. So I need to change my code to only apply to 'new records'. I could use some guidance in how I could get that started.

The changes that got me this far:

Added Me!cboOffSymID.requery to the AfterUpdate of the cboOrgID.

Added forms!frmAddDelTics.OrgID to the rowsource query criteria of the cboOffSymID.

Removed the Orgs.* from the cboOrgID query build window and added only OrgID and Orgs.

Removed the OffSym.* from the cboOffSymID query build window and added only OffSymID, OffSym and OrgID with the criteria shown above.

Any ideas how I can get my existing records to display the OffSym?

 

[TheAceMan1]

nastar1 . . .

[blue]The RowSource of a bound combobox on a continuous form is the same for all records![/blue] If previous selections do not exist in the new changed recordset of the combo, those values can't be displayed . . .

You'll have to unbound the combo and update a textbox in its place.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886