Connect data from one form to data in another form

[NDClark]

I have a "Maintenance Records Subform" on the bottom of my "Vehicle
Information Form". When I click data in the Subform I have it set to open
that data for editing. This works fine.

Now I have put a button at the bottom of the original "Vehicle Information
Form" to open the same 'Maintenance Records Subform'. It does open blank.

However, it shows "####" in the 'ID field'. When I input data in the form
it is not connecting the new data to that specific vehicle. Please help

 

[Arvin Meyer [MVP]]

Subforms are not actually forms when part of a main form, they are controls
which have form properties. As such they have code which syncs the link
fields. If you open the subform by itself, it becomes a true form and you
must supply your own linking code. Remember that if you have it open in both
the main form and separately as a its own form, you are very likely to get
locking conflicts as you cannot edit the same page of records.

 

[NDClark]

I can open a "edit form" out of a subform and edit a piece of data. That is
no problem.

I want to click open a form with a button on the "Vehicle main form" and
ADD a new entry to the "Maintenance Table" which is also linked with the
"Vehicle Table".

What I have so far:
I can open the "Add Maint form" when I click the button at the bottom of the
"Vehicle main form".

The problem is:
The "Add Maintenance form" is not linking the information with the vehicle I
am adding it to. In the ID field it has "####"

 

[Arvin Meyer [MVP]]

The problem is:
The "Add Maintenance form" is not linking the information with the vehicle
I
am adding it to. In the ID field it has "####"

Make sure the width of the control is wide enough to display the entire ID
field.. A separate form is not linked you need to use code.

First set the default value of the ID link field to:

= Forms!Form1_Name!ID

so that new records will pick up the ID from the main form (remember, this
is the foreign key not a primary key in the second form).

Now you can either open it to a record, and if there is not, it will use the
default:

DoCmd.OpenForm "Form2", , , "ID =" & Me.txtID

or straight to a New record:

DoCmd.OpenForm "Form2", , , , acFormAdd

 

[NDClark]

I changed the where condition of the button to "Add New Maint Record" where
condition to [ID]=[Forms]![Vehicle Info]![Maint Record Entry]![ID] in the On
Click Macro.

The Open Form action is Maint Record Entry, Form, , [ID]=[Forms]![Vehicle
Info]![Maint Record Entry]![ID], Add, Normal

On the Maint Record Entry form I put the Default value of the ID of the
Vehicle Info 'Main Form' as [ID] =[Forms]![Vehicle Info]![ID]

When I click the button to add a new record, The Maint Record Entry form
opens to NEW in both ID fields. It should be opening to the Vehicle I was
working on ID field. The two fields are ID field for the Maint Record and
ID field for the Vehicle.

If I add a entry Access tells me there is no record for the Vehicle.

 

[Arvin Meyer [MVP]]

First there must be a record in the main form to link to. That means you
need to create a record and save it. Save it in the first code line of the
open second form code. Do not make any other changes to controls in the main
form other than the code in the button to open the second form.

The open form VBA code should read like (using macros does not allow any
error handling, and will run slower, and I'm not even sure that you can do
this with a macro):

DoCmd.OpenForm "Maint Record Entry", , , "ID =" & Me.ID

Use the wizard to create the code from the button, then delete anything
about stLinkCriteria and the DoCmd line and replace it with the line above

The DefaultValue property of the ID textbox in "Maint Record Entry" is an
expression which should read:

= [Forms]![Vehicle Info]![ID]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I changed the where condition of the button to "Add New Maint Record" where
condition to [ID]=[Forms]![Vehicle Info]![Maint Record Entry]![ID] in the
On
Click Macro.

The Open Form action is Maint Record Entry, Form, , [ID]=[Forms]![Vehicle
Info]![Maint Record Entry]![ID], Add, Normal

On the Maint Record Entry form I put the Default value of the ID of the
Vehicle Info 'Main Form' as [ID] =[Forms]![Vehicle Info]![ID]

When I click the button to add a new record, The Maint Record Entry form
opens to NEW in both ID fields. It should be opening to the Vehicle I was
working on ID field. The two fields are ID field for the Maint Record
and
ID field for the Vehicle.

If I add a entry Access tells me there is no record for the Vehicle.

