Binding Cascaded Combo Boxes

[gagetpro]

Hello-

I habe searched the topics and read numerous posting- but none seems to address binding the results of a combo box.

I have cascaded 2 combo boxes using after update event code but can't seem to get the results bound to a table. I get wrong data type error- text is returned by combo box but table expects numbers for foreign key.

Ideas or suggestions appreciated greatly.

 

[MarkK]

What do you mean that you want to "bind the results to a table?" Are you trying to add a record to a table bound to a form?

 

[gagetpro]

lagbolt-

Thanks for your response. Yes- I would like to display the text in the combo box & store the combo boxes control source property to a tables field (foreign key). The form is bound to this table.

My data base is normalized so I have a separate lookup tables for the values in the combo box and a main table for the form. The problem is that the cbo's display text so they give data type errors when bound to a table that expects numbers.

My tables structure:
L_CoA
CoA_ID AutoNumber PK
CoAName Text

L_CoASub
CoASub_ID AutoNumber PK
CoASubName Text
CoASubCoAs_IDs Number FK (link to L_CoA.CoA_ID)

M_Test
Test_ID AutoNumber PK
TestStuff Text
Testcbo1s_IDs Number FK (link to L_CoA.CoA_ID)
Testcbo2s_IDs Number FK (link to L_CoASub_ID)

The 2 combo boxes are called cbo1 and cbo2.

I have been working on this for some time and any suggestions would be appreciated.

 

[Cosmos75]

Maybe this might help? - Cascading Comboboxes

 

[gagetpro]

cosmos75-
Thanks for the link. A brief look- looks good. It will take a while to review the entire approach. Will report back how it goes.

thanks again

 

[gagetpro]

cosmos-
A very nice database however it accomplishes only displaying the records BUT does not bind/ place the records in a detail table. CascadeCbo2000 has only 3 tables one for each of the combo boxes.

I am trying to store the selections is a additional table- along with other data of course.

I appreciate the link & it is a lot more robust than my solution. I can certainly incorporate its features!
Any further ideas on my main problem?

 

[Cosmos75]

BUT does not bind/ place the records in a detail table.

...

I am trying to store the selections is a additional table- along with other data of course.

It might help if you could post an example file with some data in it so that I know what you are going for.

My example is only meant to show how to limite the available options in comboboxes based on options in a "higher" combobox. You can just bind those comboboxes to the fields in the table that you are using the comboboxes to populate data.

In any event, here's updated sample file for you to play around with. There may be some tweaking needed, particularly with the AfterUpdate events for the comboboxes and the Current event for the form but I think it's a good start. If I ever get around to tweaking it, I'll update the file on my website as well. Let me know if you find any problems with it!

 

[gagetpro]

Thanks Cosmos75-

This is EXACTLY what I was trying to do. I will try tio figure out how you did it and get back to you if any questikons.

Thanks for your time

 

[gagetpro]

This is my first post. Your sample did the trick. Thanks again.

I would suggest making tblStore lng1, lng2, lng3 number data type for consistency. Also perhaps a descriptive label on the forms- especially frmCreateData. eg " this form populates all 3 level tables with data. Otherwise, must enter data directly into tables."

What is the site protocal on using your code in my application? After your great help, don't want to slight anyone.

thanks again

 

[Cosmos75]

Your sample did the trick. Thanks again.

Glad to be of help.

I would suggest making tblStore lng1, lng2, lng3 number data type for consistency. Also perhaps a descriptive label on the forms- especially frmCreateData. eg " this form populates all 3 level tables with data. Otherwise, must enter data directly into tables."

I will try to incorporate your suggestions.

What is the site protocal on using your code in my application? After your great help, don't want to slight anyone.

I appreciate your thoughtfulness! There really isn't anything in the code that is unique. It's more of an example of
- how I would create cascading comboboxes
- how I would bind them to a field
- and how I use the AfterUpdate and Form_Current events to ensure that any changes in "higher" lever comboboxes affect the "lower" level comboboxes

That being said, if you wanted to include a comment in your code stating that your code is derived from an example from http://AccessDB.info as well as a link to the original article, I would not be opposed to the idea.


But really, that is entirely up to you. If it was a custom function I created that you intended to use, then I would ask that the header comments are left with the function in order for some recognition as well as providing a way for someone to contact me with any problems or suggestions regarding the function.


Of course, there isn't anyway to check that no one is claiming my work as their own or much less to enforce it, especially seeing as my code is available for free on my website. Also, I don't know if it is even possible to copyright code. If it is, it would probably be hard to enforce.

me.rant.end = true

 

[gagetpro]

Continuous form

Everything worked out great. Thanks again for your past help.

I have a slight variation on this issue, that perhaps you could help. My form also has a subform that is continuous and has a pair of related combo boxes. When I input a set of values it seems to work just like in a single form, but (always seems to be something) entering the next pair of combo box inputs "erases" the first. They are not visible.

Have tried for a few days to resolve this but can't seem to get it.