Rebuilding. Am I on the right path?

[JediYodaNT]

Hey there folks.
I could use a little help. I have a database that our department has been using for a few years. The associate that created it is no longer with us. The beginning of this month, the VBA module became corrupt and the back up I'd used before had been deleted by a clueless associate in order to "free up more hard drive space". (overlook the mound of dirt in my back yard.)

So, here is what we have. The main function of this database is to maintain productivity totals. For each work item an assoicate does, the enter a transaction into the database to log their work. Using this, management can do a search for totals volumes by associate on a day by day range, process by process total, or simply over volumes by process itself.

We used five tables (Roster, Function, DocType, States, and Entries). We had a switchboard that would take you to the Input form (used to input each work item) or the search form (used to track the totals).

Attached are samples of the Entries table, Process table and Roster table.

I think my question is related to setting up the Entries table and building the associated form. If I can figure these out, that will help get us back on the right path. If someone could, please take a look at the descriptions in the Entries table and let me know if what I'm attempting is feasable, or if I could be approaching this from a more efficient approach. The end goal is to have one entry form for the associates, and one search form of management to pull various metrics.

 

[HiTechCoach]

Have you checked the regular (hopefully nightly) backups to see if you can find a good copy?

 

[JediYodaNT]

There ensues the issue....there were no backups. The maunal back up that I had created, two weeks prior, had been deleted by someone in another department. Thus the body buried in my back yard...*doh! I didn't say that* LOL.

 

[JediYodaNT]

Ok, I think I know how I can ask my first question. In my Input table, I'm trying to create two lookup fields. Both fields will refer to the Process table. There are about 20 items processes on this table which belong to one of five functions. As it stands, when I click on add the Function lookup field, the pull down menu lists each function multiple times instead of just once per Function. Then under the Process field, the list shows all 20 processes. How would I go about getting the Function field to only list the five Function, and then have the Process filed only show the Processes that are found under the chosen Function?
Any help would be greatly appreciated.

 

[HiTechCoach]

Sounds like you need to use cascading combo box on your form.

See:
How to Synchronize Two Combo Boxes on a Form

 

[JediYodaNT]

Thanks for the link. I'll see how I can try to apply it.

 

[dfenton]

In my Input table, I'm trying to create two lookup fields.

If you mean you're going to define the lookup dropdowns in the table design, then DON'T DO IT.

It's bad practice and leads to all sorts of problems.

The lookups should be combo boxes in queries or on forms, never in the table itself.

 

[JediYodaNT]

That makes sense. Thanks for the warning. So, when creating my primary table, I'm just going to create two placeholder fields on the table, then when creating the form, I'll create two combo boxes and have the values from the combo boxes saved to the two place holder fields. If I'm taking this route, is there going to be a different approach to having the "Process" combobox populate with options dependant on the users choice in the "Function" combobox?

 

[HiTechCoach]

Here is another example that may help:

Cascading Combo Boxes Using 2 Tables

Demonstrates how to make combo boxes whose values are filtered by the value of other combo boxes (whose record source are two tables) in a cascade.