Transferring values from one form to another

[Charles Hatfield]

I want to be able to enter data on form1 that is bound to one table and
then click on a button to open form2 that is bound to another table. I
want to be able to
carry the value entered in field "FormNum" to the same field on form2.

I linked the two tables on the FormNum field.

I also reviewed the FAQ on Tech-Tips and found the following code from a
similar request and modified it accordingly:


Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form2"

stLinkCriteria = "[FormNum]=" & Me![FormNum]
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click

End Sub

When I enter data on Form1 and click the button, Form2 opens but
without the "FormNum" value that was entered into the FormNum field on
Form1.

What I don't understand is that the stLinkCriteria is equaled to the
value entered on Form1 but doesn't appear in the FormNum field on Form2.

What am I doing wrong?.

 

[John Vinson]

What I don't understand is that the stLinkCriteria is equaled to the
value entered on Form1 but doesn't appear in the FormNum field on Form2.

These are two separate operations. The stLinkCriteria simply filters
Form2 to display only those records which match that criterion (none,
if it's a new value).

In order to put that value into FormNum, you must have code to
explicitly put that value into FormNum. I'd suggest the following:

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String

stDocName = "Form2"

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, OpenArgs := Me![FormNum]

Exit_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click

End Sub

This will pass the value in the form control in the OpenArgs parameter
to the new form; in Form2's Open event you can put code like

Private Sub Form_Open(Cancel as Integer)
If Me.OpenArgs & "" <> "" Then
Me!FormNum = Me.OpenArgs
End If
End Sub

 

[George Nicholson]

What exactly are you trying to do with Form 2? Filter it to show all
existing records with the value of Form1.FormNum, or Add a new record that
uses Form1.FormNum for a default value?

The OpenForm method has 2 arguments that can help you in either case, but
they do entirely different things. Currently you are using the
WhereCondition argument which sets the filter of Form 2 to show all records
with the value of Form1.FormNum. Since nothing is displaying and that isn't
what you expect, I assume you actually want to add records, not display
existing ones.

If that is the case, you want to use the "OpenArgs" argument of OpenForm.
This allows you to pass a value to Form2:
DoCmd.OpenForm stDocName, , , , , , Me![FormNum] (OpenArgs is the 7th &
last argument)

That will set the OpenArgs property of Form2. Then, in the Open event of
Form2:

Private Sub Form_Open(Cancel As Integer)
Me.Newrecord
Me![FormNum] = Me.OpenArgs
End sub

Hope this helps,

 

[Charles Hatfield]

John

It worked.
Thanks.

Charles

What I don't understand is that the stLinkCriteria is equaled to the
value entered on Form1 but doesn't appear in the FormNum field on Form2.

These are two separate operations. The stLinkCriteria simply filters
Form2 to display only those records which match that criterion (none,
if it's a new value).

In order to put that value into FormNum, you must have code to
explicitly put that value into FormNum. I'd suggest the following:

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String

stDocName = "Form2"

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, OpenArgs := Me![FormNum]

Exit_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click

End Sub

This will pass the value in the form control in the OpenArgs parameter
to the new form; in Form2's Open event you can put code like

Private Sub Form_Open(Cancel as Integer)
If Me.OpenArgs & "" <> "" Then
Me!FormNum = Me.OpenArgs
End If
End Sub

 

[Charles Hatfield]

George,

It does help!

Thanks

Charles

What exactly are you trying to do with Form 2? Filter it to show all
existing records with the value of Form1.FormNum, or Add a new record that
uses Form1.FormNum for a default value?

The OpenForm method has 2 arguments that can help you in either case, but
they do entirely different things. Currently you are using the
WhereCondition argument which sets the filter of Form 2 to show all records
with the value of Form1.FormNum. Since nothing is displaying and that isn't
what you expect, I assume you actually want to add records, not display
existing ones.

If that is the case, you want to use the "OpenArgs" argument of OpenForm.
This allows you to pass a value to Form2:
DoCmd.OpenForm stDocName, , , , , , Me![FormNum] (OpenArgs is the 7th &
last argument)

That will set the OpenArgs property of Form2. Then, in the Open event of
Form2:

Private Sub Form_Open(Cancel As Integer)
Me.Newrecord
Me![FormNum] = Me.OpenArgs
End sub

Hope this helps,

--
George Nicholson

Remove 'Junk' from return address.

I want to be able to enter data on form1 that is bound to one table and
then click on a button to open form2 that is bound to another table. I
want to be able to
carry the value entered in field "FormNum" to the same field on form2.

I linked the two tables on the FormNum field.

I also reviewed the FAQ on Tech-Tips and found the following code from a
similar request and modified it accordingly:


Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form2"

stLinkCriteria = "[FormNum]=" & Me![FormNum]
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpen_Click:
Exit Sub

Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click

End Sub

When I enter data on Form1 and click the button, Form2 opens but
without the "FormNum" value that was entered into the FormNum field on
Form1.

What I don't understand is that the stLinkCriteria is equaled to the
value entered on Form1 but doesn't appear in the FormNum field on Form2.

What am I doing wrong?.