Carry the Key ID field to subforms when entering new records

[Guest]

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?

 

[Rick Brandt]

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?

You are misunderstanding what a subform is. A subform is one that is embedded
within another using a subform control. If you are opening the forms separately
then they are not subforms and that is why the linked field behavior is not
there. If you were using a true subform then the MasterLink and ChildLink
properties would provide the behavior you desire.

What you have are simply forms opened with a filter applied, but as you have
seen that only affects which existing records are displayed, not new records
that are created. If these forms will ONLY be used in the manner you describe
(when the main form is also opened) then you can set the DefaultValue property
of the linked field so that it gets its value from the main form using...

=Forms!EVENT!EVENTID

 

[Guest]

Thanks Rick for responding!

I tried what you suggested and I am closer, but now I get an error message
saying the record cannot be updated. The first field of focus on the
secondary form is the EVENTID field, then the PERSONID field. After I get the
functionality to work, I will make these fields invisible to the user. After
clearing the error message, I can click in a field and type away. The arrow
in the margin that appears when I first open the form turns into a pencil
after I clear the error message.

How do I get the secondary form to open and be ready to accept a new record
without the error message? And can I select a field to get first focus
without tabbing thru the PERSONID field first?

 

[Rick Brandt]

Thanks Rick for responding!

I tried what you suggested and I am closer, but now I get an error message
saying the record cannot be updated.

What does your code do besides open the form?

The first field of focus on the
secondary form is the EVENTID field, then the PERSONID field. After I get the
functionality to work, I will make these fields invisible to the user. After
clearing the error message, I can click in a field and type away. The arrow
in the margin that appears when I first open the form turns into a pencil
after I clear the error message.

How do I get the secondary form to open and be ready to accept a new record
without the error message? And can I select a field to get first focus
without tabbing thru the PERSONID field first?

You can make whatever control you like the first one in the TabOrder by setting
its TabIndex to zero.

 

[Guest]

What does your code do besides open the form?

I don't know what Here is the code I copied from the EVENT 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 Form_Current procedure I copied from somewhere on the web. I thought it
would solve one of my problems, but now I wonder if it didn't create more
problems or if it isn't needed at all. Sorry the naming conventions are
kooky, I really don't know what I am doing, so its kinda a mess.

Any ideas?

 

[Rick Brandt]

What does your code do besides open the form?

I don't know what Here is the code I copied from the EVENT 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 Form_Current procedure I copied from somewhere on the web. I
thought it would solve one of my problems, but now I wonder if it
didn't create more problems or if it isn't needed at all. Sorry the
naming conventions are kooky, I really don't know what I am doing, so
its kinda a mess.

Any ideas?

It Current event doesn't appear to be doing anything for you since you are not
using the filter criteria that it is building anyway.

What exactly opens the second form? A button press? I don't see anyting that
would cause your error, but if the form is already opened and has had data
entered then some of the code above would trigger a save attempt on the form and
if the record is not a "legal" record then that would cause an error.

 

[Guest]

OK, I deleted the Form Current stuff and it didn't seem to matter.
I got the tab order to start where I want too.

On the main EVENT form, I put a Control button called AddPerson. When I
click the button, the secondary form PERSON opens and the EVENTID field is
now filled with the default value from the EVENT form. BUT, it is not set to
receive new data. It isn't in edit mode, I guess. As soon as I clear the
error message, though, the EVENTID field also clears and I lose the link from
the EVENT table. HELP!!!

Is there a better way to design the flow of this information? Because of the
many to many relationships, I thought this would be easier for the user. I
wanted to eventually have the people and vehicle tables become lookup tables
so there wouldn't be two or more records in there of the same person...save
some typing and typos!! Am I crazy or stupid? Either way, I'm stuck.

What does your code do besides open the form?

I don't know what Here is the code I copied from the EVENT 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 Form_Current procedure I copied from somewhere on the web. I
thought it would solve one of my problems, but now I wonder if it
didn't create more problems or if it isn't needed at all. Sorry the
naming conventions are kooky, I really don't know what I am doing, so
its kinda a mess.

