How do I link a form with another form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I link one form with another form using the primary key and open the
second form with a command button (is this possible)? For example, I would
like to link the main form using the primary key "Item Number" with another
form that contains the primary key "Item Number" all the infomation that is
recorded is found under one table. Therefore if I go to the main form under
"Item Number 2" and click on the command button, another form will pop up
with all other relevant details for "Item Number 2" etc.
The only way I found this to work is if I created a sub form in the main form.
I've tried, creating seperate tables, relationships, macros etc... none of
them seems to be working.

HELP!!
 
Tiffany,
When you open Form2, use the value of ItemNumber on Form1 to filter
Form2.
The OpenForm method has a "where condition" argument that you can use to
do that.
Use the Click event of your Form1 button, and use this code...
DoCmd.OpenForm "frmForm1",,,"ItemNumber = Forms!frmForm1!ItemNumber"
hth
Al Camp
 
Hi Al,

Thanks for that information, but you've completely loss me. Maybe if i
describe the forms it will help me have a better understanding.

Form 1: called "List" primary key is "Item Number" (this is auto numbering)
Form 2: called 'Invoice" primary key is "Item Number"
Form 1 "List" has a command button to open "Invoice"
I've created a marco that opens the "Invoice form" with the "where
condition" set on "
  • ![Item Number]" . Am I on the right track so far?

    The only problem is, if i go to List and Item Number 3 and click on the
    command button, the "invoice" form doesn't open up to Item Number 3, it goes
    back to the first record in the list.

    Tiff
 
Tiffany,
Your using Macros for your coding. No problem....
Your OpenForm macro Where should read...
[ItemNumber] = [Forms]!
  • ![ItemNumber]

    That says... The ItemNumber on Invoice equals the ItemNumber value on List.
    hth
    Al Camp

    Tiffany said:
    Hi Al,

    Thanks for that information, but you've completely loss me. Maybe if i
    describe the forms it will help me have a better understanding.

    Form 1: called "List" primary key is "Item Number" (this is auto
    numbering)
    Form 2: called 'Invoice" primary key is "Item Number"
    Form 1 "List" has a command button to open "Invoice"
    I've created a marco that opens the "Invoice form" with the "where
    condition" set on "
    • ![Item Number]" . Am I on the right track so far?

      The only problem is, if i go to List and Item Number 3 and click on the
      command button, the "invoice" form doesn't open up to Item Number 3, it
      goes
      back to the first record in the list.

      Tiff



      Al Camp said:
      Tiffany,
      When you open Form2, use the value of ItemNumber on Form1 to filter
      Form2.
      The OpenForm method has a "where condition" argument that you can use
      to
      do that.
      Use the Click event of your Form1 button, and use this code...
      DoCmd.OpenForm "frmForm1",,,"ItemNumber = Forms!frmForm1!ItemNumber"
      hth
      Al Camp
      Click to expand...
    Click to expand...
 
I just did this myself and it seems to work

After placing your command button, the wizard should appear, if no
place it again making sure you have the Control Wizard butto
selected. Once you're in the Command Button Wizard, select th
following

Form Operations, Open for
Next screen - Choose the form you want to link T
Next screen - select "Open form and find specific data
Next screen - select the field in each form that you want to lin
Next screens - it'll ask you to choose formatting options on you
command butto

So far, I've had no problems - when I click the command button in th
first form, it takes me straight to the same record in the secon
form

Good Luck
 
I know this is a old post but I'm having problems linking a pop-up for to my
main form using A2K. I have an option buttom behind which is this code on
click:

Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName] 'opens the form to update Special Handling
End Sub

The form pops up but is not linked to the main form table.
The pop-up form record source is a table SpecialHandling which I would like
to update. The main form record source is a table Customers. I am trying to
link on CustomerName, the primary key for both files. Do I need to add an
invisible subform on main form in order to link the tables?
I also tried using a command button as this post suggests and nothing
happens at all. The wizard created this code:

Private Sub Special_Handling_Click()
On Error GoTo Err_Special_Handling_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSpecialHandling"

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

Exit_Special_Handling_Click:
Exit Sub

Err_Special_Handling_Click:
MsgBox Err.Description
Resume Exit_Special_Handling_Click

End Sub

What do I need to do next and what is the best approach?
 
Jim,

THis method,

I think what you are trying to do is sync the popup record to the main form,
as if it were a subform. To do this, you need to set the value of the field
which links the tables. You can pass the value through the OpenArgs
property. So if the linking fields were named, [ID] the method to open the
form would be,

DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName] = &
Me![CustomerName]", , , [ID]

The value of [ID] will be passed to the popup like a variable. On the
popup's Open or Load event, set the value of [ID] on the form, like

[ID] = OpenArgs


God Bless,

Mark A. Sam



