Opening Forms w/Command Button

  • Thread starter Thread starter Slej
  • Start date Start date
S

Slej

My situation involves three forms: PARTICIPANTS,
REGISTRATIONS, and CSQ8. PARTICIPANTS has a one-to-many
relationship with REGISTRATIONS. REGISTRATIONS has a one-
to-one relationship with CSQ8.

On the PARTICIPANTS' form, I have a command button to
open the REGISTRATIONS form. The REGISTRATIONS form has a
command button to open CSQ8.

Basically, the data-entry flow for these forms works like
this: enter a participant's information in the
PARTICIPANTS form (name, address, etc.). The key field
is PARTICIPANT_ID (as an autonumber). Next, one would
press the REGISTRATIONS button to enroll that participant
in an event. The REGISTRATIONS form also contains the
PARTICIPANT_ID field and I have it set up so the control
pulls across the ID number for the participant that was
just entered. A REGISTRATION_ID is generated in this
table (autonumber and it's the key field) for this
event. At a later date, the participant will take a
Customer Satisfaction Questionnaire (CSQ8). The CSQ8
button on the REGISTRATIONS table brings up a CSQ8 form
with the unique REGISTRATION_ID for the registration that
called it. REGISTRATION_ID is also the key field for the
CSQ8 table.

I thought this setup would work so each participant could
enroll in multiple events and take one survey for each
event. Two things are happening:

1. the information entered into the REGISTRATIONS, and
CSQ8 forms is not retained (even after a SAVE).

2. if I change information in these forms while in the
process of entering data, when I go to save the form I
get:

ERROR 3201: You cannot add or change a record because a
related record is required in table PARTICIPANTS.

I don't understand this because there IS a related record
in PARTICIPANTS. For the life of me, I can't figure out
how to get any information entered into either the
REGISTRATIONS or CSQ8 to be retained and how to make this
error go away. Any help would be greatly appreciated.
Thanks.
 
Slej,

A question for clarification...
When you say the data is not retained, have you checked in the
Registrations table to see if (any of) the data is there, or are you
referring to when you re-open the form?

I would personally combine the Registrations and SCQ8 tables into one.
You can still have two forms for the two functions based on this
table. I would also try to use a Registrations Subform on the
Participants form, rather than a separate form.

- Steve Schapel, Microsoft Access MVP
 
Steve,

Thank you so much for responding. Regarding your
clarification question: when I re-open the form, there
is no data.

Initially, I had the REGISTRATIONS form as a subform to
PARTICIPANTS with the CSQ8 as a subform to that. This
seemed to work okay (I could add multiple registrations
to one participant ID) but I ditched that idea because
there are actually other tables in addition to the CSQ8
that have to be linked to each registration ID and I
didn't want the form to become too crowded (all of the
other tables were giving me the same problem as the CSQ8
so I decided to only use one for this inquiry) - that's
why I decided to use command buttons.

Is there no way to still use the buttons on the
REGISTRATION form so I can:

1. retain the data entered when accessing CSQ8
2. have multiple registrations to one participant ID

Thank you.
-----Original Message-----
Slej,

A question for clarification...
When you say the data is not retained, have you checked in the
Registrations table to see if (any of) the data is there, or are you
referring to when you re-open the form?

I would personally combine the Registrations and SCQ8 tables into one.
You can still have two forms for the two functions based on this
table. I would also try to use a Registrations Subform on the
Participants form, rather than a separate form.

- Steve Schapel, Microsoft Access MVP
Click to expand...
 
Slej,

It sounds to me that when you enter the Registration information into
the secondary form, it is being entered. And then, the procedure you
are using to open the Registrations form is getting the form opened at
a new record. So you don't get to see the previously entered data.
Is the Registrations form in single view or continuous view? What is
the details of the routine on your button that opens Registrations?
What method are you using to assign the Participant ID to a new
Registration?

As regards the CSQ8, have you considered using a Tab Control, with the
core registration data on one tab and the CSQ8 on another?

- Steve Schapel, Microsoft Access MVP
 
Steve,

First, thank you so much for taking so much time to help
me work through this. :)

Regarding your questions:
1. Is the Registrations form in single view or
continuous view? The form is in single view.

