Help Building Complex Data Entry Forms – PART 1

[Walt]

Hi everyone, I want to start by saying I’m a novice user with Access 2003,
novice programming skills and I’m baffled at the best way to do this.
Therefore, I appreciate all help.

Okay, here’s what I’m trying to do:

I’m trying to build data entry forms with auto fills in drop down selection
menus that will input information into different tables.

1 – Form 1: At the top of the form is a tab controlled area with two tabs:
Accomplishment (ACC) and Point of Contact (POC). The ACC tab will have a
memo box. The POC tab will have text boxes for personal information: name,
title, phone, email, etc. ACC information will go into an ACC table and POC
information will go in a POC table. I also need the form to populate ID
fields in each table. The ID in the ACC table is in this format: A001,
A002, etc. I was just going to use numbers for the IDs in the POC table:
001, 002, etc.

2 – Next on the form are three memo boxes: Impact, Obstacle, Next Steps.
These are columns in my ACC table. I’m not sure if I need to include these
on the tabbed control form to make it easier to set-up or if I can just have
it in the middle of the form outside of the tabbed control?

3 – At the bottom of the form, I want to have 3 drop down boxes. Daily
Operations (DO), Goals (GP), Mission (MP). Each has its own table with an ID
and Description field. I want the users to have to select an item from the
DO drop-down first. The first item in list will be N/A. If they select N/A
then the GP box becomes active and they have to select a GP. The first item
in GP will also be N/A. If they select that then they have to select a MP in
order for the information on the form to submit. Now, I need to have the
boxes set-up in a way so that when the users selects a certain item in DO
drop-down other than N/A only those items that correspondent to it in GP and
MP will become available. For example, let’s say they select DO 1, and then
in GP they will only be able to select 1 or 3. Based on which GP they select
they will be limited to which MPs that are available. I want the drop downs
to display the description information (not the IDs) but on input I want the
form to input the IDs that relate to that description. I have a table that
cross links the IDs to know what correspondends but I don't know how to use
it.

4 – At the end of the from is a question. Is this ACC linked to a
Recommendation? With a YES / NO selection (not sure what to use here
drop-down, check box, etc). If the user selects NO. Then the information
they filled out is submitted. The ACC, Impact, Obstacle, and Next Steps is
input in the Accomplishments table and given the appropriate ID. The POC
information is put in the POC table and given the appropriate ID. Then those
2 IDs, along with the IDs from the drop down selections all get updated in a
Master table that only contains IDs so I have a cross link to based queries’
on.

Now if they select, Yes. Then the information they submitted is held
(somehow) and they are taken to another form. That will be PART 2 of my
question.

 

[Dorian]

Your subject is a giveaway that your approach may be wrong ("Complex data
entry"). Why aim to make it complex?
Normally a form is bound to data in a single table, that should give a clue
as to how to set up your design.
Also, its best to ask one question per post as we can't design your
application for you - only give help with specific issues.
Is your table structure already built or are you free to design your own
tables? It makes a big difference.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Walt]

Hi Dorian and thank you for your comment. I thought trying to spell-out what
I am trying to do would make it easier to get help versus piece-milling it
into multiple questions but I understand how that might make this process
easier.

As for making this complex, I've been pretty much given direction on what's
wanted and am trying to fill the order. If there is a better way to do this
then I'm willing to try anything to get the information as needed.

Yes my table structure is built already but I still have some room to make
changes if needed. I appreciate any help. Thank you

 

[Dorian]

It sounds like a terrible design.
Your form cannot be bound to any table because you may not update the table.
So all controls will need to be unbound and you will have to do all form to
table data transfers and table writing manually in VBA.
Your 3 inter-dependent drop downs will need to be controlled by VBA coding.
It's difficult to offer any suggestions without knowledge of what this
databases purpose is and knowing all the table relationshiops.
Your Link Y/N at the end should be 2 radio buttons inside an option group.
Then you will need a command button that says something like 'Continue'.
You could make the caption change based on which radio button is checked.
If they have selected Yes, you will need to keep this form open when opening
the next form so you can still refer to the data on the form. You could set
visible = false to make it hidden. You will have to close this form after you
finish with the next form and no longer need the data.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".