How to pass multiple Fields to new Form

[dohernan]

When a person chooses “Verification†from a dropdown list, either the current
record has a SSN that is already in the Personnel table, which opens the
“VerifLMini†form, or the SSN is not found and a “VerifLetterInfo†form opens
up, ready to be filled with an address and whatever comments, which is then
saved to an Address Table.

When the VerifLmini form pops up it has the address from the address table
already filled in, and the SSN and name from the Personnel table filled in as
well. The person can look at the info and “create my report/letter†or they
can decide they need to edit/add more info.

When they decide to edit/add more is when I have issues. I have a button to
open the VerifLetterInfo form from the mini form, but I’m not sure how to
pass the current record information to it, so the person won’t have to type
everything in.

The Address table has an Auto# Field called AddressRecord
The Personnel table has an Auto# Field called Record


The current OpenArgs to the VerifLetterInfo form-

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.SSN = Me.OpenArgs
End If
End Sub

++++++++++++++++++++++++++++++++++

Private Sub FormType_AfterUpdate()

If Me.[FormType] = "Verification" Then
If Me.Dirty Then Me.Dirty = False
If IsNull(DLookup("SSN", "VerificationLetterInformation", "SSN = '" &
Me.SSN & "'")) Then
' The SSN does not exist in the second table.
' Open form VerifLetterInfo so that they can add the necessary information.
DoCmd.OpenForm "VerifLetterInfo", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=Me.SSN
Else
' The SSN exists in the second table.
' Open Form VerifLMini in case they want to change the information.
DoCmd.OpenForm "VerifLMini", WindowMode:=acDialog, _
WhereCondition:="SSN = '" & Me.SSN & "'"
End If
End If

End Sub

+++++++++++++++++++++++++++++++++

What isn’t working in the VerifMini form-

Private Sub Add_Edit_Letter_Info_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "VerifLetterInfo"
DoCmd.OpenForm stDocName, , , stLinkCriteria, _
"[AddressRecord] = " & Me.AddressRecord

End Sub

Also tried-

DoCmd.OpenForm "VerifLetterInfo", WindowMode:=acDialog, _
WhereCondition:="SSN = '" & Me.SSN & "'"


Thanks.

 

[Tom van Stiphout]

On Wed, 2 Dec 2009 12:07:01 -0800, dohernan

If I understand correctly you want to know how to display multiple
values from form1 in form2.
There are two basic strategies:
1: Pass in everything via OpenArgs.
2: "Look back" on the first form and pick up the values.

Re 1:
OpenArgs is a string of arbitrary length. You can concatenate several
values, separated by some separator. For example
strOpenArgs = Me.CustomerID & "," & Me.SalesrepID
DoCmd.OpenForm OpenArgs:=strOpenArgs 'etc.
Then in the receiving form you pick OpenArgs apart, perhaps using the
Split function:
dim args() as string
args = Split(Me.OpenArgs, ",")
Debug.Print "CustomerID=" & args(0) & ", SalesrepID=" & args(1)

Personally I always use an approach very similar to a querystring in
the browser: it uses name/value pairs so it makes it self-describing:
strOpenArgs = "CustomerID=" & Me.CustomerID & "&SalesRepID=" &
Me.SalesrepID
Then in the receiving form I convert this to a Scripting.Dictionary
object. I leave that for you to consider. The benefit is that with the
dictionary object I have a rich object I can do all kinds of things
with. For example I can easily test if a CustomerID key value was
given in OpenArgs, and if it was omitted, I display all customers.

Re 2:
Forget about OpenArgs, and as long as form1 is still open you can
write in form2's Form_Load event:
Me.CustomerID = Forms!form1!CustomerID
Me.SalesrepID = Forms!form1.SalesrepID
'etc.

-Tom.
Microsoft Access MVP

When a person chooses “Verification” from a dropdown list, either the current
record has a SSN that is already in the Personnel table, which opens the
“VerifLMini” form, or the SSN is not found and a “VerifLetterInfo” form opens
up, ready to be filled with an address and whatever comments, which is then
saved to an Address Table.

When the VerifLmini form pops up it has the address from the address table
already filled in, and the SSN and name from the Personnel table filled in as
well. The person can look at the info and “create my report/letter” or they
can decide they need to edit/add more info.

When they decide to edit/add more is when I have issues. I have a button to
open the VerifLetterInfo form from the mini form, but I’m not sure how to
pass the current record information to it, so the person won’t have to type
everything in.

The Address table has an Auto# Field called AddressRecord
The Personnel table has an Auto# Field called Record


The current OpenArgs to the VerifLetterInfo form-

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.SSN = Me.OpenArgs
End If
End Sub

++++++++++++++++++++++++++++++++++

Private Sub FormType_AfterUpdate()

If Me.[FormType] = "Verification" Then
If Me.Dirty Then Me.Dirty = False
If IsNull(DLookup("SSN", "VerificationLetterInformation", "SSN = '" &
Me.SSN & "'")) Then
' The SSN does not exist in the second table.
' Open form VerifLetterInfo so that they can add the necessary information.
DoCmd.OpenForm "VerifLetterInfo", DataMode:=acFormAdd, _
WindowMode:=acDialog, OpenArgs:=Me.SSN
Else
' The SSN exists in the second table.
' Open Form VerifLMini in case they want to change the information.
DoCmd.OpenForm "VerifLMini", WindowMode:=acDialog, _
WhereCondition:="SSN = '" & Me.SSN & "'"
End If
End If

End Sub

+++++++++++++++++++++++++++++++++

What isn’t working in the VerifMini form-

Private Sub Add_Edit_Letter_Info_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "VerifLetterInfo"
DoCmd.OpenForm stDocName, , , stLinkCriteria, _
"[AddressRecord] = " & Me.AddressRecord

End Sub

Also tried-

DoCmd.OpenForm "VerifLetterInfo", WindowMode:=acDialog, _
WhereCondition:="SSN = '" & Me.SSN & "'"


Thanks.

 

[dohernan]

Thanks, I'll try

Me.Salary = Forms!VerifLmini!Salary etc.

The thing is if the SSN is found in the Address table and the VerifLmini
form is opened, all of the Fields are automatically filled in, so I can't
understand why when a person decides to add/edit the fields and clicks to
open up the VerifiLetterInfo form from that mini form, that it doesn't
automatically fill the Fields in as they all have the same Control Sources,
just the issue is letting it know what AddressRecord # we're looking at.