To PHV:
If I write too much here, I can understand if you want to pass on my situation.
That is a good question, and I tried again today to see if I could connect tblReport to the tblReservation. 2 of the 3 fields would match up. Like you are thinking. BusnID and ResortID.
However, the 3rd field ReportNum is only in tblReport.
Here is some background should you see something that I do not see.
The form button to generate the report has an On Click Event.
The purpose is for the VBA to find the name of which 2 reports to use depending upon some criteria.
Once it dloopups into tblReport to find the desired Report name, it runs the report.
then behind the scenes, the related qry for that report, gets the Body1, Body2 etc senetences needed for that hotel report. Many hotels have different sentences. Hence why I put them into tblReport.
The form button has :
Private Sub Command30_Click()
'keep
Dim lResort As Long, sReportNameSpl As String, sReportNameInd As String
lResort = Me.txtResortID
If Nz(DLookup("ReportName", "tblReport", "[Resort#]=" & lResort _
& " AND [ReportNum]=20"), "") = "" Then
MsgBox "No Resort Available"
Exit Sub
Else
sReportNameSpl = DLookup("ReportName", "tblReport", "[Resort#]=" & lResort _
& " AND [ReportNum]=20")
End If
sReportNameInd = DLookup("ReportName", "tblReport", "[Resort#]=" & lResort _
& " AND [ReportNum]=10")
If Not IsNull(Forms![frmReservation]!txtSplinterDateIN) Then
DoCmd.OpenReport sReportNameSpl, acViewPreview
Else
DoCmd.OpenReport sReportNameInd, acViewPreview
End If
End Sub
I did not write the above vba, so feel free to criticize.
FYI, the tblReport has fields:
BusnID - number field
Resort# - number (i wished i had used reportID but too late now)
ReportNum - number
ReportName - text
Body1 - memo
Body2 - memo
Body3 - memo
Body4 - memo
Body5 - memo
Body6 - memo
Body7 - memo
Body8 - memo
Here is the catch: There are 4 ReportNum styles in tblReport for each hotel and busn. For example: busnID, resortID, reportNum.
1 510 10
1 510 20
1 510 30
1 510 40
2 510 10
2 510 20
2 510 30
2 510 40
In summary: i thought of joining tblReservation and tblReport via BusnID and ResortID (resort#) that is in both tables, however, there are 4 reportNum's. So if i joined, it would not join on just the 1 desired reportnum. my records would multiple by 4 incorrectly.
Maybe I could try and see if I can reportNum into tblReservation somehow??
as of now, i can't think of how.
Anyway, if you still want to investigate this, thanks.
So this is why i went to Dlookups. Maybe you will unlock the complicated process.
As of now, i put the dlookups for Body1, body2 etc into the qry.
But now the report using those qry fields are using just 80 to 90% of the sentences of Body1 field. it cuts them off in the report. and body1 is a memo field. so i don't get it.
thanks
Cimoli