JIM said:
I know this is a old post but I'm having problems linking a pop-up for to
my
main form using A2K. I have an option buttom behind which is this code on
click:

Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName] 'opens the form to update Special Handling
End Sub

The form pops up but is not linked to the main form table.
The pop-up form record source is a table SpecialHandling which I would
like
to update. The main form record source is a table Customers. I am trying
to
link on CustomerName, the primary key for both files. Do I need to add an
invisible subform on main form in order to link the tables?
I also tried using a command button as this post suggests and nothing
happens at all. The wizard created this code:

Private Sub Special_Handling_Click()
On Error GoTo Err_Special_Handling_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSpecialHandling"

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

Exit_Special_Handling_Click:
Exit Sub

Err_Special_Handling_Click:
MsgBox Err.Description
Resume Exit_Special_Handling_Click

End Sub

What do I need to do next and what is the best approach?



Tiffany said:
Thank you for all your help.
The forms are now linking with each other :)
Click to expand...
Click to expand...
 
Thanks Mark for your help. My subroutine looks like this:
Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName], , , [CustomerName] 'opens the form to update or add
Special Handling
End Sub

and I assigned [CustomerName]=OpenArgs on the load event of
frmSpecialHandling.

My form now pops up when option button is pressed but does not find the
underlying entry connected to frmSpecialHandling. If an entry doesn't exist
for the primary key that is on the main form then an entry is correctly
created. But if a table entry already exists the pop-up form just tries to
create a new table entry, which is not desired, and when I try to close form
it gives me the message, "The changes you requested to the table were not
successful because they would create duplicate values in the index...."
CustomerName is the primary index in both the main form and the pop-up form.
Any ideas on how to access the existing table entries?
JIM

Mark A. Sam said:
Jim,

THis method,

I think what you are trying to do is sync the popup record to the main form,
as if it were a subform. To do this, you need to set the value of the field
which links the tables. You can pass the value through the OpenArgs
property. So if the linking fields were named, [ID] the method to open the
form would be,

DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName] = &
Me![CustomerName]", , , [ID]

The value of [ID] will be passed to the popup like a variable. On the
popup's Open or Load event, set the value of [ID] on the form, like

[ID] = OpenArgs


God Bless,

Mark A. Sam



JIM said:
I know this is a old post but I'm having problems linking a pop-up for to
my
main form using A2K. I have an option buttom behind which is this code on
click:

Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName] 'opens the form to update Special Handling
End Sub

The form pops up but is not linked to the main form table.
The pop-up form record source is a table SpecialHandling which I would
like
to update. The main form record source is a table Customers. I am trying
to
link on CustomerName, the primary key for both files. Do I need to add an
invisible subform on main form in order to link the tables?
I also tried using a command button as this post suggests and nothing
happens at all. The wizard created this code:

Private Sub Special_Handling_Click()
On Error GoTo Err_Special_Handling_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSpecialHandling"

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

Exit_Special_Handling_Click:
Exit Sub

Err_Special_Handling_Click:
MsgBox Err.Description
Resume Exit_Special_Handling_Click

End Sub

What do I need to do next and what is the best approach?



Tiffany said:
Thank you for all your help.
The forms are now linking with each other :)
Click to expand...
Click to expand...
Click to expand...
 
I need to think this though and can't address it at the moment. I think I
misunderstood what you wanted. Maybe someone else will answer in the
meantime.


JIM said:
Thanks Mark for your help. My subroutine looks like this:
Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName], , , [CustomerName] 'opens the form to update or add
Special Handling
End Sub

and I assigned [CustomerName]=OpenArgs on the load event of
frmSpecialHandling.

My form now pops up when option button is pressed but does not find the
underlying entry connected to frmSpecialHandling. If an entry doesn't
exist
for the primary key that is on the main form then an entry is correctly
created. But if a table entry already exists the pop-up form just tries
to
create a new table entry, which is not desired, and when I try to close
form
it gives me the message, "The changes you requested to the table were not
successful because they would create duplicate values in the index...."
CustomerName is the primary index in both the main form and the pop-up
form.
Any ideas on how to access the existing table entries?
JIM

Mark A. Sam said:
Jim,

THis method,

I think what you are trying to do is sync the popup record to the main
form,
as if it were a subform. To do this, you need to set the value of the
field
which links the tables. You can pass the value through the OpenArgs
property. So if the linking fields were named, [ID] the method to open
the
form would be,

DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName] = &
Me![CustomerName]", , , [ID]

The value of [ID] will be passed to the popup like a variable. On the
popup's Open or Load event, set the value of [ID] on the form, like

[ID] = OpenArgs


God Bless,

Mark A. Sam



JIM said:
I know this is a old post but I'm having problems linking a pop-up for
to
my
main form using A2K. I have an option buttom behind which is this code
on
click:

Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName] 'opens the form to update Special Handling
End Sub

