Combo Box prompt for reports 2

[judgekwk]

I'm trying to restrict what a user can select as input for a report. I would like them to select a company name from a list/drop-down menu.

Normally, a report parameter is a text box allowing the user to enter anything, however if they do not type the entry correctly, the report will display no data.

It will be based on a column in a table (list of company names).

Thanks for the help...

 

[GingerR]

how far have you gotten? have you made the combo box? is there a button that launches the report? not sure where you're at to begin helping. all you have to do in the report is change the criteria to the name of the combo box.

another option is to put some code into the report's On No Data event. something like

msgbox "No Data to display!",vbokonly
docmd.cancel

then if there are no records for the report, the user just gets this message and the report never shows up.

 

[judgekwk]

The report Tenant_Account_Summary_Report is based on a query called TenantHistoryQuery (which contains a history of payment transactions).

The idea is to have the user select the company for which they want to print the report.

Right now, I have placed "Like [Enter the tenant name: ]" in the criteria column. under the column "Tenant". I've tried to change it to a select statement, but that doesn't seem to work.

Let me know if you need more information.

 

[judgekwk]

Sorry. Forgot to mention that there is a button that launches the report.

 

[GingerR]

1) make a combo box on your report. do you have a table that lists each company one time? if not, you should. base your combo box on this table. follow the wizard when doing the combo box, it will lead you along. call the combo box cboTenant.

2) take the criteria out of the query. instead put [Forms]![FormName]![cboTenant]. substitute your form name for FormName.

3) what if someone doesnt choose a company in the combo box? in the button's OnClick event, copy and paste this code before the code that opens the report:

if isnull(me.cboTenant) then
msgbox "Please choose a Tenant!",vbokonly,"Missing Info"
me.cboTenant.setfocus
exit sub
end if

this will pop up a message to the user, then put the cursor in the combo box, and not open the report.

ok? try that and let us know how it goes.

 

[judgekwk]

I tried it and it didn't work.

Here's what I did: I changed the Tenant text box on the report to a combo box and called it cboTenant. I took the cirteria out of the query and replaced it with [Forms]![ReportsSwitchboard]![cboTenant]. Then, I pasted the code sample into the OnClick event for the button that launches the report. It looks like this:

Private Sub tenantsummary_Click()
On Error GoTo Err_tenantsummary_Click

Dim stDocName As String

If IsNull(Me.cboTenant) Then
MsgBox "Please choose a Tenant!", vbOKOnly, "Missing Info"
Me.cboTenant.SetFocus
Exit Sub
End If

stDocName = "Tenant_Account_Summary_Report"
DoCmd.OpenReport stDocName, acPreview

Exit_tenantsummary_Click:
Exit Sub

Err_tenantsummary_Click:
MsgBox Err.Description
Resume Exit_tenantsummary_Click

End Sub


I tried to run the report and it stopped on the code sample I inserted. I tried placing it in several differnt places and still couldn't get it to run.

Any suggestions? (Thanks for your help...)

 

[GingerR]

was there an error message?

did you make a recordsource for the combo box?

 

[judgekwk]

It gives the error:

Method or data member not found.

 

[GingerR]

hmm...you're sure the NAME of the combo box is cboTenant?

in the code, anywhere, start typing (me dot)

me.

and see if cboTenant is in the list of stuff that pops up.

 

[GingerR]

or change the dot to an !
dont know why that would work but try it

 

[judgekwk]

Like a dummy, I named the object incorrectly. Thanks for the tips. It worked nicely.

And...

Thanks for the extra code to check to see if the selection is null.

You've been a great help.

 

[DomFino]

Hi GingerR,
I too have followed your advice on this post and successfully created my first report with combo box criteria selection. Your explanation was terrific and it works very well!

My question is purely one of newness. I have tried all kinds of changes to the form that contains the combo box and it only opens in full screen mode. No matter what I change on the properties, it does not stay at the 2" by 3" I want displayed.

I am sure this may sound silly to experts like yourself, but darn if I can figure out what I am doing wrong. Any suggestions?

 

[GingerR]

try form properties
resize = yes
center = yes
the detail portion of the form must be shrunk down to the size you want
reduce the form to the size you want in design mode and hit ctrl+s (save)

you can also set pop-up to YES but i dont do that cause other forms cannot go on top of it.