2. What is the details of the routine on your button
that opens Registrations?
REGISTER button on PARTICIPANTS form
Private Sub register_Click()
On Error GoTo Err_register_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Registrations"

stLinkCriteria = "[participant_id]=" & Me!
[participant_id]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_register_Click:
Exit Sub

Err_register_Click:
MsgBox Err.Description
Resume Exit_register_Click

End Sub

3. What method are you using to assign the Participant
ID to a new Registration?
I'm not sure what you mean but I have a one-to-many
relationship between PARTICIPANTS and REGISTRATIONS. The
only means I have to add a new registration to a
participant is to select the NEW RECORD button on the
REGISTRATIONS form for that participant.

4. As regards the CSQ8, have you considered using a Tab
Control, with the core registration data on one tab and
the CSQ8 on another? I hadn't thought about that at all
but I'm going to work on it and see what happens. This
may be the solution for me. I'll let you know.

- Sharon James


-----Original Message-----
Slej,

It sounds to me that when you enter the Registration information into
the secondary form, it is being entered. And then, the procedure you
are using to open the Registrations form is getting the form opened at
a new record. So you don't get to see the previously entered data.
Is the Registrations form in single view or continuous view? What is
the details of the routine on your button that opens Registrations?
What method are you using to assign the Participant ID to a new
Registration?

As regards the CSQ8, have you considered using a Tab Control, with the
core registration data on one tab and the CSQ8 on another?

- Steve Schapel, Microsoft Access MVP
Click to expand...
 
Sharon,

Sorry to be peppering you with more questions than answers, but still
trying to spot where the problem might lie.

Relating to your answer to point 3...
When you enter a new Registration on the Registration form, can you
see the Participant_id control on the form? If the Registration form
was a subform on the Participant form, the Participant_id for a new
registration would be automatically entered by Access with no further
effort on your part. But it's not, so it won't. In order for the
Participant_id to be entered, you either have to put it in there by
physical data entry, or else you have to set up some other means of
doing it, either by macro or VBA code or default value setting. At
this stage it is not clear to me that this has been properly
established. If the correct Participant_id is not being entered in
the Registrations table, this would explain some of the problems you
are having. Open the Registration table, and look at the data in the
Participand_id field to see if it is as expected. If all is present
and accounted for, well... the problem must be somewhere else :-)

A further comment about point 4...
If you did it this way, it would facilitate the Registration and CSQ8
table to be combined into one (where they belong!)

- Steve Schapel, Microsoft Access MVP
 
Steve,

The Registrations form is pulling over the correct
Participant ID. That was accomplished when I created the
button. The Command Button Wizard prompts for the field
that is to be linked if one wants specific records to be
displayed. I have a participant ID field in the
REGISTRATIONS table so I was able to make that link.

Thanks.
- Sharon
-----Original Message-----
Sharon,

Sorry to be peppering you with more questions than answers, but still
trying to spot where the problem might lie.

Relating to your answer to point 3...
When you enter a new Registration on the Registration form, can you
see the Participant_id control on the form? If the Registration form
was a subform on the Participant form, the Participant_id for a new
registration would be automatically entered by Access with no further
effort on your part. But it's not, so it won't. In order for the
Participant_id to be entered, you either have to put it in there by
physical data entry, or else you have to set up some other means of
doing it, either by macro or VBA code or default value setting. At
this stage it is not clear to me that this has been properly
established. If the correct Participant_id is not being entered in
the Registrations table, this would explain some of the problems you
are having. Open the Registration table, and look at the data in the
Participand_id field to see if it is as expected. If all is present
and accounted for, well... the problem must be somewhere else :-)

A further comment about point 4...
If you did it this way, it would facilitate the Registration and CSQ8
table to be combined into one (where they belong!)

- Steve Schapel, Microsoft Access MVP


Steve,

First, thank you so much for taking so much time to help
me work through this. :)

Regarding your questions:
1. Is the Registrations form in single view or
continuous view? The form is in single view.

2. What is the details of the routine on your button
that opens Registrations?
REGISTER button on PARTICIPANTS form
Private Sub register_Click()
On Error GoTo Err_register_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Registrations"