Any ideas?

It Current event doesn't appear to be doing anything for you since you are not
using the filter criteria that it is building anyway.

What exactly opens the second form? A button press? I don't see anyting that
would cause your error, but if the form is already opened and has had data
entered then some of the code above would trigger a save attempt on the form and
if the record is not a "legal" record then that would cause an error.

 

[Rick Brandt]

OK, I deleted the Form Current stuff and it didn't seem to matter.
I got the tab order to start where I want too.

On the main EVENT form, I put a Control button called AddPerson. When
I click the button, the secondary form PERSON opens and the EVENTID
field is now filled with the default value from the EVENT form. BUT,
it is not set to receive new data. It isn't in edit mode, I guess. As
soon as I clear the error message, though, the EVENTID field also
clears and I lose the link from the EVENT table. HELP!!!

If all your code in the first form is doing is opening the second form then I
fail to see why you would get an error about "performiong an update". The mere
act of opening the form should not begin a new record and you wouldn't get an
error like that until some kind of entry was made.

Is there some code in the open, load, or current event of the second form being
opened?

Is there a better way to design the flow of this information? Because
of the many to many relationships, I thought this would be easier for
the user. I wanted to eventually have the people and vehicle tables
become lookup tables so there wouldn't be two or more records in
there of the same person...save some typing and typos!! Am I crazy or
stupid? Either way, I'm stuck. "Rick Brandt" wrote:

Well, using actual subforms would definitely be easier because it would take
care of a lot of this for you without funny default values or code. The way you
are going though should work.

 

[Guest]

If all your code in the first form is doing is opening the second form then I
fail to see why you would get an error about "performiong an update". The mere
act of opening the form should not begin a new record and you wouldn't get an
error like that until some kind of entry was made.

Well, you are right, I don't get the error message until I start typing in
the "First Name" field. I thought I just read in the help file that you
cannot update a query based on multiple tables in a many to one to many
relationship. Would this apply to my situation on forms? Could this be a clue
as to why I can't carry the eventID field, create the link to the Person
table and add a person to the event?

Is there some code in the open, load, or current event of the second form being
opened?

The only code I have in the person form is:
----------------------
Function setImagePath()
Dim strImagePath As String
On Error GoTo PictureNotAvailable
strImagePath = Me.txtImageName
Me.ImageFrame.Picture = strImagePath
Exit Function
PictureNotAvailable:
strImagePath = "C:\Pictures for Access\spdpatch.gif"
Me.ImageFrame.Picture = strImagePath
End Function
-------------------------------

Well, using actual subforms would definitely be easier because it would take
care of a lot of this for you without funny default values or code. The way you
are going though should work.

Maybe if I can't get this to work soon, I will switch to subforms and take
care of viewing related data via customized reports. This is supposed to be
a system where not only can the user enter events, but also query the
database for people, vehicles, or events and view all related data. I also
want to design a report on gang affiliations, sort of a "whos who" that
officers can generate and get a current snapshot of the players when they
input a particular gang. I haven't even ventured into the Switchboard stuff
yet. Thoughts?

 

[Rick Brandt]

Well, you are right, I don't get the error message until I start
typing in the "First Name" field. I thought I just read in the help
file that you cannot update a query based on multiple tables in a
many to one to many relationship. Would this apply to my situation on
forms? Could this be a clue as to why I can't carry the eventID
field, create the link to the Person table and add a person to the
event?

Why is the form bound to a query with multiple tables? Generally a form used
for editing should be bound directly to the table or to a query using only that
one table for input. Multi-table queries "can" be made editable, but they often
are not.

 

[Guest]

Why is the form bound to a query with multiple tables? Generally a form used
for editing should be bound directly to the table or to a query using only that
one table for input. Multi-table queries "can" be made editable, but they often
are not.

The EVENT form has a one-to-many relationship to its location. The record
source is a query of all the fields from the event table plus the fields from
the location table.

