I'm missing something basic - cascading combo boxes plus option group not updating

[viragored]

Another question that is probably a bit basic for old hands. Sorry... Could you give me a bit more guidance?



This follows from another question (a little guidance...) that I have marked as solved. A bit premature, as it turns out.

I'm trying to build a database to collect and process information about model aircraft contests. I've attached a snip of the relationships, following the advice I got earlier. Also attached is a snip of a sample screen with completed flight details. And a snip of the resulting entry in the table "tblflighttime" (it's the bottom record).

My aim is to build a form to use during the contest, for an operator to select in succession: contestant, model, event (the three combo boxes, choosing data from tables already entered) then click a radio button to identify the specific flight that the data is being recorded for, and enter the flight details (time and landing yes/no). The output is then a record in tblflighttime with fkeventsenteredid coming from the bound field on the bottom combo box, and fkflighttype coming from the option group with the radio buttons.

In the QBE the form has its record source as tblflighttime, with criteria for the two foreign keys set to the relevant combo boxes. Yet another snip attached.

The picture shows text fields to the right of the cascading combo boxes showing the actual bound values from the user's selection: they are all the expected keys to the data that is in the tables. So far so good.

The resulting record however records only the flight details, and not the foreign keys to complete the relationships.

I am sure this must be pretty basic, but I am still struggling with where to look to make those two foreign keys appear in the record.

I've tried setting the control source for the combo box and option group to their relevant table fields, but Access then won't let me make selections on the screen. OK, you knew that would happen.... I've still got a lot to learn

I'll be really pleased to get any pointers to how to link the form properly to the table! And of course, let me know if I need to provide more information.

Thanks in advance

[jzwp11]

I would typically use a subform in the form you show since the flight info is on the many side of the relationship. The form-subform should link via the key fields (and the flight time table would populate), without that link the key fields will not populate as you indicated. Now, if you want to keep the form as is rather than using a subform design, you will most likely have to use an unbound form and thus would need code behind your save button that in essence runs an append query. That query would pull the values from the appropriate form controls and store the info in the flight time table. The drawback of this is that you would not be able to use the form to see previously entered data.

Any chance you can post your actual database? Screenshots only tell part of the story. I would recommend running the compact and repair utility on the database, zip the database and post the zip file. (this will keep the size of the file manageable)

[viragored]

Thanks once more I'll have another go at form-subform over the weekend, and if I'm still stuck I'll post the database.

[viragored]

Any chance you can post your actual database? Screenshots only tell part of the story. I would recommend running the compact and repair utility on the database, zip the database and post the zip file. (this will keep the size of the file manageable)

Well, I have spent several hours trying all the tips and hints I thought I had learned and am still stuck. Here's the compacted, zipped database, and I'll be most grateful for any pointers.

The main forms (available via the switchboard) I have designed are for registration ("frmregister") which seems to be working properly (just a couple of tweaks left to do), and "frmscore" for recording the results - and that's where I'm having trouble.

First I must apologise for having fiddled with so many tables, form/subform link settings and other things in succession that I probably ought to go back and start afresh - maybe when I've had a good sleep ;-)

What I'm trying to achieve is a screen with cascading combo boxes to identify entrant, model, and event. Then either another combo box or (preferably) radio buttons to select one of the six available flight types, and finally the two fields that record the flight time (in seconds for now) and yes/no whether the spot landing was achieved for this flight.

It all sounds so simple, but I don't seem able to get the right things in place to populate the tables correctly for flight types and scores. So if someone can point me to what I need to fix, I'll get fixing!

Many thanks in advance.

[jzwp11]

With respect to your frmScore, since the flight time info is held in the lowest table of the structure you would typically use forms and subforms that cascade through all of the relationships not combo boxes. However,, you can use the cascading combo box design you show on frmScore, but the form itself cannot be bound to a table or query AND you will have to use code to append the record after all selections in the combo boxes have been made.

But before we get to that I noticed some table structure issues. First, you should set referential integrity for your joins between tables. I also noticed that you were making a join between two text fields (event name). Even though this can be done, Access and other relational databases are more efficient when joins are made with numeric fields. Further, I saw in one case you used a combo box (i.e. a lookup) at the table level. Even though Access has this capability, it is generally not recommended because of the problems it can cause. This site has further details on those problems. Also, you used a field called time. The word time is a reserved word in Access so it should not be used as a table or field name. This site has a list of reserved words.

I also noticed that you had not separated the model detail items into their own table as I had suggested earlier. The way you had it is OK if you never ever plan to add any other details about a model. Using your design, if you ever added another model detail that you wanted to capture, you would have to add a new field to the table and then update every related form, query, report throughout the database. With the design I suggested, you would only need to add a new record---no redesign needed.

I have attached the revised database. Of course, not that I have changed the table design some of your forms will need to be reworked.

Once you have had a chance to see what I have done and reworked the forms, I can help you with the code necessary to get the frmScore working the way you want.



In the attached database, I have c

[viragored]

Thanks so much for what you have done, I really appreciate that. I'm going to have to put this on the back burner now till next weekend, I've just got the computer up and running again after installing Windows 7 - I'm hoping the pain will be worth the gain

My only excuse for re-using a reserved word after thinking I'd got rid of them all is...the last edit was late at night and I should have gone to bed before I couldn't think straight any more.

And I've got so much to learn.....

Thanks again.