Synchronizing two subforms to main form when adding new records

Hello,

I have a main EVENT form with two control buttons which open VEHICLE and
PERSON subforms. They are many-to-many relationship to EVENT table and use
link tables to join them. The subforms are synchronized with the main form on
existing records in the database and show related data for any event
displaying on the main form like they should. BUT, when I enter a new record,
the subforms are not picking up the EVENTID field to link the PERSON or
VEHICLE records to the event. The buttons work like I think they should,
except the link is not happening automatically.

How is the EVENTID key field carried over to the subform after I click the
control button? Is there additional code I need to shift focus or add new
record or something? The application is just not creating the link record in
the link tables. I am not a programmer, so this is probably really easy and I
just don't know enough about this to fix it. Can anyone please help?

ACCESS will insert the linking field's value if you use the LinkChildFields
and LinkMasterFields properties of the subform control (the control that
actually holds the main form). See Help file for info on how to use them;
post back if you have questions.

If for some reason you're not able to use these properties, then you'll need
to run programming in the subform when you insert a new record, and that
programming will have to read the required values from the main form and
insert them into the appropriate fields in the subform's RecordSource.

--

Ken Snell
MS ACCESS MVP

"JustJen" wrote in message
...
Hello,

I have a main EVENT form with two control buttons which open VEHICLE and
PERSON subforms. They are many-to-many relationship to EVENT table and use
link tables to join them. The subforms are synchronized with the main form
on
existing records in the database and show related data for any event
displaying on the main form like they should. BUT, when I enter a new
record,
the subforms are not picking up the EVENTID field to link the PERSON or
VEHICLE records to the event. The buttons work like I think they should,
except the link is not happening automatically.

How is the EVENTID key field carried over to the subform after I click the
control button? Is there additional code I need to shift focus or add new
record or something? The application is just not creating the link record
in
the link tables. I am not a programmer, so this is probably really easy
and I
just don't know enough about this to fix it. Can anyone please help?

Hello Ken, thanks for your suggestions!

I was confusing a subform with a regular form that is opened up by a control
button on my main form. On suggestions from another, I set the defalt value
of the linking field to carry over from the main form. Now, I get an error
message saying the new record cannot be created. After I clear the error
message, I can type in new information, but the EVENTID also clears at this
point and I lose the link between the tables. Any idea why this is happening?
Here is the code I copied from the main form.

---------------------------
Private Sub Form_Current()

' Declare and set a variable to store the WHERE
' clause that describes the records you want to
' display.
Dim strCond As String
strCond = "EventID = Forms!Event - Wizard!EventID"

' Use the IsLoaded function from the Northwind
' sample database to check whether the Products
' form is open, then set the properties.
If IsLoaded("Person Subform (Current)") Then
Forms![Person Subform (Current)].FilterOn = True
Forms![Person Subform (Current)].Filter = strCond
End If

End Sub
----------------------------------
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![Person Subform (Current)].DataEntry = True
Else
Forms![Person Subform (Current)].Filter = "[Event ID] = " &
Me![EventID]
Forms![Person Subform (Current)].FilterOn = True
End If

End Sub
--------------------------------
Private Sub AddPerson_Click()
On Error GoTo Err_AddPerson_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Person Subform (Current)"

stLinkCriteria = "[Event ID]=" & Me![EventID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AddPerson_Click:
Exit Sub

Err_AddPerson_Click:
MsgBox Err.Description
Resume Exit_AddPerson_Click

End Sub
-------------------------------
Private Sub AddVeh_Click()
On Error GoTo Err_AddVeh_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Vehicle Subform"

stLinkCriteria = "[Event ID]=" & Me![EventID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AddVeh_Click:
Exit Sub

Err_AddVeh_Click:
MsgBox Err.Description
Resume Exit_AddVeh_Click

End Sub
---------------------------------
the first procedure comes from the web, I thought it would solve a problem,
but now I don't think I need it or perhaps it is causing the problem.

If for some reason you're not able to use these properties, then you'll need
to run programming in the subform when you insert a new record, and that
programming will have to read the required values from the main form and
insert them into the appropriate fields in the subform's RecordSource.

Ken Snell
MS ACCESS MVP

The IsLoaded function will not work for telling you if a subform is "open"
within another form. That function as designed will tell you only if a form
is open as a separate main form.

I think I see that you have another thread already underway on this
question, so it will be better if we don't duplicate information here and
there. Stay with that thread and continue asking questions there.

--

Ken Snell
MS ACCESS MVP


"JustJen" wrote in message
...
Hello Ken, thanks for your suggestions!

I was confusing a subform with a regular form that is opened up by a
control
button on my main form. On suggestions from another, I set the defalt
value
of the linking field to carry over from the main form. Now, I get an error
message saying the new record cannot be created. After I clear the error
message, I can type in new information, but the EVENTID also clears at
this
point and I lose the link between the tables. Any idea why this is
happening?
Here is the code I copied from the main form.

---------------------------
Private Sub Form_Current()

' Declare and set a variable to store the WHERE
' clause that describes the records you want to
' display.
Dim strCond As String
strCond = "EventID = Forms!Event - Wizard!EventID"

' Use the IsLoaded function from the Northwind
' sample database to check whether the Products
' form is open, then set the properties.
If IsLoaded("Person Subform (Current)") Then
Forms![Person Subform (Current)].FilterOn = True
Forms![Person Subform (Current)].Filter = strCond
End If

End Sub
----------------------------------
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![Person Subform (Current)].DataEntry = True
Else
Forms![Person Subform (Current)].Filter = "[Event ID] = " &
Me![EventID]
Forms![Person Subform (Current)].FilterOn = True
End If

End Sub
--------------------------------
Private Sub AddPerson_Click()
On Error GoTo Err_AddPerson_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Person Subform (Current)"

stLinkCriteria = "[Event ID]=" & Me![EventID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AddPerson_Click:
Exit Sub

Err_AddPerson_Click:
MsgBox Err.Description
Resume Exit_AddPerson_Click

End Sub
-------------------------------
Private Sub AddVeh_Click()
On Error GoTo Err_AddVeh_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Vehicle Subform"

stLinkCriteria = "[Event ID]=" & Me![EventID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AddVeh_Click:
Exit Sub

Err_AddVeh_Click:
MsgBox Err.Description
Resume Exit_AddVeh_Click

End Sub
---------------------------------
the first procedure comes from the web, I thought it would solve a
problem,
but now I don't think I need it or perhaps it is causing the problem.

If for some reason you're not able to use these properties, then you'll
need
to run programming in the subform when you insert a new record, and that
programming will have to read the required values from the main form and
insert them into the appropriate fields in the subform's RecordSource.

Ken Snell
MS ACCESS MVP

On Mon, 3 Jul 2006 14:04:01 -0700, JustJen
wrote:

I have a main EVENT form with two control buttons which open VEHICLE and
PERSON subforms.

Jen, I think the confusion here is over the term "subform". These
*aren't subforms*, if you're opening them separately as popup forms.

A Subform control is a box on a mainform, containing another form. The
Subform Control has a master and a child link field property. These
control the linkage between the "one" side table on the mainform, and
the "many" side table on the subform. Neither form should be based on
a Query based on both tables.

If you're popping up a separate form, using a command button, then
*you* must provide the linkage in some non-trivial VBA code. It won't
be given to you automatically.

John W. Vinson[MVP]