The form pops up but is not linked to the main form table.
The pop-up form record source is a table SpecialHandling which I would
like
to update. The main form record source is a table Customers. I am
trying
to
link on CustomerName, the primary key for both files. Do I need to add
an
invisible subform on main form in order to link the tables?
I also tried using a command button as this post suggests and nothing
happens at all. The wizard created this code:

Private Sub Special_Handling_Click()
On Error GoTo Err_Special_Handling_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSpecialHandling"

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

Exit_Special_Handling_Click:
Exit Sub

Err_Special_Handling_Click:
MsgBox Err.Description
Resume Exit_Special_Handling_Click

End Sub

What do I need to do next and what is the best approach?



:

Thank you for all your help.
The forms are now linking with each other :)
Click to expand...
Click to expand...
Click to expand...
 
Jim.

Does the field [CustomerName] exist in the SpecialHandling table? If so,
how is it populated, with a combobox?

God Bless,

Mark

JIM said:
Thanks Mark for your help. My subroutine looks like this:
Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName], , , [CustomerName] 'opens the form to update or add
Special Handling
End Sub

and I assigned [CustomerName]=OpenArgs on the load event of
frmSpecialHandling.

My form now pops up when option button is pressed but does not find the
underlying entry connected to frmSpecialHandling. If an entry doesn't
exist
for the primary key that is on the main form then an entry is correctly
created. But if a table entry already exists the pop-up form just tries
to
create a new table entry, which is not desired, and when I try to close
form
it gives me the message, "The changes you requested to the table were not
successful because they would create duplicate values in the index...."
CustomerName is the primary index in both the main form and the pop-up
form.
Any ideas on how to access the existing table entries?
JIM

Mark A. Sam said:
Jim,

THis method,

I think what you are trying to do is sync the popup record to the main
form,
as if it were a subform. To do this, you need to set the value of the
field
which links the tables. You can pass the value through the OpenArgs
property. So if the linking fields were named, [ID] the method to open
the
form would be,

DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName] = &
Me![CustomerName]", , , [ID]

The value of [ID] will be passed to the popup like a variable. On the
popup's Open or Load event, set the value of [ID] on the form, like

[ID] = OpenArgs


God Bless,

Mark A. Sam



JIM said:
I know this is a old post but I'm having problems linking a pop-up for
to
my
main form using A2K. I have an option buttom behind which is this code
on
click:

Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName] 'opens the form to update Special Handling
End Sub

The form pops up but is not linked to the main form table.
The pop-up form record source is a table SpecialHandling which I would
like
to update. The main form record source is a table Customers. I am
trying
to
link on CustomerName, the primary key for both files. Do I need to add
an
invisible subform on main form in order to link the tables?
I also tried using a command button as this post suggests and nothing
happens at all. The wizard created this code:

Private Sub Special_Handling_Click()
On Error GoTo Err_Special_Handling_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSpecialHandling"

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

Exit_Special_Handling_Click:
Exit Sub

Err_Special_Handling_Click:
MsgBox Err.Description
Resume Exit_Special_Handling_Click

End Sub

What do I need to do next and what is the best approach?



:

Thank you for all your help.
The forms are now linking with each other :)
Click to expand...
Click to expand...
Click to expand...
 
Jim,

I think I am understanding this now. Instead of assiging,
[CustomerName]=OpenArgs on the Load event set the DefaultValue property of
the [CustomerName] field in frmSpecialHandling to [OpenArgs]. I think that
will do what you need. Also by having [CustomerName] in SpecialHandling
table as a unique index, you are creating a One to One relationship between
the Customers and SpecialHandling tables. Is that your intention?

God Bless,

Mark


JIM said:
Thanks Mark for your help. My subroutine looks like this:
Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName], , , [CustomerName] 'opens the form to update or add
Special Handling
End Sub

and I assigned [CustomerName]=OpenArgs on the load event of
frmSpecialHandling.

My form now pops up when option button is pressed but does not find the
underlying entry connected to frmSpecialHandling. If an entry doesn't
exist
for the primary key that is on the main form then an entry is correctly
created. But if a table entry already exists the pop-up form just tries
to
create a new table entry, which is not desired, and when I try to close
form
it gives me the message, "The changes you requested to the table were not
successful because they would create duplicate values in the index...."
CustomerName is the primary index in both the main form and the pop-up
form.
Any ideas on how to access the existing table entries?
JIM

Mark A. Sam said:
Jim,

THis method,

I think what you are trying to do is sync the popup record to the main
form,
as if it were a subform. To do this, you need to set the value of the
field
which links the tables. You can pass the value through the OpenArgs
property. So if the linking fields were named, [ID] the method to open
the
form would be,

DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName] = &
Me![CustomerName]", , , [ID]

