How to pass multiple Fields to another Form?

[dohernan via AccessMonster.com]

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 & "'"

 

[Daryl S]

Dohernan -

When you open the form to allow updates, pass in the key value to the
record. If you re-create the button that allows the user to edit the
information, then the button wizard will ask which record the form should
open - you should identify the primary key value of the current record.

 

[dohernan via AccessMonster.com]

Thanks, i tried the button Wizard, it gave the code below, but the
verifletterinfo form still opened blank?

Private Sub Add_Edit_Letter_Click()
On Error GoTo Err_Add_Edit_Letter_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "VerifLetterInfo"

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

Exit_Add_Edit_Letter_Click:
Exit Sub

Err_Add_Edit_Letter_Click:
MsgBox Err.Description
Resume Exit_Add_Edit_Letter_Click

End Sub

 

[Daryl S]

Is the SSN the single primary key to the table or query the update form is
based on? In the records you are testing, did you make sure that a matching
record is there? Are there differences in the format of the SSN on the form
and in the table?

 

[dohernan via AccessMonster.com]

SSN is the only key, and I just changed the masking to match, but while the
mini form picks up the info, when I try to open the next form with the info
it doesn't work.

However, if the SSN isn't in the AddressTable, the Original Input form passes
the SSN in OpenArgs to the VerifLetterInfo form with no issues.

I'm thinking of cutting down to just one form, the Verifletterinfo one, which
leaves me unsure of how to fix the lines below-



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

Is the SSN the single primary key to the table or query the update form is
based on? In the records you are testing, did you make sure that a matching
record is there? Are there differences in the format of the SSN on the form
and in the table?

Thanks, i tried the button Wizard, it gave the code below, but the
verifletterinfo form still opened blank?

[quoted text clipped - 18 lines]

End Sub

 

[Daryl S]

Dohernan -

It sounds like the first OpenForm is working (with OpenArgs), but the second
one is not (with the Where condition). Have you stepped through the code to
see what is happening? You may want to try brackets around your field name,
like this:

WhereCondition:="[SSN] = '" & Me.SSN & "'"

Is the form opening, but no records show? Stepping through the code may show
why.

 

[dohernan via AccessMonster.com]

Thanks, the form does open.
The Where condition works with the mini form. But when the person decides
they want to edit and open up the regular verifletter form, the info
disappears.

So I have decided to get rid of the mini form and just open the
VerifletterInfo form with whatever info is available.

But if the SSN is already in the database, the verifletterinfo form opens up
with nothing in the fields, not even the SSN.

The form is using the fields from a query I did that ties the address etc.
table to the names table.
The issue may be that the SSNs show up more than once in the Query, since the
same person may order multiple forms on different dates.

I do Debug/Step Into but I don't see anything?


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 "VerifLetterInfo", WindowMode:=acDialog, _
WhereCondition:="[SSN] = '" & Me.SSN & "'"
End If
End If

End Sub

Dohernan -

It sounds like the first OpenForm is working (with OpenArgs), but the second
one is not (with the Where condition). Have you stepped through the code to
see what is happening? You may want to try brackets around your field name,
like this:

WhereCondition:="[SSN] = '" & Me.SSN & "'"

Is the form opening, but no records show? Stepping through the code may show
why.

SSN is the only key, and I just changed the masking to match, but while the
mini form picks up the info, when I try to open the next form with the info

[quoted text clipped - 25 lines]

End Sub

 

[Daryl S]

When you say the SSNs may show up more than once in the query, does that mean
on different records? If so, the form should open with multiple records.
If you have the SSN appearing in more than one column (e.g. coming from more
than one table in your query), then your criteria may not be correct. Look
at the recordsource for the verifletterinfo form and see if that could be the
issue. In the WHERE Condition, the field name should match the recordsource
fieldname.

While you are looking at the recordsource, you can bring it up in design
mode. If you can enter the SSN there and switch to datasheet mode, do you
get the record(s) you expect to see on the form? If not, check the query or
the data in the tables to make sure there isn't an issue there.

Let us know how it goes...