Print one record from a form with two subforms

[RonMcIntire]

All:

I have a form with fields referenceing a table source, tblFamilyREc, and two subforms, one with a second table source tblIndivRec, and another with a query built on both of the aforementioned tables. Both tables include a FamilyID field which are joined.

I wish to print one record from the tblFamilyRec source. As you might expect, I receive the error "The specified 'FamilyID' field could refer to more than one table in the FROM clause."

Here's the offending code:

Dim strDocName As String
Dim strWhere As String
Dim strMsg As String
Dim strMsg1, strMsg2, strMsg3, strMsg4, strMsg5 As String
Dim strCCTo, strBCCTo, strSubject As String

strDocName = "srptDESK COPY - Members"
strWhere = "FamilyID=" & [FamilyID]
.
.
.
DoCmd.OpenReport strDocName, acPreview, , strWhere


I know the argument in the strWhere statement must contain a reference table data source like tblFamilyRec.FamilyID that includes the FamilyID field, but I've tried many things that seem logical to me but nothing works.

Any suggestions?

 

[dhookom]

You should never get that error. There is no reason to have the same field value in the report's record source more than once.

I expect you are using [tt][blue]SELECT ...*, ...* FROM ... [/blue][/tt]in your record source. Don't. Select the specific fields you need in your report and don't duplicate them.

Duane
Hook'D on Access
MS Access MVP

 

[RonMcIntire]

dhookom:

Thanks for your response.

I reviewed my application and I may not have been as clear as I could have been. My overall goal is to email the record.

The record source of my parent form, frmFamilyRec is a table called tblFamilyRec.
The record source of my first subform, sfrmIdivRec, is a table called tblIndivRec.
The second subform is a listing of individuals belonging to the FamilyRec. The Family and Individual tables are joined by the FamilyID and FamID records in a query called qryFamilyMembers with the FamilyID for the parent and FamID for the IndivRec table. Both the FamilyID and FamId are the same Id number but named differently for clarity.

The syntax of the query is not, as you suggest, "SELECT ...*, ...* FROM ..." rather it is "SELECT tblIndivRec.IndivID, tblFamilyRec.FamilyID, tblFamilyRec.FamName, IIf(IsNull([childorg]),"","(" & [ChildOrg] & ")") AS Org, IIf(tblIndivRec.famRelation="Deceased","",IIf([LName]<>[FamName],[NickName] & " " & [LName],[NickName] & " " & [Suffix])) AS Name, tblIndivRec.Gender, tblIndivRec.FamRelation, tblIndivRec.Birthdate
FROM tblFamilyRec INNER JOIN tblIndivRec ON tblFamilyRec.FamilyID = tblIndivRec.FamID
WHERE (((tblFamilyRec.FamilyID)=[Forms]![frmFamilyRecord - MASTER]![FamilyID]));"

For clarity, the underlying code is repeated here:

Private Sub cmdPrintMbrConfirm_Click()
On Error GoTo Err_cmdPrintMbrConfirm_Click

Dim strDocName As String
Dim strWhere As String
Dim strMsg As String
Dim strMsg1, strMsg2, strMsg3, strMsg4, strMsg5 As String
Dim strCCTo, strBCCTo, strSubject As String

strDocName = "srptDESK COPY - Members"
strWhere = "FamilyID = " & FamilyID
strMsg1 = "It's time to review and update the chapter directory again."
.
.
strMsg4 = "Thanks,"
strMsg5 = "Ron" & Chr(10) & Chr(13) & "Parish Directory Editor"

DoCmd.OpenReport strDocName, acPreview, , strWhere

DoCmd.SendObject acSendReport, strDocName, acFormatRTF, Me!nEMAIL, strCCTo, strBCCTo, "Chapter directory update", strMsg, -1

DoCmd.Close
End Sub

As far as I can tell, the offending code is above in red. I hope this is a bit clearer. FYI: The short statement in the criteria section of the query that lists the individuals in the second subform is [Forms]![frmFamilyRecord - MASTER]![FamilyID]

Thanks,

Ron

 

[dhookom]

I don't understand why:
1) you have the same criteria in the report's record source query as you are using in the WHERE CONDITION.
2) what form is "the second subform is [Forms]![frmFamilyRecord - MASTER]"

Duane
Hook'D on Access
MS Access MVP

 

[RonMcIntire]

dhookom:

Come to think of it, my problem may be in the report I'm trying to print. The above code is calling a report with a query that has three or four "SELECT ...*, ...* FROM ..." statements.

Unless you see anything that stands out in my post of Jan 15, I think I have answered my own question. Thanks for your time and your comment. I'll see what I can do about taking out the SELECT* statements.

Ron

 

[dhookom]

I thought you were suggesting the SQL statement you provided was the record source of your report. Again, try not to use select * particularly from the tables that serve as lookup tables.

Typically I create all unique field names in an MDB. Every field name begins with three letters that identify its table. For instance:
[tt][blue]
tblFamilies
famFamID
famAddress
famCity
famState
....

tblIndividuals
indIndID
indFamID links to tblFamilies.famFamID
indFirstName
indLastName
ind....[/blue]
[/tt]

This naming conventions avoids errors like "The specified '...' field could refer to more than one table"


Duane
Hook'D on Access
MS Access MVP

 

[RonMcIntire]

dHookom:

Thanks for your response. You've made some good points with your naming convention. However, one thing that stands out is how to distinguish tables from queries. It doesn't seem like a big problem though.

Thanks again,

Ron

 

[dhookom]

My table and query names generally begin with tbl or q... If I have a table and a query in another query, some of the field names may be duplicated. If this is the case, I make sure only one of the duplicate fields is in the SELECT clause.

Duane
Hook'D on Access
MS Access MVP

 

[RonMcIntire]

dHookom:

Thanks.

Ron