Cascading Combo Box - How to save selected item?

[Pascale]

I have the following tables:

1. DATA (to store data for all my records). First 3 columns are DataID, Sector Type, and Sector Name.
2. TblSectorType (with SectorTypeID, and SectorType) Sector types are: Operational and Corporate.
3. TblSector (with TblSectorID, Sector, SectorLongName, and TblSectorTypeID) - Sector is the acronym vs long name. Five Sectors are "Corporate"(1) while the others are "Operational"(2).

I have a form with two cascading combo boxes

1. Sector Type with two listed choices: "Corporate" and "Operational"
2. Sector with a list of Sectors (both acronyms and associated long names are listed) filtered based on selected type above.

The two combo lists seem to work fine in terms of filtering, using an AfterUpdate requery macro for Sector type, and a SELECT criteria for the Sector Row Source.

The problem is that my selections do not save properly in the DATA table. Sometimes, a number ID?) gets inputted but some other times, the Sector Type field remains blank (???). I can never get the actual Sector Type (Operational or Corporate) to show in DADA table nor in my form after selection. Similar issue with Sector value.

Can someone help me with that?

[alansidman]

Is your form bound to your table?

[Pascale]

My DATA table is the Record Source. Is this what you mean?

[CJ_London]

is your SectorType field a lookup field in your table?

[alansidman]

Following up on Ajax Question: http://access.mvps.org/access/lookupfields.htm

http://dbageek.blogspot.com/2004/10/...fields-in.html

[Pascale]

is your SectorType field a lookup field in your table?

Got it. So I made both my SectorType and my Sector fields lookup fields in DATA table. I made filters. Both fields now input correctly in the table. Thanks! Now there is an issue with the form. The SectorType shows OK. As for the Sector, it sometimes do, sometimes not. However, if I re-select the SectorType, then the right Sector gets automatically selected. But it disappears as soon as I move to the next record.

Thanks for your help!

Pascale

[CJ_London]

the point was that lookup fields cause problems, not that they provide a solution

But it disappears as soon as I move to the next record.

this implies you have a continuous form

[Pascale]

Sorry. I don't understand. What should I do? Should I make my form "continuous"? How do I do that?

[CJ_London]

Should I make my form "continuous"?

no, just as you shouldn't have changed your fields to lookups.

I'm trying to get some information as to how your form is supposed to work. Please provide the following:

For both combo's provide the rowsource, the controlsource, the column count and the bound column values plus details of any code/macros associated with these two controls

Also provide the recordsource to the form

[Pascale]

Field	SectorType	Sector
Row Source	SELECT TblSectorType.TblSectorTypeID, TblSectorType.[Sector Type] FROM TblSectorType ORDER BY TblSectorType.[Sector Type];	SELECT TblSector.TblSectorID, TblSector.Sector, TblSector.[Sector - Long Name], TblSector.TblSectorTypeID FROM TblSector WHERE (((TblSector.TblSectorTypeID)=[Forms]![DATA ENTRY]![CbxSectorType])) ORDER BY TblSector.Sector;
Control Source	SectorType	Sector
Column Count	2	3
Bound Column	1	1
Code/Macro	Event - AfterUpdate: Requery CbxSector	NIL
Record Source to the Form	SELECT DATA.DataID, DATA.Sector, DATA.[Sector Type], DATA.Contact, DATA.[Meeting Date], DATA.Issue, DATA.[Mitigation/Initiative], DATA.Timeframe, DATA.[Strategic Linkage], DATA.[Summary Notes] FROM DATA;

[CJ_London]

to clarify - what are your storing in

DATA.[Sector Type]?

The TblSectorType.TblSectorTypeID or TblSectorType.[Sector Type]?

And do you have any code in the form current event to requery CbxSector? Otherwise if the next record has a different value for DATA.[Sector Type], it won't show anything.

Other issues - the control source to cbxSectorType is SectorType, but in your recordsource you are bringing through DATA.[Sector Type]

Just a tip - don't have spaces or non alphanumeric characters in field and table names - at some point you will find you have wasted hours or days trying to resolve a misleading error message

[Pascale]

[QUOTE=Ajax;337029]to clarify - what are your storing in
DATA.[Sector Type]?
The TblSectorType.TblSectorTypeID or TblSectorType.[Sector Type]?

I am storing the SectorType (Corporate or Operational)

[Pascale]

And do you have any code in the form current event to requery CbxSector? Otherwise if the next record has a different value for DATA.[Sector Type], it won't show anything.

I cannot see the codes. In After Update, I clicked [...] then selected Macro Builder. Then from the list, I picked "Requery" and then I inputted the control Name "CbxSector".

[Pascale]

Other issues - the control source to cbxSectorType is SectorType, but in your recordsource you are bringing through DATA.[Sector Type]

How to fix that?

[Pascale]

Just a tip - don't have spaces or non alphanumeric characters in field and table names - at some point you will find you have wasted hours or days trying to resolve a misleading error message
[/QUOTE]

Thanks for the tip!