open args 3 fields and strCriteria

[deb]

Access 2003
I have a form called fPMAddEdit(PK PMID and fields PMLName, PMFName, Title.
This form is to enter new Project Managers.
There is a second form called fEmailAddEdit(fields RecipientLastName,
RecipientFirstName and RecipientTitle. This form is to enter email addresses.

Most Project Managers will have an email associated.
However there may be other email addresses for people that are not in the
fPMAddEdit form.

In the form fPMAddEdit I have a btnEmail that opens the fEmailAddEdit form.
If there is a record associated with PMID in fEmailAddEdit then display
data. - PMLName=RecipientLastName, PMFName=RecipientFirstName ,
Title=RecipientTitle. (In case there are edits to the 3 fields)
If there is not a record already associated with the PMID then fill in the 3
fields...PMLName=RecipientLastName, PMFName=RecipientFirstName ,
Title=RecipientTitle.

The below code works but it only send over one field(fPMAddEdit.PMLName) to
fEmailAddEdit.RecipientLastName).
How can I edit the code to send all 3 fields?

Private Sub btnEmail_Click()
On Error GoTo Err_btnEmail_Click

If Me.Dirty = True Then
Me.Dirty = False
End If

Dim strOpenArgs As String
Dim strCriteria As String
Dim strFormName As String
On Error GoTo Proc_Error
strFormName = "fEmailAddEdit"
strCriteria = "[PMID] = " & Me![PMID]
strOpenArgs = Me![PMLName]
DoCmd.OpenForm strFormName, WhereCondition:=strCriteria, OpenArgs:=strOpenArgs
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in btnEmail_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit

End Sub


Private Sub Form_Open(Cancel As Integer)
Const Quote As String = """"
If Me.OpenArgs & "" <> "" Then
Me![RecipientLastName].DefaultValue = Quote & Me.OpenArgs & Quote
End If

 

[John Spencer]

Since you don't close the calling form you should be able to reference the
values of the controls on it. One way.

OpenArgs:="fPMAddEdit"

Private Sub Form_Open(Cancel As Integer)
Const Quote As String = """"
If Me.OpenArgs & "" = "FPmAddEdit" Then

Me.[RecipientLastName].DefaultValue = Quote & Forms!fPMAddEdit!PMLName & Quote
Me.[RecipientFirstName].DefaultValue = Quote & Forms!fPMAddEdit!PMFName & Quote
Me.[Title].DefaultValue = Quote & Forms!fPMAddEdit!RecipientTitle & Quote
End If

Another way would be to pass all three values in a delimited string
strOpenArgs = Me![PMLName] &"//" & Me.PMFname & "//" & Me.RecipientTitle

Private Sub Form_Open(Cancel As Integer)
Dim sArray as Variant
Const Quote As String = """"
If Me.OpenArgs & "" <> "" Then
Set sArray = Split(me.OpenArgs,"//")
Me![RecipientLastName].DefaultValue = Quote & sArray(0) & Quote
Me.[RecipientFirstName].DefaultValue = Quote & sArray(1) & Quote
Me.[Title].DefaultValue = sArray(2)
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Access 2003
I have a form called fPMAddEdit(PK PMID and fields PMLName, PMFName, Title.
This form is to enter new Project Managers.
There is a second form called fEmailAddEdit(fields RecipientLastName,
RecipientFirstName and RecipientTitle. This form is to enter email addresses.

Most Project Managers will have an email associated.
However there may be other email addresses for people that are not in the
fPMAddEdit form.

In the form fPMAddEdit I have a btnEmail that opens the fEmailAddEdit form.
If there is a record associated with PMID in fEmailAddEdit then display
data. - PMLName=RecipientLastName, PMFName=RecipientFirstName ,
Title=RecipientTitle. (In case there are edits to the 3 fields)
If there is not a record already associated with the PMID then fill in the 3
fields...PMLName=RecipientLastName, PMFName=RecipientFirstName ,
Title=RecipientTitle.

The below code works but it only send over one field(fPMAddEdit.PMLName) to
fEmailAddEdit.RecipientLastName).
How can I edit the code to send all 3 fields?

Private Sub btnEmail_Click()
On Error GoTo Err_btnEmail_Click

If Me.Dirty = True Then
Me.Dirty = False
End If

Dim strOpenArgs As String
Dim strCriteria As String
Dim strFormName As String
On Error GoTo Proc_Error
strFormName = "fEmailAddEdit"
strCriteria = "[PMID] = " & Me![PMID]
strOpenArgs = Me![PMLName]
DoCmd.OpenForm strFormName, WhereCondition:=strCriteria, OpenArgs:=strOpenArgs
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in btnEmail_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit

End Sub


Private Sub Form_Open(Cancel As Integer)
Const Quote As String = """"
If Me.OpenArgs & "" <> "" Then
Me![RecipientLastName].DefaultValue = Quote & Me.OpenArgs & Quote
End If

 

[Jeff Boyce]

Deb

Let me make sure I understand ...

You have one form in which you have FirstName, LastName, and Title, all of
which point back to an underlying table in which you store these.

Then you have a second form, pointed at another table, into which you wish
to copy FirstName, LastName and Title. If that's an accurate re-statement,
?why?!

In a well-normalized relational database, you would not need to store the
same fact (e.g., FirstName) more than once.

I can't be sure from your description, but it sounds like some of your
tables may look more like spreadsheets. Access is not a spreadsheet.

If my paraphrasing is accurate, you are facing a "pay now or pay later"
situation. If you don't spend the time now to normalize your data, you WILL
spend (more) time later trying to maintain your data. As a small example,
if someone mistakenly entered "Jonh Doe" in your first form, and your
application automatically 'pushed' that into the second form/table, what
will happen when that person realizes his/her mistake and fixes the first
form/table data? Won't the second form/table have the wrong version?

Data integrity is a major reason why you want to spend the time up front,
normalizing your data.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Access 2003
I have a form called fPMAddEdit(PK PMID and fields PMLName, PMFName,
Title.
This form is to enter new Project Managers.
There is a second form called fEmailAddEdit(fields RecipientLastName,
RecipientFirstName and RecipientTitle. This form is to enter email
addresses.

Most Project Managers will have an email associated.
However there may be other email addresses for people that are not in the
fPMAddEdit form.

In the form fPMAddEdit I have a btnEmail that opens the fEmailAddEdit
form.
If there is a record associated with PMID in fEmailAddEdit then display
data. - PMLName=RecipientLastName, PMFName=RecipientFirstName ,
Title=RecipientTitle. (In case there are edits to the 3 fields)
If there is not a record already associated with the PMID then fill in the
3
fields...PMLName=RecipientLastName, PMFName=RecipientFirstName ,
Title=RecipientTitle.

The below code works but it only send over one field(fPMAddEdit.PMLName)
to
fEmailAddEdit.RecipientLastName).
How can I edit the code to send all 3 fields?

Private Sub btnEmail_Click()
On Error GoTo Err_btnEmail_Click

If Me.Dirty = True Then
Me.Dirty = False
End If

Dim strOpenArgs As String
Dim strCriteria As String
Dim strFormName As String
On Error GoTo Proc_Error
strFormName = "fEmailAddEdit"
strCriteria = "[PMID] = " & Me![PMID]
strOpenArgs = Me![PMLName]
DoCmd.OpenForm strFormName, WhereCondition:=strCriteria,
OpenArgs:=strOpenArgs
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in btnEmail_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit

End Sub


Private Sub Form_Open(Cancel As Integer)
Const Quote As String = """"
If Me.OpenArgs & "" <> "" Then
Me![RecipientLastName].DefaultValue = Quote & Me.OpenArgs & Quote
End If