A little guidance would help, please - subforms and cascading combo boxes

[viragored]

Hello: I'm new to Access, have ploughed my way through books and web help, and now ask for some guidance on best practice and best place to look for the tuition I need to take the next step.

The database is probably fairly simple, at least to experienced people. It's to first run the registrations and later the scoring for a model aircraft contest. Each entrant can bring multiple models (theoretically unlimited number); each model can enter multiple events (max. of 6); each event has multiple rounds in which scores can be achieved. So that's a set of three one-to-many relationships.

I have four tables that implement the relationships - I've uploaded a snip of the relationships view that shows the fields as well as the prime and foreign keys. (Ignore the two tables at the bottom of the view, they're not involved in my questions).



Question 1: I want to capture all the registration data from entrants using just one screen per person, if I can. I've got a form that captures the data for the entrant and his/her models, and that's working properly (see the other snip). What is the best way to extend that form so that it also captures the details of which events each model is entering - list box, subform, or some other technique? And is there a recommended place to learn how to integrate it?

Question 2: When the events are running and the scores are coming in, I need a form (also just one screen per entrant) that will contain cascading combo boxes so that the person doing the recording can click the entrant, click the entrant's model, click the event that the model has just taken part in - and then have a form to record the scores. I've got the first two combo boxes working properly (thanks mainly to the DataPig videos) - and that's where tutorials seem to stop. I am now struggling to get the third one working to use the results of the second as its input . Where is a good place to find a tutorial or instructions on how to get to the third level of cascade?

Thanks in advance for any help that may be coming my way

[jzwp11]

I have four tables that implement the relationships - I've uploaded a snip of the relationships view that shows the fields as well as the prime and foreign keys. (Ignore the two tables at the bottom of the view, they're not involved in my questions).

You have some structural issues with your tables that need to be corrected before even thinking about your forms. Your tables are not normalized.

But first, some general recommendations:
1. It is best not to use spaces or special characters in your table or field names
2. The names of your primary key fields should be something meaningful not just "ID" and it is more efficient for the database if the key fields were numeric rather than text.
3. Do not use reserved words as table or field names (reserved words)


Now back to your structure. During an event, if a model can have many flights and/or fly offs, that describes a one-to-many relationship. Each flight would be a record in a table not a field in a table as you show. Since you have 2 types: flight and fly-offs, you can put these two type descriptors in a table (as records) and relate them back to the flight time

tblEventTypes (2 records: flight, fly-off)
-pkEventTypeID primary key, autonumber
-txtEventType

Also, I would not have separate fields to break out the time, that will make it very difficult later on to do any kind of summing, averaging etc. I would recommend just recording the time in 1 unit of measure such as seconds, but I'll leave that choice to you. You could always display the time at some point as mm:ss using some expressions. With all that, this is what the flight time table would look like

tblFlightTime
-pkFlightTimeID primary key, autonumber
-fkEventsEnteredID foreign key to tblEventsEntered
-fkEventTypeID foreign key to tblEventTypes
-spTime (single precision number field to capture the total time in some unit of measure)

As to the events name table, the primary key should be numeric

tblEventNames
-pkEventNameID primary key, autonumber
-txtEventName

Of course, the above change impacts your Events Entered table, which will now look like this

tblEventsEntered
-pkEventsEnteredID primary key, autonumber
-fkEventNameID foreign key to tblEventNames
-fkModelID foreign key to tblModels

Now as to the electric info table, the fields wing area, capacity etc look to be attributes/characteristics of the models. If so, I would guess that you might want to capture other info as well. It would be better to list these attributes as records in a table and then link them back to the models along with the specific value

tblCharacteristicList
-pkCharListID primary key, autonumber
-txtCharListName

you would have records in the list table as follow:
wing area
capacity
discharge

Now you can relate the characteristics to the model (replaces your electric info table)



tblModelDetails
-pkModelDetailID primary key, autonumber
-fkModelID foreign key to tblModels
-fkCharListID foreign key to tblCharacteristicsList
-a field to hold the specific info for the characteristic, you will have to determine whether this field should be text or numeric based on the value of the characterist (i.e the wing area would probably be a number)

With respect to the entrant table, I'm not sure what the frequency and 2-4gHz fields represent but they do not sound like info pertinent to the entrant but more related to the model. You'll have to explain that in more detail.

[June7]

Could be form/subform/subsubform/subsubsubform arrangement.
Entrant info/models info/events info/flighttime info.
One composite form serves for registration and results entry.
Then have an unbound multi-column combobox of entrant IDs/Names to serve as search criteria to filter form for desired entrant. DataPig has tutorials for both multi-column combobox and form filter.

Not sure ElectricInfo needs to be a separate table. This is more info about the model and every model will have this. Looks like a 1:1 relationship. Actually, are these standard commercially available models? And more than one entrant can have the same model? If so need a table of models info then a junction table to relate entrants and models. This junction table would be related to events.

tblModel
ModelID
ModelName
ModelYear
electric info

tblEntrantModels
ModelID
EntrantID
AgeBonus (different for each model, if not just put in EntrantInfo)

[viragored]

Thanks very much for these two inputs - I'll try to absorb all the points, do a lot more learning and come back in a couple of days.

