Data entry forms and/or subforms

Hi all,

I am attempting to use MS Access forms for a data entry project and am
fairly new to database design and the use of forms. The data to be
entered are survey responses from high school students. Each survey
contains over 300 questions/items. Originally, I had envisioned
entering the data into a single table with a student id serving as the
primary key (there is never a one-to-many relationship in the way the
data appears to be structured). Is this a reasonable approach (or
should the data go to multiple tables or queries)?

Because of the large number of items, I had also designed separate
forms for each page of the survey and inserted them into a main form
as subforms. There is a tab-control and I am able to get the forms to
navigate so that when I hit a button at the bottom of one subform, it
takes me to the top of the next subform. However,what I would like to
be able to do is to hit a button upon completing one data entry record
that takes me to the next data entry record and back to the main form.
I am having no luck at all with this.

I am also having difficulties in keeping the records straight, so that
once I enter a student id, all of the subsequent data on the subforms
get entered into this record. It seems as though some or all of the
subforms records get bounced around or go back to the first record.
Any help provided is certainly appreciated.

Best,

Hugh

Hi Hugh,

I am attempting to use MS Access forms for a data entry project and am
fairly new to database design and the use of forms. The data to be
entered are survey responses from high school students. Each survey
contains over 300 questions/items. Originally, I had envisioned
entering the data into a single table with a student id serving as the
primary key (there is never a one-to-many relationship in the way the
data appears to be structured). Is this a reasonable approach (or
should the data go to multiple tables or queries)?

Most likely no, unfortunately.
And you are now running into problems as a result of this table design setup.

This really isn't my area of expertise, but MVP Duane Hookom has a great sample database you should
download:

http://rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

It is a fully functional survey database.
It should really help show you a proper design setup as well as data entry forms.
Hopefully this should help in your project.
If you have additional questions I'm sure Duane will join in (I hope).

Good luck,

--
Jeff Conrad
Access Junkie
Bend, Oregon

Because of the large number of items, I had also designed separate
forms for each page of the survey and inserted them into a main form
as subforms. There is a tab-control and I am able to get the forms to
navigate so that when I hit a button at the bottom of one subform, it
takes me to the top of the next subform. However,what I would like to
be able to do is to hit a button upon completing one data entry record
that takes me to the next data entry record and back to the main form.
I am having no luck at all with this.

I am also having difficulties in keeping the records straight, so that
once I enter a student id, all of the subsequent data on the subforms
get entered into this record. It seems as though some or all of the
subforms records get bounced around or go back to the first record.
Any help provided is certainly appreciated.

Best,

Hugh

Hi High,

Thanks for the note. I looked at the
sample database and it seems to be very dependent on using the mouse
for data entry (e.g., all drop down boxes). I have set up my forms so
that a data entry person can just tab from text box to text box with
the pages mirroring the written pages.

On the mouse issue there are almost always keyboard shortcuts for doing the same thing. For example,
ALT+DOWN ARROW will drop down a combo box list. The arrow keys can then be used to move up and down
and Enter will select one of the options. Pressing the Space Bar key on a check box control will
toggle checked/unchecked. So if you wanted to steer away from the mouse you could still not be
limited to just text boxes. You could even put some little keyboard instructions on the form for the
user to see as well. For more on keyboard shortcuts see the following link:

http://www.microsoft.com/enable/prod...asp?Product=14

Any ideas as to how to put a button at the end to proceed back to
the main form and proceed to a new record?

Can you explain what you mean is the "end?"
The last control on the form?
The last page on a form with page breaks?
The last tab on a tab control?
The last control on a subform within a main form?

There are countless ways to do this based on your situation and personal tastes. I'll offer just one
suggestion. I'm assuming here that the form is based directly on a table or query (preferably a
query) and you are not using an unbound form. I'm also assuming that you have set the form's Cycle
property to "Current Record." What this means is that if you continue to Tab around the form you
will just stay on the current record. One last assumption is that you have turned off the form's
navigation buttons on the form's property sheet.

If you simply want a command button that will go to a new record you could have something like this:

Private Sub cmdNewRecord_Click()
On Error GoTo ErrorPoint

DoCmd.RunCommand acCmdRecordsGoToNew

ExitPoint:
Exit Sub

ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub

You could also set the focus to the first text box in the above code by having something like:

Me.SomeTextBox.SetFocus

Hope that's what you're looking for.

--
Jeff Conrad
Access Junkie
Bend, Oregon

"Hugh Crean" wrote in message
om...
"Jeff Conrad" wrote in message
...
Hi Hugh,

I am attempting to use MS Access forms for a data entry project and am
fairly new to database design and the use of forms. The data to be
entered are survey responses from high school students. Each survey
contains over 300 questions/items. Originally, I had envisioned
entering the data into a single table with a student id serving as the
primary key (there is never a one-to-many relationship in the way the
data appears to be structured). Is this a reasonable approach (or
should the data go to multiple tables or queries)?

Most likely no, unfortunately.
And you are now running into problems as a result of this table design setup.

This really isn't my area of expertise, but MVP Duane Hookom has a great sample database you
should
download:

http://rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000'

It is a fully functional survey database.
It should really help show you a proper design setup as well as data entry forms.
Hopefully this should help in your project.
If you have additional questions I'm sure Duane will join in (I hope).

Good luck,

--
Jeff Conrad
Access Junkie
Bend, Oregon

Because of the large number of items, I had also designed separate
forms for each page of the survey and inserted them into a main form
as subforms. There is a tab-control and I am able to get the forms to
navigate so that when I hit a button at the bottom of one subform, it
takes me to the top of the next subform. However,what I would like to
be able to do is to hit a button upon completing one data entry record
that takes me to the next data entry record and back to the main form.
I am having no luck at all with this.

I am also having difficulties in keeping the records straight, so that
once I enter a student id, all of the subsequent data on the subforms
get entered into this record. It seems as though some or all of the
subforms records get bounced around or go back to the first record.
Any help provided is certainly appreciated.

Best,

Hugh