Combo box based on another combo box

[lradon]

Access 2000

I have created a combo box that limits its list based on the selection made in a previous combo box.

[b]First Combo Box
Table Name:  BackOffice
BOID	BOCode	BOName[/b]
1	ACCTG	Accounting
2	CENLEND	Central Lending
3	LNSERV	Loan Services
4	REL	Real Estate
5	SA	Special Accounts

[b]Second Combo Box
Table Name:  BOLossTypes
LossID	Type	                BOID[/b]
1	ACH	                1
2	Adjustments	        1
3	Cash Letters	        1
4	Blank	                2
5	DMV	                3
6	Appraisal/Drive-by Fee	4
7	Condo Certification	4
8	Credit Report	        4
9	Employee Error	        4
10	Impounds	        4
11	Income Verification	4
12	Interest	        4
13	Other Losses	        4
14	Posting Error	        4
15	Principal	        4
16	Reconveyance Fee	4
17	Recording Fee	        4
18	Sub-Escrow Fee	        4
19	Tax & Flood Certification4
20	Title Fee	        4
21	Employee Error	        5
22	Short Pay	        5
23	Other Losses	        5

Example:
BOName: Accounting [first combo box]
LossType: ACH, Adjustments or Cash Letters (is displayed, second combo box.)

What is being stored right now is

"1, ACH" instead of "Accounting, ACH"

The two boxes appear to be working correctly while data entering in the form. A selection is made in the first combo box and the next combo box displays the appropriate choices for that selection [the first combo box]. I would like the BOName to populate the field of the first combo box. I would like the Type field to populate the field of the second combo box.

The problem comes in how the data is stored in the table behind the form for the first combo box. The data for the first combo box that is being store is the BOID instead of the BOName. The selection made in the second combo box is storing correctly with the Type field.

I think it has something to do with the Bound properties of the first combo box. I have tinkered around with several of the properties: the bound, column width, column counts and I can't get it right.

These are the settings of those fields for the first combo box:

Row Source Type: Table/Query
Row Source: SELECT [BackOffice].[BOID], [BackOffice].[BOName] FROM BackOffice;
Column Count: 2
Column Heads: No
Column Widths: 0";1"
Bound Column: 1

Can anyone see where my error is, any help would be appreciate.

TIA Laura

 

[ssatech]

And have you tried creating a query with an inner join between the (2) tables on your 2nd combobox?

SELECT DISTINCTROW BOLossTypes.LossID, BOLossTypes.Type, BackOffice.BOName, BOLossTypes.BOID
FROM BOLossTypes INNER JOIN BackOffice ON BOLossTypes.BOID = BackOffice.BOID
WHERE (((BOLossTypes.BOID)=[Forms]![frmName]![Accounting]));

Column Count: 3
Column Heads: No
Column Widths: 0cm;2cm;2cm;2cm
Bound Column: 2

 

[lradon]

I'll try that, however, the second combo box works. This will help the combo box show the BOName and not the BOID?

Thanks

 

[lradon]

No, that doesn't seem to work, but, I will still keep trying. Thank you.

 

[ProgramError]

I would try changing the bound column.

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[ssatech]

I would like the BOName to populate the field of the first combo box."

In the field (for first combobox)OF YOUR TABLE(e.g. accounting)for the recordsource of this form.

Try:
Field Name: Accounting
Data Type: Number
Display Control: Combobox
Rowsource: SELECT BackOffice.BOID, BackOffice.BOName FROM BackOffice;
Bound Columm: 1
Column Count: 2
Column Widths: 0cm;2.54cm