Data from multiple forms into one record

[Guest]

I am creating a survey, I wanted each question to come up in a new form (I
have a macro at the bottom of each form to open the form with the next
question). The problem is, Access is saving the data from each form as a
separate record.

Is there a way to make the data from the individual forms save into 1
record? Can I make each form for the same person have the same autonumber
primary key? Would that even help? Or do I need to start from scratch using
sub forms or some other method.

 

[Arvin Meyer [MVP]]

You can have only 1 primary key per table. Create a hidden form and set its
ID value to be equal to that on the first form you open. As soon as the
first form is dirtied by answering the question add a bit of code to the
AfterUpdate event of the first text box to be filled in:

Sub txtMyControl_AfterUpdate()
Forms!frmMyHiddenFormName!txtID = Me.txtID
End Sub

What you need to do it open each succeeding form with code like:

DoCmd.OpenForm "frm2",,,, "ID = " & Forms!frmMyHiddenFormName!txtID

txtID being the textbox on form 1 and on the hidden form that holds your ID
(autonumber) field. The next form will open to the same ID and you can fill
in the next answer.

You can do it with more complex code and unbound forms, but simpler is
better (or at least easier).

 

[Guest]

Uh Oh, I think I'm in over my head...
-Some of the questions don't allow me to put in an AfterUpdate event (a
series of yes/no checkboxes), can I do it as an "On Click" event for a text
box below the question?
-Also, I'm not clear on where the 2nd line of code you offered gets entered
(in the AfterUpdate event or as a macro?)
-Finally, is 'txtID' in your sample code a placeholder for the field name
that I put into the original table?

If I'm beyond help feel free to say so, but if you don't mind breaking it
down into even more basic steps that would be great. I'm also happy to
contact you directly if that is easier.

 

[Arvin Meyer [MVP]]

Uh Oh, I think I'm in over my head...
-Some of the questions don't allow me to put in an AfterUpdate event (a
series of yes/no checkboxes), can I do it as an "On Click" event for a
text
box below the question?

The series of checkboxes are probably in a Frame (more correctly called an
Option Group). It you click on the frame itself in Design View, you'll see
that there is an AfterUpdate event for the entire group.

-Also, I'm not clear on where the 2nd line of code you offered gets
entered
(in the AfterUpdate event or as a macro?)

It's in the after update event. And BTW, I just noticed that I typed an
extra comma, the code should be:

DoCmd.OpenForm "frm2",,,, "ID = " & Forms!frmMyHiddenFormName!txtID

 

[Arvin Meyer [MVP]]

Last post sent to soon:


The series of checkboxes are probably in a Frame (more correctly called an
Option Group). It you click on the frame itself in Design View, you'll see
that there is an AfterUpdate event for the entire group.
It's in the after update event. And BTW, I just noticed that I typed an
extra comma, the code should be:

DoCmd.OpenForm "frm2",,, "ID = " & Forms!frmMyHiddenFormName!txtID

Yes that's exactly what it is, a placeholder for the name of the textbox
control which houses the data in the ID field.

You'll be fine. Just ask specific questions and someone will answer them
poste haste.