"joining" Data Entry Forms, Access 2013
May 19 2017, 03:30 AM
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.
May 19 2017, 06:34 AM
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.
May 19 2017, 06:50 AM
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.
May 19 2017, 07:37 AM
UtterAccess VIP / UA Clown
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.
May 19 2017, 07:41 AM
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...
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.
|Search Top Lo-Fi||22nd May 2017 - 08:11 PM|