Form navigation is screwing up my records

[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

 

[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.

 

[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!

 

[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.

 

[KenSheridan via AccessMonster.com]

Unfortunately you have fallen into the common trap of having multiple columns
rather than multiple rows in related tables, but I can understand that at
this stage you are reluctant to remodel the database from scratch, so lets
look at things in the context of your current model.

1. Firstly you should make sure the four 'subsidiary' tables are related to
the first table in one-to-one relationships, and that referential integrity
is enforced. The first tables primary key column can be an autonumber if you
wish, but if so the others must have straightforward long integer data type
primary keys, not autonumbers. If you are using a predetermined value as the
key, such as a structured Patient Number, or if you are computing an
incrementing key value when a record is inserted into the first table, then
all will be the same data type of course.

2. Essentially you want to insert a row into the first table vai its form,
then another row into the second, with the same key value as the first, and
so on until the fifth table, then return to the first form to insert another
row, so:

3. When you start the process, e.g. from an unbound 'opening menu' form,
make sure you open the first form in data addition mode:

Const FORMNAME = "frmB101" ' first form's name

DoCmd.OpenForm FORMNAME, _
DataMode:=acFormAdd

4. To open the next form in the sequence, also open this in data addition
mode and pass the primary key value to it via the OpenArgs mechanism, at the
same time closing the current form, e.g.

Const FORMNAME = "frmB102" ' second form's name

DoCmd.OpenForm FORMNAME, _
DataMode:=acFormAdd, _
OpenArgs:= Me.ID

DoCmd.Close, acForm, Me.Name

Note that you don't need the acSaveNo argument, that only relates to saving
the form definition, not the data. Nor do you need to save the record by
setting the Dirty property to True as closing the form automatically does
this.

5. In the second form's Open event procedure set the DefaultValue property
of the ID control (which can be a hidden text box) to the value passed into
the form:

If Not IsNull(Me.OpenArgs) Then
Me.ID.DefaultValue = """" & Me.OpenArgs & """"
End If

Note that the DefaultValue property is always a string expression, regardless
of the data type involved, so is wrapped in quotes characters. Often this
isn't essential, but sometimes its crucial, so should always be done.

6. Repeat steps 4 and 5 for each form until the final fifth form. In its
button's event procedure open the first form again in data addition mode and
close the current form:

Const FORMNAME = "frmB101" ' first form's name

DoCmd.OpenForm FORMNAME, _
DataMode:=acFormAdd

DoCmd.Close, acForm, Me.Name

If I've understood correctly how you've set things up this should enable you
to cycle through the five forms until the cows come home, inserting a new
record in each with the same ID values at each cycle.

BTW, as it happens I've recently put together a little .mdb file for
demonstration purposes, which can be used to build and use questionnaires
along more normalized lines. It would not fit the bill for your
questionnaire as it only allows the user to select from a set of fixed
answers and not enter any free text (fixed answers are easily capable of
analysis, while free text isn't), but it would give you an idea of how these
sort of things are built, and you may even find some other use for it. You
may well feel you have enough on your plate with you current project to be
bothered with looking at an alternative design, but if you would like a copy
just drop me a line at:

kenwsheridan<at>yahoo<dot>co<dot>uk

All the best for the holiday,

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

You've convinced me, Ken ...

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

Jeff Boyce

 

[KenSheridan via AccessMonster.com]

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

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]

 

[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.

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

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

 

[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