Form report button needs to look to a table for report name 2

[cimoli]

I have different Resort hotels in my file. There are 3 styles of paperwork for a hotel because of their preference.
I built a table which i need to correlate to my form button to find the desired report to print for that hotel.
The ResortID is on the form as the source. BusID field too.

The table has the following fields in tblReport: (I wish they had not used the # in the name below)
BusID, Resort#, ReportNum, ReportName
(busID means business ID)

Here is an example of the 4 fields:
1

238​

20

rpt1234​

1

238​

88

rpt8035​

1

480​

20

rpt1234​

1

480​

30

rpt3333​

1

480​

88

rpt8088​

1

567​

88

rpt8035​

Notice that resortID's 238 and 567 use rpt8035 for reportNum88. The 480 resort uses rpt8088, which is a different styled report.

In my Form button, I have:
Private Sub cmd88_Click()
DoCmd.OpenReport "rpt8035", acViewPreview
End Sub

I need the above code to search tblReport, look for the busID, then the ResortID and then the ReportNum,
and then use whatever report is noted in the Reportname column of tblReport.

So I need my DoCmd to be flexible. I need the rpt8035 to be erased and new code
to search tblReport for the 3 fields, then it would run the report name shown in the reportname field of tblReport.

It would also be nice to have something at the end that says something like,
"could not find a report for this hotel".

Clear as mud??

Thank you for reviewing. Currently, we have about 30 form buttons on a form.
They can be replaced by just ONE form button that would look into tblReport for the
desired reportname to run. This way, the girls do not have to stop and ask themselves,
which of the 30 buttons do they pick from for a given hotel. My method would do the trick
for them as long as they picked the hotel or ResortID on the main form.

thanks

 

[cimoli]

Darn, my examples did not show as landscape.

I wanted you to see:

1 238 20 rpt1234
1 238 88 rpt8035
1 480 20 rpt1234
etc

 

[cimoli]

I envision the form button event to be something along these lines:


Private Sub cmd88_Click()

if BusID and Resort# and ReportNum agree to the combo fields picked on the main form above,
then run the xyz report below

DoCmd.OpenReport "rptXYZ", acViewPreview

Else if you did not find a match say something like,
"You did not setup this hotel for this report".

End Sub

thanks, cimoli

 

[dhookom]

Use DLookup() to get the appropriate report name from the table.

Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

I am still looking around the internet to find an answer. I will let you know when i find one.

 

[dhookom]

It looks like your BusID and Resort# fields are numeric so your code might look like:

Private Sub cmd88_Click()
   Dim strReport as String
   strReport = DLookup("ReportName","tblReport","BusID=" & Me.cboBusID & " AND [Resort#]=" & Me.cboResortNum ) & ""
   If Len(strReport) > 0 Then
       DoCmd.OpenReport strReport, acViewPreview
   End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

Duane - You are real close. I messed you up on a couple of my field names. I also need 1 more criteria for the dlookup.
This particular report button applies to the tblReport, ReportNum when it is number 71.

I spent the last 5 hours making a small dummy Access file to show you the button 8035 that is on the startup screen.
(i stripped out relationships etc that had no bearing on my form button vba report problem.)

It is on dropbox.

https://www.dropbox.com/s/9rpiacajz5ri3of/CSI 2014-08-29 version 2.zip?dl=0

I tried to adjust myself but that is a mistake, right away.
As a test, you can use BusnID of "1", TripDate of '6/25/2014", tripID of '7133' and Resort '480'.
Wait 2 seconds for people's name to show up on screen.

Then hit the 8035 button that is on the startup screen.

Then the need for the '71' tblReport, ReportNum, Dlookup criteria in tblReport is needed in the vba 8035 button.
Right now, I don't have it working. Sorry for not explaining better.

I will kick in some $$$ for this help on top of my monthly plan. Plz download the sample file to make things clearer. Glen

 

[PHV]

What about this ?

strReport = DLookup("ReportName", "tblReport", "BusnID=" & Me.cboBusnID & " AND [Resort#]=" & Me.cboResort & " AND ReportNum=71")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

May I ask for one refinement that I forgot to mention.
Can you include a condition that if the Dlookup cannot find a report for that combination,
an error message would show such as "You need to setup the report in tblReport for this Resort."

Here is what works so far in button 8035.
I am including a Dropbox file link for version 3 in case you need to test the button 8035.
I added another report, this time for hotel 500 to show if this works. it does.

So first test is busID 1, TripDate 6/25/14, Pick Trip 7133 and ResortID 480. Hit 8035 button and report rpt8035 will appear.
The name is at the bottom of the report.

Second test is busID 1, tripDate 6/20/14, pick trip 7045, resortID 500. Hit 8035 button and report rpt8043 will appear.
You can confirm by looking at the bottom of the report to see rpt8043.

Here is what we have so far. All works. Just need a message to tell the person if they forgot to setup a resort room list.
thanks.

Private Sub cmd8035_Click()

Dim strReport As String
strReport = DLookup("ReportName", "tblReport", "BusnID=" & Me.cboBusnID & " AND [Resort#]=" & Me.cboResort & " AND ReportNum=71")
If Len(strReport) > 0 Then
DoCmd.OpenReport strReport, acViewPreview
End If

End Sub

DropBox newer version 3:

https://www.dropbox.com/s/vkj2wqfks2hinw0/CSI 2014-08-30 version 3.zip?dl=0

 

[PHV]

Something like this ?

If Len(strReport) > 0 Then
    DoCmd.OpenReport strReport, acViewPreview
Else
    MsgBox "You need to setup the report in tblReport for this Resort."
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

Duane and PHV.
We need a tweak regarding the situation whenever i forget to add a resort's 71 reportnum in tblReport.

For example in version 4 (i added data for this illustration), i choose busID 1, tripDate 7/19/2014 and detail tripID 7192 and resort 62.
Pretend that i forgot to make a '71' reportnum for this resort 62 in tblReport, in which case,
I should get the Message that i need to Set it up.

Instead, i get an Error message of:
Run time error '94'. Invalid use of Null.
The yellow stays on the dlookup row. But I suspect the problem is in Msgbox area ?? I have no clue since I am a VBA novice.

Could you please investigate? Thanks so much. You are real close. The newer model 4 has
the fixes in it for you to try. First try using 7/19/2014. after you get the proper Msgbox,
then try the next one to insure that the resorts that are properly setup, continue to work.
You've done a nice job for me.


version 4 of my file is at :

https://www.dropbox.com/s/y2dwov9b3y7xcop/CSI 2014-08-30 version 4.zip?dl=0

Private Sub cmd8035_Click()

Dim strReport As String
strReport = DLookup("ReportName", "tblReport", "BusnID=" & Me.cboBusnID & " AND [Resort#]=" & Me.cboResort & " AND ReportNum=71")
If Len(strReport) > 0 Then
DoCmd.OpenReport strReport, acViewPreview

Else
MsgBox "You need to setup the report in tblReport for this Resort."

End If

End Sub

 

[dhookom]

Add the & "" like I had after DLookup() in an earlier post.



Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

So I think this ends it. For any other reader, I will summarize below:

I use the following in a Form button's On click property. Good job. Glen

Private Sub cmd8035_Click()

Dim strReport As String
strReport = DLookup("ReportName", "tblReport", "BusnID=" & Me.cboBusnID & " AND [Resort#]=" & Me.cboResort & " AND ReportNum=71") & ""
If Len(strReport) > 0 Then
DoCmd.OpenReport strReport, acViewPreview

Else
MsgBox "You need to setup the report in tblReport for this Resort."

End If

End Sub

 

[dhookom]

cimoli,
You've been a member here for over four years. You should be using tags to mark up your posts similar to how PHV and I have been posting. It is very easy now to select your code and then click the code icon. It makes code much easier to read and it's as easy as formating text in any word processor.

Duane
Hook'D on Access
MS Access MVP