Using cascading comboboxes to navigate main form records

[Carouser]

I am managing to put together a db with my limited experience and all is going well so far.

I have a form with a subform. In the main form I have days field and Sessions fields and the various combinations have an ID field which is then automatically added to the subform ID field. It is all working, but I have to use the record selector on the bottom of the form to navigate the various combinations till I find the one I want. I have been playing around with cascading combo boxes and in their unbound form I can get them to work so that I can select the day, and it will filter Session records for that day and also give me the ID for that combination. The problem is that I can't find a way to use it to navigate the form or get the ID into the subform when the comboboxes do give what I want.

So, my questions are:

1)Can cascading comboboxes be set to navigate a form?



2)How would I get an unbounded field on the main form into the subform without breaking the relationship between tables?

Apologies if this is basic access 101

[jzwp11]

Typically the main form is used for data entry into a table that forms the one side of a one-to-many relationship. The subform typically displays the related records from the table that forms the many side of the one-to-many relationship.

So could you provide the field names of the table to which the main form is bound as well as the table & field names to which the subform is bound along with the relationship between the two tables?

[Carouser]

The tables below

This is only part of design so far. The tbl_Days_Session (one) is on the main form and I want the ID to be inserted in the ID_sessions field of the (many)tbl_Combo_Main. This is where I will add rooms data with the relevant day and sessions ID.

I was snooping around and found something that looks promising.

http://support.microsoft.com/kb/287658

I was looking at method 3 (because it looked easiest with no coding) , but can't get it to work.

[jzwp11]

If you have your main form and subform linked via the ID--ID_DaySessions, the ID_DaySessions should show automatically in the subform (it will not be committed until after you add/select the room). So that leaves the cascading combo boxes. I would typically do that in the header of the main form with two unbound combo boxes. My approach does require some coding. First, in the after update event of the first (day) combo box, I create and set the row source for the second (session) combo box. The code to move to the correct record is in the after update event of the second combo box.

I've attached a sample database. The form frmDaysSessions has the cascading combo boxes.

[Carouser]

That is brilliant, exactly what I was trying to do. It was 3am here when I posted after spending so much time researching and trying to solve my issue. It was great to wake up this morning with your comprehensive sample. Thank you. I didn't expect you to do it for me, and after looking at the combo boxes event code, I'm glad you did. The simple method for cascading combos I got from a video tutorial recommended by someone on this site from another post.

http://www.datapigtechnologies.com/f...combobox2.html

Will I still be able to achieve what i need to do with the above method? I ask this as your VB code is intimidating until I dissect everything you have written and how/why it does what it does.

Thanks again JZWP11.

[jzwp11]

Yes, there are a couple of different ways to handle cascading combo boxes that will work & I believe the reference you cited should be fine. However, the code to move the form to the particular record after selecting an item in the second combo box will still be needed.

[Carouser]

I reopened the db to dissect and understand the code. When testing the combo boxes, an error popped up.



I thought I might had messed something up when snooping the first time. I reloaded from the original download and all is working again. Close the db (without saving changes), reopen and I get the error code again.

btw, I think I do understand the code, I was just thrown a bit when I saw mySQL references.

[jzwp11]

I was able to duplicate the error. It looks like the code in the after update event of the second combo box does not like that I specified the table name. I've remove the table name reference in the attached. It appears to have taken care of the problem.

[Carouser]

Sorry for the late reply. Had a weekend away.

I have completed the Day, Sessions and Room structures, created data entry forms and is working like a charm. Thanks for all your help on this. All day I was working on the next phase which is the main presentation table, Speaker tables and forms. In my previous db, I had combo boxes for the Days, Sessions, Rooms referencing lookup tables. Now I am stuck as I've been reading that cascading comboboxes on a continuous subform is not workable and not sure how to proceed after all the previous work done so far.

Any thoughts would be appreciated

[jzwp11]

Roger Carlson has some work arounds on his site. I'm not sure if it will help in your case, but it is worth a try. The other thing is that you have to compromise on normalization. Basically each combo box needs to be bound to an underlying field in the table. This generally duplicates information that is already in other tables.