Referencing Combobox Columns

[binghamw]

combo boxes - PLEASE HELP!

I have created two tables:

tblDept and tblFile

I have made two combo boxes based on these tables;

When you select a department from the first combo box, the second combo box is filtered based on the department selection.

After selecting the two combo boxes, I would like additional fields from
tblFile to display in text boxes.

Any suggestions on how to do this?

 

[charityg]

The way I would do it is to set the datasource for the form=tblFile.
In the AfterUpdate event of the second combo, set the bookmark of the form equal to the record corresponding to the selection you made in the combo

Like:

Me.RecordsetClone.FindFirst "[tblFileID] = '" & Me![cboFile] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

where tblFileID= the unique identifier for the tblFile table and cboFile is the name of your combo box.

 

[binghamw]

CharityG,

Is the datasource for the form the same as the recordsource? That's what I set to tblFile

When I paste that code into the afterupdate field, I get an error that says:

"Data type mismatch in criteria expression"

and this line is highlighted when I go to debug:


Me.RecordsetClone.FindFirst "[FileID] = '" & Me![cboFile] & "'"

Do you know why it would do that?

 

[Mile-O]

If you have selected extra fields into the SQL of the second combobox in the cascade then, firstly, set the column widths to 0 for these columns.

For the textboxes you want to change value depending on the selection, put the following in their ControlSource:

=[MyCombo].[Column](2)


Where MyCombo is the name of your combo and the number in brackets (2 in this example) relates to the column of the combobox. Bear in mind that 2 is the third column as the column indexes, like most things, start at 0.

 

[charityg]

Sorry about that, in my example my unique identifier was a text field, so the value needed to be enclosed in quotes. Yours is probably a numeric field, in which case you don't need the quotes.

Try:
Me.RecordsetClone.FindFirst "[FileID] = " & Me![cboFile]

 

[Mile-O]

charityg, I think you may have misread the question. The poster does not wish to navigate to the first record in the form's recordset with the value selected in the combobox but to display extra information from the underlying table of the second combobox.

 

[binghamw]

CharityG,

Thank you SO much, that has really helped me out.

 

[binghamw]

Mile,

I used your example exactly of the [tblCars] and [tblModels] from a previous thread.

So for the second combo box, I didn't have the extra columns in there.

I tried the control source [for the text box]: =cboModels.model and it didn't work because I didn't have multiple columns selected in the second combo box.

So when I used charityG's code, it worked.

How would I do it from your method?

 

[charityg]

I think Mile-O-Phile misread your question. The method will work, but requires more coding in that the textboxes you want to display the extra information are unbound and require you to set the text property for each textbox in code.

 

[Mile-O]

The SQL for the second combobox was:

SELECT ModelID, Model FROM tblModels WHERE CarID = [Forms]![MyForm]![cboCars] ORDER BY Model;

So, say there was an extra two columns in tblModel and these fields were Engine and Doors.

The SQL would change to:

SELECT ModelID, Model, Engine, Doors FROM tblModels WHERE CarID = [Forms]![MyForm]![cboCars] ORDER BY Model;

In the combobox called cboModels, the ColumnCount would change to 4 to reflect the 4 columns. The ColumnWidths would be changed to 0cm;2cm;0cm;0cm so that only the second field showed.

On two new textboxes on the form (called txtEngine and txtDoors) they would initially have their Enabled property set to False and locked property set to True. Their ControlSource would be: =[cboModels].[Column](2) and =[cboModels].[Column](3) respectively.

Now, as you select from tblModels, the two textboxes show the engine and doors value for that particular model as defined in the table.

 

[binghamw]

Mile,

Thanks, that worked.

 

[binghamw]

Mile,
What if I want to do the exact same thing, but instead of have the first selection being a combo box, I use a text box to do a search.
I then want the results to show up in the second combo box to pick a selection.
Does this make it a lot more complicated or can you simply change a few names in the original cascading combo box form as I did above?

Also, will there be problems if I use the same names for the combo boxes on different forms?

 

[Pat Hartman]

Join the lookup table to the main table and use that query as your recordsource. You can choose whatever columns you need from both tables. Then whenever you choose from the combo or type in a value, the cooresponding values from the lookup table will AUTOMATICALLY populate.

If you want to read more about this search for "autolookup query" - here and in help.