Make sure the width of the control is wide enough to display the entire
ID
field.. A separate form is not linked you need to use code.

First set the default value of the ID link field to:

= Forms!Form1_Name!ID

so that new records will pick up the ID from the main form (remember,
this
is the foreign key not a primary key in the second form).

Now you can either open it to a record, and if there is not, it will use
the
default:

DoCmd.OpenForm "Form2", , , "ID =" & Me.txtID

or straight to a New record:

DoCmd.OpenForm "Form2", , , , acFormAdd
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[NDClark]

When I open the 'Vehicle Info Form' I am inside a record. That record is
saved. I click the Add Maint Record button and it does open my Maint Record
Entry form.

I want the Maint Record Entry form to open new but be linked to the vehicle
record I am currently open on.

I have entered the code you said on the Maint Record Entry form under the as
an event procedure On Load and it is still opening to 'NEW' ID on the Vehicle
table. It is not linking to the vehicle on the main form.

First there must be a record in the main form to link to. That means you
need to create a record and save it. Save it in the first code line of the
open second form code. Do not make any other changes to controls in the main
form other than the code in the button to open the second form.

The open form VBA code should read like (using macros does not allow any
error handling, and will run slower, and I'm not even sure that you can do
this with a macro):

DoCmd.OpenForm "Maint Record Entry", , , "ID =" & Me.ID

Use the wizard to create the code from the button, then delete anything
about stLinkCriteria and the DoCmd line and replace it with the line above

The DefaultValue property of the ID textbox in "Maint Record Entry" is an
expression which should read:

= [Forms]![Vehicle Info]![ID]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I changed the where condition of the button to "Add New Maint Record" where
condition to [ID]=[Forms]![Vehicle Info]![Maint Record Entry]![ID] in the
On
Click Macro.

The Open Form action is Maint Record Entry, Form, , [ID]=[Forms]![Vehicle
Info]![Maint Record Entry]![ID], Add, Normal

On the Maint Record Entry form I put the Default value of the ID of the
Vehicle Info 'Main Form' as [ID] =[Forms]![Vehicle Info]![ID]

When I click the button to add a new record, The Maint Record Entry form
opens to NEW in both ID fields. It should be opening to the Vehicle I was
working on ID field. The two fields are ID field for the Maint Record
and
ID field for the Vehicle.

If I add a entry Access tells me there is no record for the Vehicle.

The problem is:
The "Add Maintenance form" is not linking the information with the
vehicle
I
am adding it to. In the ID field it has "####"

Make sure the width of the control is wide enough to display the entire
ID
field.. A separate form is not linked you need to use code.

First set the default value of the ID link field to:

= Forms!Form1_Name!ID

so that new records will pick up the ID from the main form (remember,
this
is the foreign key not a primary key in the second form).

Now you can either open it to a record, and if there is not, it will use
the
default:

DoCmd.OpenForm "Form2", , , "ID =" & Me.txtID

or straight to a New record:

DoCmd.OpenForm "Form2", , , , acFormAdd
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

I gave you and expression to be added to the DefaultValue PROPERTY not to an
event.

Secondly, if you have an autonumber for the value in the subtable, instead
of a long integer, you'll get a new value when you open the second form.
There is no possible way to generate a number out of thin air without an
autonumber field. Linking numbers are NEVER from a main table to another
Primary Key unless it is a one to one relationship. And then you still don't
use an autonumber. You have a one to many relationship and the second form
needs to attach with a link to the Foreign Key, not the Primary Key.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

When I open the 'Vehicle Info Form' I am inside a record. That record is
saved. I click the Add Maint Record button and it does open my Maint
Record
Entry form.

I want the Maint Record Entry form to open new but be linked to the
vehicle
record I am currently open on.

I have entered the code you said on the Maint Record Entry form under the
as
an event procedure On Load and it is still opening to 'NEW' ID on the
Vehicle
table. It is not linking to the vehicle on the main form.

First there must be a record in the main form to link to. That means you
need to create a record and save it. Save it in the first code line of
the
open second form code. Do not make any other changes to controls in the
main
form other than the code in the button to open the second form.

The open form VBA code should read like (using macros does not allow any
error handling, and will run slower, and I'm not even sure that you can
do
this with a macro):

DoCmd.OpenForm "Maint Record Entry", , , "ID =" & Me.ID

