Solved What's the Proper Way to Structure Bound Combo Box on Form? (1 Viewer)

JMongi

Active member
Local time
Today, 02:43
Joined
Jan 6, 2021
Messages
802
Basic Question Alert!
I don't know why I can't remember this from all the tutorials I did, but, isolating this with a simple example should help me lock it in for the future.

So, I'm working on a much simpler database at the moment than the one from my other threads. This on is a basic labor hour tracker. I have the following tables:

HourTracker.PNG


My question involves the combo boxes on the main hour tracker form. I want there to be combo boxes for the Employee, Project and TaskType. As you can see, my relationships are based on ID numbers not the actual data I want displayed. These are more appropriately considered list boxes. No data entry, just selection. Just so you understand their intended usage.

Even though all of the fields that I want are in the HourTracker table, am I right to assume I need to base my form on a query that pulls adds the following fields: FirstName, LastName, ProjName and TaskName along with all of the fields in HourTracker so these can be displayed in the combobox?
 

Isaac

Lifelong Learner
Local time
Yesterday, 23:43
Joined
Mar 14, 2017
Messages
8,738
For example, for the Employee combobox. All you need to do is make sure you have a rowsource that includes the empid as one of the columns in order to "do" something to it after its selection. I don't see any reason for the combobox to be bound, nor for you to include anything in the form's recordsource other than the fields you really want.

Have I maybe misunderstood?
 

JMongi

Active member
Local time
Today, 02:43
Joined
Jan 6, 2021
Messages
802
No problem. Sorry if I didn't use accurate terminology to describe it. Maybe I don't understand bound/unbound. Anywho.

This is going to be the main form for our shop foreman to record the hours from the paper sheets out on the floor. He's going to open the form, create a new record and select the project by project name from a dropdown box (but only store ProjID), select the employee from a drop down box by name (but store EmpID), select the tasktype by name from a drop down box (but only store TaskID) and then enter in the hours and date (which will default to today but be editable).
 

JMongi

Active member
Local time
Today, 02:43
Joined
Jan 6, 2021
Messages
802
Sometimes I think I'm getting this, and then other times, like today, I can't extract the steps needed to complete something basic. 😞
 

JMongi

Active member
Local time
Today, 02:43
Joined
Jan 6, 2021
Messages
802
If I understand it, then I need a query with EmpID, FirstName, LastName as my rowsource for my Employee ComboBox.
Now how does the EmpID value get linked to my new record that is connected to HourTracker? Maybe I'm making this more complicated than it is....
 

JMongi

Active member
Local time
Today, 02:43
Joined
Jan 6, 2021
Messages
802
Ok, wow. Apparently I need some more coffee....I was getting myself all twisted up on which thing was bound where. The form itself is bound to my hour tracker table, then I use my employee table/query as the record source for my combo box, then I select the appropriate table field to store my value. Sorry to clutter up with such a basic question. I got msyelf all tied up in knots on this one.

Edit: Rereading your reply, I see now you tried to help me by underlining "Form's" recordsource, but I missed it the first go around. Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:43
Joined
Feb 19, 2002
Messages
42,981
To summarize, combos and listboxes are bound to two things, the ControlSource which is a field in the recordSource that the form is bound to and the RowSource which is the table/query that produces the list of items it shows. Combos/listboxes don't have to have a ControlSource but they always have to have a RowSource. So the combo/listbox can be unbound (in relation to the form on which it sits) if you want to use it for searching for example.
 

JMongi

Active member
Local time
Today, 02:43
Joined
Jan 6, 2021
Messages
802
I'll tack this onto this thread since it's related.

When using the Access ComboBox wizard, I noticed that it writes an SQL statement for a SELECT query based as the rowsource for the ComboBox. Is there any benefit to basing the rowsource off of the same query that I create myself as opposed to a dynamically generated one?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:43
Joined
Feb 19, 2002
Messages
42,981
I always use a saved querydef. There is a bug in Access that can destroy an embedded sql string and using a saved querydef avoids that bug.
 

JMongi

Active member
Local time
Today, 02:43
Joined
Jan 6, 2021
Messages
802
Oooh! Thanks for that tip. In general I like to do things in a way that grants me the most control over the process. This seems to fit that. Thanks!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:43
Joined
Jul 9, 2003
Messages
16,245
I thought you might find my blog on combo boxes of some use:-

 

Users who are viewing this thread

Top Bottom