(Question about models - no, not commercially available, all scratch-built from plans dating from 1930, most are IC powered but some are electric powered, and we're just capturing information about electric-powered models for reference and not calculation. So entrants can have variants of models, hence each model is in effect unique.)

[viragored]

Many thanks to jzwp11 for all the crystal-clear input that went into your post. I think I have worked my way through till I understand properly what you were saying, and why. My tables now follow the conventions you suggested, and I think I have avoided reserved words and spaces.

So I now have a completed form fully working for the registration process - built by the wizard as a form + sub + sub (and no need for a sub-sub form). I still haven't worked out how to create a sub-sub form but have side-stepped the issue for now.

I think I have worked out how to set up the third level (and lower) of cascading combo boxes. If I've got it right, the combo box will need at least these three fields:
* the foreign key of the preceding combo box's prime key (which was bound in the higher level)
* the data that the user will see and select
* the prime key for the selected data (which will be bound in this combo box)
All three fields will be 'visible' in the QBE, but columns 1 and 3 will have zero width so the user won't see them.

And I had all that working when the darndest thing happened. I saved and closed the database, came back to it on Monday and - now Access crashes every time I try to get at the Visual Basic, delete something, or even open one of my forms. "Compact and repair database" didn't fix anything, The same database file runs properly on another computer with Access 2010.... So now I'm starting over from the beginning. I sure am glad I don't have to use this thing to earn my crust!

So with renewed thanks for the excellent input, I'll mark this thread "Solved" - and get back to trying to finish the database.

[jzwp11]

Instead of starting from scratch you could decompile and then recompile to database to try to identify the error causing it to crash. Here is a procedure that I found somewhere out on the internet:

To decompile an ACCESS Database (assuming Access 2010):

Delete the laccdb file if present, make a backup copy, then from the RUN window (STARTUP MENU) put in this text (modified for the database file location and the location of the access executable file):

"C:\Program Files\Microsoft Office\Office14\MSACCESS.exe" /decompile "C:\Documents and Settings\....\DBfileName.accdb"


Then click OK



After decompiling, open up the VBA window of the database and then go to Debug and compile the database. Any errors that would prevent a successful compile should be identified.

[viragored]

Thanks for even more great input

Because I've done some learning, starting again took me only a fraction of the time it took the first time. Just a couple of hours to get back to where I was after a whole week's investment of spare time. I could still copy the tables out of the crashing database so I had only the forms to re-do.

But I tried the decompile and recompile of the crashing database, another venture into territory I've never been before. There were no error messages and now it all appears to be working just as I left it before the weekend. How bizarre is that?

[jzwp11]

That does seem strange, but I'm glad it is working again.

[viragored]

That does seem strange, but I'm glad it is working again. By chance are you using XP? I have noticed some strange behavior with my Access 2010 databases running on XP at work. I have not had any issues with any databases running on Windows 7 at home.

No. this PC is running Vista Home Premium (but will soon be upgraded to 7).

The other PC on which I tried the same database file (ie the file that was causing crashes, but which ran successfully away from home) is running Win 7 - I wonder if it's a Vista issue? Anyway, just at the moment everything is working as it should.

[jzwp11]

I hope it continues to run OK for you. Best of luck on your project as you move forward.

[viragored]

Thanks - I'm nearly there, at least as far as getting the data in and seeing it in the tables. Next comes the tricky bit, well tricky for me - applying the scoring rules to each type of event to produce the scores and results...in real time.

And I really appreciate your help at the beginning, you gave me a great pointer in the right direction.

[jzwp11]

Going back to your original post and using a table structure similar to what I suggested, it would just be a matter of creating the forms. The main form would be based on tblEntrants with a subform based on tblModels. Within the models form would be a subform for the model details (tblModelDetails). You will also have another subform in the models form that will hold the event entered info (tblEventsEntered) and a subform within that subform to record the flight times (tblFlightTime).

With so many forms/subforms/subsubforms, your users may have trouble navigating through everything in just one form, so a lot of developers will create more simple forms and navigate the users from one form to the next. That design item is really up to you and your users.

[viragored]

Here are my two forms, as far as I've got them. This evening's entertainment will be to convert the "Flight time in seconds" to two fields, minutes and seconds, which will convert to one seconds field on the record but importantly match the way in which the timekeepers work.

The forms are working and I can populate my tables with test data, but there are some usability issues to tidy up. Mostly clearing the forms of data from the previous record - just some events to sort out, I think.

Registration happens first thing in the day, with a gaggle of people filling in forms before they are keyed in. Flying and timing happens throughout the rest of the day(s) so it makes sense to have two different forms. And KISS

Then my next challenge is to work out how to display at the bottom of the "time" form each of the six possible flights for that entrant, model and event, applying the rules and showing the total score achieved plus the current ranking amongst the competitors.

In case you're interested, the most complex rule set is for "Precision". Here's a summary: All entrants make three flights; Points are awarded at 1 per second flown up to 180; every second over 180 is a point deducted; the model's age bonus is added; bonus is added for a spot landing; maximum points per flight capped at 200. If there's no single winner, those tied for first place fly-off again one sudden-death round at a time, same scoring as first time. Usually that sorts out a winner after the first fly-off, but in theory there could be a tie.

I'm enjoying this! But I'm still glad it's a leisure activity and I don't have to earn my wages with Microsoft and crashing Access.

Cheers, Dave

[viragored]

It turns out I was a bit premature thinking I'd got the data entry sorted out. I've started another thread with the specific question......