Form navigation is screwing up my records

L

laura.dodge

Hi, I've created a database to manage the results of a very lengthy
questionnaire. Due to size constraints, I have five tables that hold
my data. Each field in each table has a corresponding control in one
of five corresponding forms. The idea is for the user to be able to
begin on form1 (frmB101) and navigate through the other four forms. At
the end, she should be able to go back to form1 and start entering
data from another participant. I've put command buttons on each form
to close the current form and open the next form. On load, the
previous form will pass the subject ID number to the next form. The
subject ID is my primary key, and is locked on forms 2-5. All of this
worked wonderfully for record 1. At form5 I can click the 'New'
button, which will take me to form1, record 2. The problem begins when
I try to navigate to form2, record2. Access will take me to form2, but
it will be record1. Because of the passing of the ID number, this
returns a blank record1 with the new ID number, so it's overwritten my
previous record. Adding code to go to a new record on load doesn't
work, because each time I navigate to a new form, the record number
increases by 1, which leaves me with duplicate primary key values.

Does anyone have any ideas on how to tell Access to stay on record2
for all the forms until I get to the end? I've included some of my
code for form1 (frmB101) below in case that is helpful. Thanks so much
for any suggestions!

Option Compare Database

Private Sub cmdB101_Click()
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm "frmB312", OpenArgs:=Me.B101
DoCmd.GoToRecord , , acNewRec
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Private Sub cmdB101New_Click()
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm "frmB101"
DoCmd.GoToRecord , , acNewRec
End Sub
 
J

Jeff Boyce

Laura

The phrase "due to size constraints" seems like something of a red flag...

In a well-normalized relational database, it is quite rare to need to split
data across multiple tables.

If you'll describe your situation, and provide an example of what kind of
data you are splitting across tables, folks here may be able to offer more
specific suggestions.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
L

laura.dodge

Hi, I'll try to explain in better detail what I'm trying to do. I have
a questionnaire that is about 30 pages long and has hundreds of
questions about an individual's demographics and pregnancy history. I
don't have any formal Access training beyond a short class, so I
somewhat understand the concept of normalized tables, but I don't see
how I would apply it here. In addition to having hundreds of questions
to begin with, some questions require multiple fields. This is usually
a text field so the user can specify an answer of 'Other.' Some of the
questions are 'check all that apply' and because I'm using 2003, I had
to make a separate field for each answer choice, which has a yes/no
check box. So I have the first 54 questions, which require 72 fields,
on the first table. I'm trying to keep the sections together, and this
was all I could fit on this form without splitting the next section. I
thought that you could put up to 255 fields on a table, but I guess my
field sizes are too big or something, though I've tried to keep them
as small as possible. I have a subject ID number that I want to use to
link the tables. Is there other information that would be useful?

I realize that this may not be the best way to organize things, but
I've already spent a ton of hours on this database and have had to
redo things multiple times. So if it's at all possible to fix this
problem with the database organized as it is right now, that would be
ideal. Thanks so much for all your help!
 
J

Jeff Boyce

It seems you're trying to treat Access as if it were a spreadsheet. It
isn't.

Here's a link to an approach for handling surveys (i.e., multiple
questions/responses) that Duane H. came up with:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
J

Jeff Boyce

You've convinced me, Ken ...

(this is a "pay now or pay later" situation <g>!)

Jeff Boyce
 
J

Jeff Boyce

And to you as well, Ken.

(if you checked with some of my ... colleagues ..., you might not consider
my approach quite so "pure". I do try to use the tool designed for the job,
though, and I can say, in all honesty, that I've never tried to drive nails
with my chainsaw ... while it was running ... <g>)

Jeff B.

KenSheridan via AccessMonster.com said:
Sadly we sometimes have to compromise our purist instincts, I'm afraid. I
guess that's what they mean by 'realpolitik'.

All the best for the holiday, Jeff.

Ken Sheridan
Stafford, England

Jeff said:
You've convinced me, Ken ...

(this is a "pay now or pay later" situation <g>!)

Jeff Boyce
Unfortunately you have fallen into the common trap of having multiple
columns
[quoted text clipped - 143 lines]
DoCmd.GoToRecord , , acNewRec
End Sub
 
L

laura.dodge

Thanks so much Ken, this works beautifully! As always, I am very
grateful to the wonderful people who answer these questions!

Thanks again, and happy holidays,
Laura
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top