The value of [ID] will be passed to the popup like a variable. On the
popup's Open or Load event, set the value of [ID] on the form, like

[ID] = OpenArgs


God Bless,

Mark A. Sam



JIM said:
I know this is a old post but I'm having problems linking a pop-up for
to
my
main form using A2K. I have an option buttom behind which is this code
on
click:

Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName] 'opens the form to update Special Handling
End Sub

The form pops up but is not linked to the main form table.
The pop-up form record source is a table SpecialHandling which I would
like
to update. The main form record source is a table Customers. I am
trying
to
link on CustomerName, the primary key for both files. Do I need to add
an
invisible subform on main form in order to link the tables?
I also tried using a command button as this post suggests and nothing
happens at all. The wizard created this code:

Private Sub Special_Handling_Click()
On Error GoTo Err_Special_Handling_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSpecialHandling"

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

Exit_Special_Handling_Click:
Exit Sub

Err_Special_Handling_Click:
MsgBox Err.Description
Resume Exit_Special_Handling_Click

End Sub

What do I need to do next and what is the best approach?



:

Thank you for all your help.
The forms are now linking with each other :)
Click to expand...
Click to expand...
Click to expand...
 
Thanks. I think I found the problem. My subroutine works perfect and looks
like this:

Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName] ='" &
Me![CustomerName] & " '", , , [CustomerName] 'opens the form to update or add
Special Handling
End Sub

The problem was syntax only. Thanks for putting me on the right track. May
God bless you too. JIM

Mark A. Sam said:
Jim,

I think I am understanding this now. Instead of assiging,
[CustomerName]=OpenArgs on the Load event set the DefaultValue property of
the [CustomerName] field in frmSpecialHandling to [OpenArgs]. I think that
will do what you need. Also by having [CustomerName] in SpecialHandling
table as a unique index, you are creating a One to One relationship between
the Customers and SpecialHandling tables. Is that your intention?

God Bless,

Mark


JIM said:
Thanks Mark for your help. My subroutine looks like this:
Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName], , , [CustomerName] 'opens the form to update or add
Special Handling
End Sub

and I assigned [CustomerName]=OpenArgs on the load event of
frmSpecialHandling.

My form now pops up when option button is pressed but does not find the
underlying entry connected to frmSpecialHandling. If an entry doesn't
exist
for the primary key that is on the main form then an entry is correctly
created. But if a table entry already exists the pop-up form just tries
to
create a new table entry, which is not desired, and when I try to close
form
it gives me the message, "The changes you requested to the table were not
successful because they would create duplicate values in the index...."
CustomerName is the primary index in both the main form and the pop-up
form.
Any ideas on how to access the existing table entries?
JIM

Mark A. Sam said:
Jim,

THis method,

I think what you are trying to do is sync the popup record to the main
form,
as if it were a subform. To do this, you need to set the value of the
field
which links the tables. You can pass the value through the OpenArgs
property. So if the linking fields were named, [ID] the method to open
the
form would be,

DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName] = &
Me![CustomerName]", , , [ID]

The value of [ID] will be passed to the popup like a variable. On the
popup's Open or Load event, set the value of [ID] on the form, like

[ID] = OpenArgs


God Bless,

Mark A. Sam



I know this is a old post but I'm having problems linking a pop-up for
to
my
main form using A2K. I have an option buttom behind which is this code
on
click:

Private Sub Option87_Click()
DoCmd.OpenForm "frmSpecialHandling", , , "[CustomerName]" =
Me![CustomerName] 'opens the form to update Special Handling
End Sub

The form pops up but is not linked to the main form table.
The pop-up form record source is a table SpecialHandling which I would
like
to update. The main form record source is a table Customers. I am
trying
to
link on CustomerName, the primary key for both files. Do I need to add
an
invisible subform on main form in order to link the tables?
I also tried using a command button as this post suggests and nothing
happens at all. The wizard created this code:

Private Sub Special_Handling_Click()
On Error GoTo Err_Special_Handling_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSpecialHandling"

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

Exit_Special_Handling_Click:
Exit Sub

Err_Special_Handling_Click:
MsgBox Err.Description
Resume Exit_Special_Handling_Click

End Sub

What do I need to do next and what is the best approach?



:

Thank you for all your help.
The forms are now linking with each other :)
Click to expand...
Click to expand...
Click to expand...
 
Not helpful - after choosing the form linked to, the other command choices do
not show up.
______________________________________________________________
 
I am trying to use the Command Button Wizard.
I want a button that will take me to another form that has additional
customer information and I want the customer name to show up in the second
form (the customer name is not included in the underlying table but they are
tied together with the primary key.

When I get to the part in the wizard where it asks me to identify which
fields contain matching data, no fields show up at all for the main form (the
one on the left). Why would this be?
 
Back
Top