Should I use Combo Box w/ fixed choices to update a field on my table?

[DumplingsForLife]

Hi! I am creating a database to store research participant's session information. The basic design I came up with so far is:
Table1ppt info: participant (ppt) unique ID (not autonumber), ppt family doctor
Table2session info: ppt unique ID, session date, session staff, session number, scan quality comment
Relationship: Table1 ppt unique ID-1toMany-Table2 ppt unique ID, as ppt might have multiple sessions

I want to create a form for staff to update session information after each session. I want to have a drop-down list all fields to be updated to Table2 (except date), as I don't want staff to input something random. I have read that lookup wizard is generally bad, but I got lost when people start saying they are okay for forms. I don't think I have grasped all major rules of Access nor do I understand each level...I've been playing with it and following tutorials.

Right now, I have a form+subform with: 1. ppt unique ID, 2. subform table displaying all sessions belonging to that ppt, 3. three combo boxes with fixed list of choices +set to update corresponding fields in Table2session info: session staff, session number, scan quality comment. This seems weird but I don't know what would be the right way to do this.

Any help is appreciated! I did take down some recommended tutorial pages. I only want to learn Access enough to create this one database (as this is not my main job and I don't have time to do it in-depth). So, sorry if I am way off...

 

[theDBguy]

Hi. Welcome to AWF!

What would be the choices in your combobox? Sounds like you may need a table for them too.

 

[The_Doc_Man]

The answer to your question depends on uniformity and permanence of the items to be filled in. Generally, having a table of choices driving each individual combo box is best since if you suddenly realize a new choice has become available, all you must do is add one row to the underlying table that drives the combo and you are done.

What may be confusing you is that Access has something called a LOOKUP FIELD (a.k.a. MULTI-VALUED FIELD or MVF) which is all snazzy and cute - and a total b**ch to use in queries and programs. What a form's combo-based lookup uses, you can see in its entirety. An MVF on the other hand HIDES a lot of what is going on and isn't quite so easy to change. "But," you say, "I don't want to see all of that." To which the reply is "Wait until you have to change something OR use the result in a query." The COMBO BOX lookup wizard isn't bad. It is the FIELD lookup wizard that leads you down a garden path.

MS decided to add the MVF as an "ease of operation" thing but unfortunately screwed the pooch on how well its infrastructure played with other parts of the form and with SQL queries looking at the form.

 

[Pat Hartman]

I only want to learn Access enough to create this one database (as this is not my main job and I don't have time to do it in-depth). So, sorry if I am way off...

Maybe you should pay someone to build the app for you.

 

[GPGeorge]

Hi! I am creating a database to store research participant's session information.

Any help is appreciated! I did take down some recommended tutorial pages. I only want to learn Access enough to create this one database (as this is not my main job and I don't have time to do it in-depth). So, sorry if I am way off...

I hope this doesn't sound too critical, but if you need legal advice, do you only want to learn enough about the law to write a single contract to lease a building. Or that if you have a stomach ache, you only want to learn enough about medicine to treat what you think is probably the cause. You'd probably not consider that a very sound approach to either, I hope.

You get the point, right?

If this database is as crucial to you as it seems from the context described, then it's crucial enough to either learn enough about it to do it right, or hire a professional, just as you would for other aspects of your situation. You're storing information about research involving participants, and the reference to family doctors means it is medical information. That's not an area to be handled with something less than professionalism at every level.

Hire a competent professional.

 

[DumplingsForLife]

Hi. Welcome to AWF!

What would be the choices in your combobox? Sounds like you may need a table for them too.

they would be short texts. for example: session staff would have different staff' names, scan quality comment would have short, standard phrases like "good-no movement, bad-movement, etc".

Although they wouldn't change much on a monthly basis, they might change on a yearly basis. Would a table's advantage be that so you could grow/change your list with ease?

Thank you for your help!

 

[DumplingsForLife]

The answer to your question depends on uniformity and permanence of the items to be filled in. Generally, having a table of choices driving each individual combo box is best since if you suddenly realize a new choice has become available, all you must do is add one row to the underlying table that drives the combo and you are done.

What may be confusing you is that Access has something called a LOOKUP FIELD (a.k.a. MULTI-VALUED FIELD or MVF) which is all snazzy and cute - and a total b**ch to use in queries and programs. What a form's combo-based lookup uses, you can see in its entirety. An MVF on the other hand HIDES a lot of what is going on and isn't quite so easy to change. "But," you say, "I don't want to see all of that." To which the reply is "Wait until you have to change something OR use the result in a query." The COMBO BOX lookup wizard isn't bad. It is the FIELD lookup wizard that leads you down a garden path.

MS decided to add the MVF as an "ease of operation" thing but unfortunately screwed the pooch on how well its infrastructure played with other parts of the form and with SQL queries looking at the form.

Ohhh I see. I think I thought a lookup wizard automatically equates to a lookup field operation. Thank you for your detailed explanation! Cheers

 

[Pat Hartman]

Most combos are linked to simple lookup tables with only a couple of fields. Rather than create separate tables (an application could easily have dozens of tiny tables) and separate forms to maintain them, I designed an application over 40 years ago to manage these tables. That was well before Access and the first incarnation of the app was built with COBOL and IMS(IBM's hierarchical database)

I add this mini-app to all applications so this is something I never have to ever think about again. If the lookup is simple and fits the standard pattern, it goes into the mini-app.

I've attached a link.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[DumplingsForLife]

Most combos are linked to simple lookup tables with only a couple of fields. Rather than create separate tables (an application could easily have dozens of tiny tables) and separate forms to maintain them, I designed an application over 40 years ago to manage these tables. That was well before Access and the first incarnation of the app was built with COBOL and IMS(IBM's hierarchical database)

I add this mini-app to all applications so this is something I never have to ever think about again. If the lookup is simple and fits the standard pattern, it goes into the mini-app.

I've attached a link.

Omg thats amazing! I will check it out. Thank you for your help!