Use the wizard to create the code from the button, then delete anything
about stLinkCriteria and the DoCmd line and replace it with the line
above

The DefaultValue property of the ID textbox in "Maint Record Entry" is an
expression which should read:

= [Forms]![Vehicle Info]![ID]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I changed the where condition of the button to "Add New Maint Record"
where
condition to [ID]=[Forms]![Vehicle Info]![Maint Record Entry]![ID] in
the
On
Click Macro.

The Open Form action is Maint Record Entry, Form, ,
[ID]=[Forms]![Vehicle
Info]![Maint Record Entry]![ID], Add, Normal

On the Maint Record Entry form I put the Default value of the ID of the
Vehicle Info 'Main Form' as [ID] =[Forms]![Vehicle Info]![ID]

When I click the button to add a new record, The Maint Record Entry
form
opens to NEW in both ID fields. It should be opening to the Vehicle I
was
working on ID field. The two fields are ID field for the Maint Record
and
ID field for the Vehicle.

If I add a entry Access tells me there is no record for the Vehicle.



:



The problem is:
The "Add Maintenance form" is not linking the information with the
vehicle
I
am adding it to. In the ID field it has "####"

Make sure the width of the control is wide enough to display the
entire
ID
field.. A separate form is not linked you need to use code.

First set the default value of the ID link field to:

= Forms!Form1_Name!ID

so that new records will pick up the ID from the main form (remember,
this
is the foreign key not a primary key in the second form).

Now you can either open it to a record, and if there is not, it will
use
the
default:

DoCmd.OpenForm "Form2", , , "ID =" & Me.txtID

or straight to a New record:

DoCmd.OpenForm "Form2", , , , acFormAdd
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[NDClark]

You are now saying you gave me an expression to be added to the defaultValue
Property? You said in the post previous to this one to the open form VBA
code should read like this DoCmd.OpenForm "Maint Record Entry", , , "ID =" &
Me.ID

I gave you and expression to be added to the DefaultValue PROPERTY not to an
event.

Secondly, if you have an autonumber for the value in the subtable, instead
of a long integer, you'll get a new value when you open the second form.
There is no possible way to generate a number out of thin air without an
autonumber field. Linking numbers are NEVER from a main table to another
Primary Key unless it is a one to one relationship. And then you still don't
use an autonumber. You have a one to many relationship and the second form
needs to attach with a link to the Foreign Key, not the Primary Key.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

When I open the 'Vehicle Info Form' I am inside a record. That record is
saved. I click the Add Maint Record button and it does open my Maint
Record
Entry form.

I want the Maint Record Entry form to open new but be linked to the
vehicle
record I am currently open on.

I have entered the code you said on the Maint Record Entry form under the
as
an event procedure On Load and it is still opening to 'NEW' ID on the
Vehicle
table. It is not linking to the vehicle on the main form.

First there must be a record in the main form to link to. That means you
need to create a record and save it. Save it in the first code line of
the
open second form code. Do not make any other changes to controls in the
main
form other than the code in the button to open the second form.

The open form VBA code should read like (using macros does not allow any
error handling, and will run slower, and I'm not even sure that you can
do
this with a macro):

DoCmd.OpenForm "Maint Record Entry", , , "ID =" & Me.ID

Use the wizard to create the code from the button, then delete anything
about stLinkCriteria and the DoCmd line and replace it with the line
above

The DefaultValue property of the ID textbox in "Maint Record Entry" is an
expression which should read:

= [Forms]![Vehicle Info]![ID]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I changed the where condition of the button to "Add New Maint Record"
where
condition to [ID]=[Forms]![Vehicle Info]![Maint Record Entry]![ID] in
the
On
Click Macro.

The Open Form action is Maint Record Entry, Form, ,
[ID]=[Forms]![Vehicle
Info]![Maint Record Entry]![ID], Add, Normal

On the Maint Record Entry form I put the Default value of the ID of the
Vehicle Info 'Main Form' as [ID] =[Forms]![Vehicle Info]![ID]

When I click the button to add a new record, The Maint Record Entry
form
opens to NEW in both ID fields. It should be opening to the Vehicle I
was
working on ID field. The two fields are ID field for the Maint Record
and
ID field for the Vehicle.

