Please help me set up some Access forms.

August 5, 2009 1:41 PM

I have two questions about setting up MS Access forms: 1) Currently, I have a form to enter the volunteer id, shift start time and shift stop time, but the client doesn't want to have to look up the id in a report. Is there a way to create a selection list from the volunteer's names? What other options are there? 2) On the "add/edit a volunteer" form, I'd like to be able to choose tasks from a selection list. The tasks table has id and name. The "tasks of volunteers" join table has volunteer id and task id. How do I set this up? I can't seem to get the right combination of configurations.

Additional information:
1) The shifts table only has those three fields, and I don't want to make the volunteer's name a primary key.
2) I don't want to resort to adding the booleans TaskSort, TaskPrep, ..., to the volunteers table, since that's not how databases are supposed to be set up. When I try to use a select query, I get "SELECT ..." as one of my options.

* It's Access version 2003.
* I'm just using standard forms (ie. not "Pages").
* I'm pretty sure I've created the tables in a "best practices" way, but since I'm creating this database from scratch, I can create anything however I want.
* I think I know about database theory pretty well, and that's not what this question is about.
* But, I'm new to Access, so it's likely I'm missing something easy.

Thanks.

posted by philomathoholic to computers & internet (7 comments total)

When I use IDs as primary keys, I like to have a query relating the description or plaintext name to the ID. In your case, I would have this simple query saved as UserIDs (so that you can use it everywhere and modify it from one place):

SELECT Users.Name, Users.ID FROM Users;

Then use a combobox on your form with the rowsource to UserIDs (or whatever you named it). Set Column Count to 1 and Bound Column to 2 (Users.ID). Make sure you limit the selection of that combobox to the query to ensure that the user doesn't enter anything that isn't there. If they do try to add a user that doesn't exist, you can use the "Not In List" event to handle it (if you need help with that or some simple VBA, just ask). On a side note, you should definitely enforce referential equality (via the relationships view) between the User ID field in your tables. This will ensure that you never have a User ID that isn't in a User table.

I'm having a little trouble understanding your second question though, could you clarify what relevent what tables and forms you have? Are you trying to set up the selection box?
posted by vmrob at 2:05 PM on August 5

To create dropdown lists of specific options for a form, you make a new table with those entries and then in your master form, you create a "combo box". The combo box wizard will, most likely, walk you through the process. If they are names in an existing table, you can just press the combo box button on the form creator and "create a dropdown list from an existing table" or something like that should be an option.

I'd be happy to help you with creating what you're trying to do---just memail me and I'll give you my email so you can send me the db.

I almost always use an autonumber for primary key and then never refer to it again, even if I'm linking other ID's among tables. Don't get hung up on that, it's specifically to isolate a given value in a given table, which isn't even especially important until you start rocking subforms and relationships.
posted by TomMelee at 2:14 PM on August 5

Oops. For example, on my form, users can only type in certain counties as options. Same with specific contractors and contractor types.

Therefore, there are three tables in my db to create these lists. One "county", one "contractors" and one "contractor type".

Each table has a primary autonumber ID (that I don't mess with) and one column header (county, contractor, contractor type) specifically.

I then go into those tables in table view and enter in all the values I want, then create a combo box on the master form that creates a list from those values and stores it in a field on the master form. For switching between members, I have a combo box that looks at the master table last names, arranges them alphabetically, and puts them in the dropdown, selecting one takes you to that record.
posted by TomMelee at 2:17 PM on August 5

Cool, I got the volunteer name drop down working for the shifts. (question 1)

more explanation for the second question: Volunteers list tasks which they are capable of doing, and I track that in the "tasks of volunteers" join table which is just volunteer id and task id. The task id comes from the tasks table which just has id and name. I want to be able to track which volunteer is capable of doing which task, and I want to be able to select tasks on my "add/edit volunteers form".

Thanks to your guys' help I've made progress, but I still have one problem. I've managed to get the volunteer tasks to show up and work on the volunteer add form, but when I change the control to allow multiple selections (which I need) it says "You tried to assign the Null value to a variable that is not a Variant data type.". I think this might have something to do with the relationships? Is there something else I should change to make it work on multiple selections? It works great when it's set to only allow one task per volunteer.
posted by philomathoholic at 3:56 PM on August 5

TomMelee: I accept your offer, and I've sent you mail.
posted by philomathoholic at 7:21 PM on August 5

I'll be glad to help anytime as well.
posted by vmrob at 12:29 PM on August 6

Epic fail on my behalf here, someone else is welcome to step in and offer assistance. I can do what he wants in access 07, not in 03.
posted by TomMelee at 7:32 PM on August 6