X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> "joining" Data Entry Forms, Access 2013    
post May 19 2017, 03:30 AM

Posts: 1
Joined: 17-April 17

I have built a navigation form that holds 5 entry forms bound to their 5 tables.
The data source is hand written survey forms with 5 parts. And the data entry people will be typing the forms as new records into Access.
After the first form is entered, where the recordID is created, they click on the tab for the next form from the navigation pane.

I would prefer that the (new) recordID be automagically populated so the next form adds data to the same record as was begun in the main(first) form and so on thru the remaining forms.
Currently these froms are for new records only.

How is this commonly done? ie best practices?

Last time I worked in Access was 2003 and I have forgotten more than I know.

Go to the top of the page
post May 19 2017, 06:34 AM

Posts: 86
Joined: 5-August 05
From: Eastbourne, UK

Duplication is against normalisation rules.

Looks like you need to have a 'parent' table, maybe for the person completing the survey, with a form for that and the 5 survey forms being subforms.
Go to the top of the page
post May 19 2017, 06:50 AM

UA Admin
Posts: 32,818
Joined: 20-June 02
From: Newcastle, WA

Welcome to UtterAccess.

I agree, table design -- not necessarily form design -- may be the key to sorting this out.

What you describe sounds like it should be designed with a parent table for information that identifies the survey being performed, i.e. date, and time, location, person taking the survey, and similar details as appropriate. It's not at all clear, though, whether you actually need five other tables, or if one other table would be adequate. It depends a great deal on what is in those tables.

There is a pretty well established design pattern for questionnaire type applications. You might benefit from studying this example before you spend any more time on a potential form design that supports an inappropriate table design. I'm not sure that your design wouldn't work, but on the surface, we do see a potential red flag. If you want, you could also upload a copy of the accdb for review here. If you want to do that, compact and repair it, then compress it into a zip file for upload.


Go to the top of the page
post May 19 2017, 07:37 AM

UtterAccess VIP / UA Clown
Posts: 32,206
Joined: 21-January 04
From: LI, NY

Welcome to UtterAccess.

As my colleagues noted table design is the foundation of an efficiently run database. And it does not sound like you have properly designed your tables. It sounds like you have one table with fields for each question. It also sounds like you have a paper form and built your database to replicate that paper form. That is not good design. A relational database, like Access and a paper form are usually at odds in terms of designing the data structure.

A survey database requires a specific structure. The basic structure would look like this:

tblQuestions: QuestionID (Primary Key autonumber), Question
tblRespondents: RespondentID (PK Autonumber), Firstname, Lastname, other info about respondent
tblResponses: ResponseID (PK autonumber), RespondentID (Foreign Key), ResponseDate
tblAnswers: AnswerID (PK Autonumber), ResponseID (FK), QuestionID (FK), Answer

That's the basic structure. You can add to it if needed. For example, you might add a PageNo field to tblQuestions to group the questions by whihc page they fall on the paper form.

As for entering the data, since each answer is a separate record, you would have to reference the ResponseID for each record. Each Response record would correspond to one of the paper forms.

I'm not a big fan of the Navigation form. I think its more trouble than its worth. It was an attempt on the development team to allow for creating a more Web page like interface.

On one of my survey apps. I have a main form bound to tblResponses and a subform bound to tblAnswers linked on ResponseID. To start a new record I enter the Response info and then prepopulate tblAnswers with a record for each question with the QuestionID and ResponseID. Then, as I go through the record in the subform, I enter the answers.

If you want to have separate forms for each survey page you can certainly do that, just open another mainform subform, where you filter for the current ResponseID and filter the subform for the PageNo field I mentioned.

Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
Jeff B.
post May 19 2017, 07:41 AM

UtterAccess VIP
Posts: 10,051
Joined: 30-April 10
From: Pacific NorthWet

"How" depends on "what". As others have pointed out, you need to start with the data, not the forms.

Please describe the underlying data/table structure you're using...


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 12:57 PM