The PERSON form is a form with a subform for HOME ADDRESS. The record source
for the person form is a query of all the fields from the person table plus
the linking table to get the EVENTID. The HOME ADDRESS subform, being a true
subform, has parent and child linking fields of PERSONID. The record source
for the subform is a query of all the fields from the home address table plus
the linking table to get PERSONID.

Are these multiple table queries?

 

[Rick Brandt]

The EVENT form has a one-to-many relationship to its location. The
record source is a query of all the fields from the event table plus
the fields from the location table.

But you're opening a separate form for the entry of Locations aren't you? Isn't
that what all of this form opening has been about? If you are opening a
separate Location form to make entries into that table then you do not need any
of the location fields in the query used by the EVENT form and you don't need
any of the event fields in the query used by the Location form.

The PERSON form is a form with a subform for HOME ADDRESS. The record
source for the person form is a query of all the fields from the
person table plus the linking table to get the EVENTID. The HOME
ADDRESS subform, being a true subform, has parent and child linking
fields of PERSONID. The record source for the subform is a query of
all the fields from the home address table plus the linking table to
get PERSONID.

But doesn't the home address table already have a foreign key for the PersonID?
That is all you need. You don't need to join to the Person table. That is what
the linking properties of the subform are for.

Are these multiple table queries?

I'm getting a bit lost on your table structures so let's go with the classic
simple example, the Sales Order. This would ordinarily be a main form bound to
an Orders table with a subform bound to a LineItems table.

Both the Orders table and the LineItems table will have a common field
[OrderNumber] to link them. This is the Primary Key in the Orders table and a
Foriegn Key in the LineItems table. Each form would be bound directly to its
corresponding table or to a query based only on that table. Neither form has to
include fields from the other table as the linking field [OrderNumber] provides
all of the linking that we need. The MasterLink and ChildLink properties of the
subform control would both be set to "OrderNumber".

My understanding of your situaton was that you want to mimic the same
relationship as described in my Sales Orders example except that you want the
child form to be opened separately instead of using an embedded subform. If
that is the case then you still have no need to include any table in the query
for your forms except for the one table that you want to each form to write to.
That is not to say that you cannot or must not have other tables included, but
if you do you have to be very careful with the structure of the query or it will
not allow edits to the data.

 

[Guest]

But you're opening a separate form for the entry of Locations aren't you? Isn't
that what all of this form opening has been about? If you are opening a
separate Location form to make entries into that table then you do not need any
of the location fields in the query used by the EVENT form and you don't need
any of the event fields in the query used by the Location form.

Nope. On the main EVENT form, since there will always only be 1 location per
event, I include fields from the LOCATION table here. Since many events can
happen at a common location, (i.e., bar fights) I decided to make this one
(EVENT) to many (LOCATION) to save typing in the location several times. So
in this case, there is a foreign EVENTID field in the location table.

But doesn't the home address table already have a foreign key for the PersonID?
That is all you need. You don't need to join to the Person table. That is what
the linking properties of the subform are for.

I thought because its a many-to-many relationship, I had to create a linking
table. The reason I have to pull the EVENTID into all these queries is to
synchronize the forms. I wanted to be able to view and cycle thru all the
people with their home addresses for each event that is selected on the main
form. When I take out the EVENTID field in the forms data source query, I
lose the synchronization. I'm probably just doing it wrong.

Maybe I should make the data entry part simpler, not worry about
synchronizing, and re-design using the subform route you mention above. Then
I can build reports that show all the relationships instead. What do you
think?

I can send you a screen shot of my relationship set-up or some of the forms
if that would help to visualize? I really appreciate your help on this, I am
feeling overwhelmed by this project, but have invested so much time already
and its something the department REALLY needs. I'm grateful for your
suggestions!!

 

[Rick Brandt]

Maybe I should make the data entry part simpler, not worry about
synchronizing, and re-design using the subform route you mention
above. Then I can build reports that show all the relationships
instead. What do you think?

Well your basic problem is making the second form editable upon opening and
that should be a completely separate issue to the carrying over of the
linking field values. You need to examine the query for that form and see
if it can be adjusted to make it editable. Check the help topic "When can I
update data in a query?".