stLinkCriteria = "[participant_id]=" & Me!
[participant_id]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_register_Click:
Exit Sub

Err_register_Click:
MsgBox Err.Description
Resume Exit_register_Click

End Sub

3. What method are you using to assign the Participant
ID to a new Registration?
I'm not sure what you mean but I have a one-to-many
relationship between PARTICIPANTS and REGISTRATIONS. The
only means I have to add a new registration to a
participant is to select the NEW RECORD button on the
REGISTRATIONS form for that participant.

4. As regards the CSQ8, have you considered using a Tab
Control, with the core registration data on one tab and
the CSQ8 on another? I hadn't thought about that at all
but I'm going to work on it and see what happens. This
may be the solution for me. I'll let you know.

- Sharon James
Click to expand...

.
Click to expand...
Click to expand...
 
Sharon,

Sorry to doubt you. But I confess I am not convinced. I have never
used a command button wizard, but I feel sure that no wizard would
include the functionality you mentioned, as what you are trying to do
is very unusual. Can you please open the Registrations table and
check what data is in the Participant_id field? Ta.

- Steve Schapel, Microsoft Access MVP
 
Steve,

Regarding the Command Button wizard: I selected to have
the button open a form. It then asks:

"Do you want the button to find specific information to
display in the form? For example, the button can open a
form and display the data for a specific employee or
customer.
- Open the form and find specific data to display
- Open the form and show all the records"

You have to select one, and I've selected the first
option. The next screen in the wizard asks:

"Which fields contain matching data the button can use to
look up information? Select the fields and then click
the <-> button."

It then provides a list of the fields from both tables.
I've selected the participant_id field from the
PARTICIPANTS table and the REGISTRATIONS table.

Regarding the data in the Participant_ID field in the
REGISTRATIONS table: In design view, the control source
for the participant id field is:

=Forms!Participants!participant_id

In the actual table itself, there are no records,
therefore, the field is blank.

- Sharon
-----Original Message-----
Sharon,

Sorry to doubt you. But I confess I am not convinced. I have never
used a command button wizard, but I feel sure that no wizard would
include the functionality you mentioned, as what you are trying to do
is very unusual. Can you please open the Registrations table and
check what data is in the Participant_id field? Ta.

- Steve Schapel, Microsoft Access MVP
Click to expand...
 
Sharon,

Thank you for the further explanation.

First of all, the wizard process that you described has to do with
opening the Registrations form to show existing records related to the
current record on the Participant form. This is irrelevant to your
purpose, as you are wanting to enter new records on the registration
form, which is a totally different matter.

Second, just so we are speaking the same language, regarding your
statement:
Regarding the data in the Participant_ID field in the
REGISTRATIONS table: In design view, the control source
for the participant id field is:
=Forms!Participants!participant_id
Click to expand...
.... fields do not have a control source. Controls on Forms have a
control source. I was talking about the Table, not the Form. Just
because you can't see any data on a form does not necessarily mean
there is no data in the table that the form is based on, for a number
of reasons.

So, can I just confirm... The Registrations form is based on the
Registrations table, and the Registrations table includes a field
called Participant_id. Is that correct?

And then, on the form there is a textbox with a control source
=Forms!Participants!participant_id
Well, this is not correct. I can't imagine how this got there, but it
shouldn't be there. Change its controlsource to particpant_id (unless
there is already another control on the form bound to the
particpant_id field, in which case just delete it from the form.

When you try to enter a new record via the Registration form, the
registration has to be related to a participant. You have correctly
allowed for this by including a particpant_id field in the
Registration table. What I was trying to explain to you before is
that the problem is because there is no process in your form to enter
the applicable data in the participant_id field. You have apparently
been under the impression that the button wizard did something that
would make this happen, but it doesn't, and so you have a
misapprehension there.

So, how are we going to get the participant_id data entered? I have a
method to suggest, but it relies on the Participant form *always*
being open at the required particpant whenever you are trying to enter
an new Registration. Is this ok? If so, you can use a vba procedure
on the registration form's BeforeInsert event. It will look like
this...
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!Participant_id = Forms!Participants!Participant_id
End Sub

Please post back if you need any more help with this.

- Steve Schapel, Microsoft Access MVP
 
Back
Top