If I add a entry Access tells me there is no record for the Vehicle.



:



The problem is:
The "Add Maintenance form" is not linking the information with the
vehicle
I
am adding it to. In the ID field it has "####"

Make sure the width of the control is wide enough to display the
entire
ID
field.. A separate form is not linked you need to use code.

First set the default value of the ID link field to:

= Forms!Form1_Name!ID

so that new records will pick up the ID from the main form (remember,
this
is the foreign key not a primary key in the second form).

Now you can either open it to a record, and if there is not, it will
use
the
default:

DoCmd.OpenForm "Form2", , , "ID =" & Me.txtID

or straight to a New record:

DoCmd.OpenForm "Form2", , , , acFormAdd
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

That VBA code was in the command button event that opens the popup form. The
expression in the DefaultValue property of the ID textbox is:

=[Forms]![Vehicle Info]![ID]

which tells the popup that if it's a new record, the ID value is = to the ID
on the main (Vehicle Info) form.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

You are now saying you gave me an expression to be added to the
defaultValue
Property? You said in the post previous to this one to the open form VBA
code should read like this DoCmd.OpenForm "Maint Record Entry", , , "ID ="
&
Me.ID

I gave you and expression to be added to the DefaultValue PROPERTY not to
an
event.

Secondly, if you have an autonumber for the value in the subtable,
instead
of a long integer, you'll get a new value when you open the second form.
There is no possible way to generate a number out of thin air without an
autonumber field. Linking numbers are NEVER from a main table to another
Primary Key unless it is a one to one relationship. And then you still
don't
use an autonumber. You have a one to many relationship and the second
form
needs to attach with a link to the Foreign Key, not the Primary Key.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

When I open the 'Vehicle Info Form' I am inside a record. That record
is
saved. I click the Add Maint Record button and it does open my Maint
Record
Entry form.

I want the Maint Record Entry form to open new but be linked to the
vehicle
record I am currently open on.

I have entered the code you said on the Maint Record Entry form under
the
as
an event procedure On Load and it is still opening to 'NEW' ID on the
Vehicle
table. It is not linking to the vehicle on the main form.



:

First there must be a record in the main form to link to. That means
you
need to create a record and save it. Save it in the first code line of
the
open second form code. Do not make any other changes to controls in
the
main
form other than the code in the button to open the second form.

The open form VBA code should read like (using macros does not allow
any
error handling, and will run slower, and I'm not even sure that you
can
do
this with a macro):

DoCmd.OpenForm "Maint Record Entry", , , "ID =" & Me.ID

Use the wizard to create the code from the button, then delete
anything
about stLinkCriteria and the DoCmd line and replace it with the line
above

The DefaultValue property of the ID textbox in "Maint Record Entry" is
an
expression which should read:

= [Forms]![Vehicle Info]![ID]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I changed the where condition of the button to "Add New Maint Record"
where
condition to [ID]=[Forms]![Vehicle Info]![Maint Record Entry]![ID]
in
the
On
Click Macro.

The Open Form action is Maint Record Entry, Form, ,
[ID]=[Forms]![Vehicle
Info]![Maint Record Entry]![ID], Add, Normal

On the Maint Record Entry form I put the Default value of the ID of
the
Vehicle Info 'Main Form' as [ID] =[Forms]![Vehicle Info]![ID]

When I click the button to add a new record, The Maint Record Entry
form
opens to NEW in both ID fields. It should be opening to the Vehicle
I
was
working on ID field. The two fields are ID field for the Maint
Record
and
ID field for the Vehicle.

If I add a entry Access tells me there is no record for the Vehicle.



:



The problem is:
The "Add Maintenance form" is not linking the information with
the
vehicle
I
am adding it to. In the ID field it has "####"

Make sure the width of the control is wide enough to display the
entire
ID
field.. A separate form is not linked you need to use code.

First set the default value of the ID link field to:

= Forms!Form1_Name!ID

so that new records will pick up the ID from the main form
(remember,
this
is the foreign key not a primary key in the second form).

Now you can either open it to a record, and if there is not, it
will
use
the
default:

DoCmd.OpenForm "Form2", , , "ID =" & Me.txtID

or straight to a New record:

DoCmd.OpenForm "Form2", , , , acFormAdd
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com