How to have Multi Choice Message Box

[PNGBill]

Hi Forum, access 2000
I can't get my head around what to look for on this issue.

I want a display of Report Choices and which ever is clicked will be acted on.

For one report, simple MsgBox yes/no but 2 or three reports?? to stack the message boxes would be painful to the operator.

How do you have a box appear that has multi choices??

Should have paid more attention to the lecturer 10 years ago!!
I have a 850 page text book but of course, what to look for. MsgBox doesn't cover Options such as this.

 

[Banana]

Well, the maximum choices we can get with the builtin MsgBox function is 3; Yes/No/Cancel and Abort/Retry/Cancel comes to mind. You would use something like this:

MsgBox("Make a choice.",vbYesNoCancel)

But here's my personal opinion: When you get to 3 or more choices, you do NOT want to use MsgBox. I've seen some try to shoehorn the Yes/No/Cancel MsgBox to display a message like this:

If you want to see Report 1, choose "Yes".
If you want to see Report 2, choose "No".
If you want to see Report 3, choose "Cancel".

That's just a recipe for disaster.

The simplest solution is just to create a new unbound form and make it look like a messagebox (by setting various format properties such as border style to dialog for example) with the buttons that runs VBA code to open the appropriate reports.

HTH.

 

[John Big Booty]

If I've understood you correctly, I think you are going to need to create your own message box using a pop up form. I don't believe it can be done with the native MsgBox.

 

[PNGBill]

Thanks Banana and Big John, Looks like a popup form is the way to go then.

Once created, I guess, you can copy and re use next time.

 

[Banana]

Well, if you want something more general and reusable, consider using a listbox where you can add the values (use Value List rather than Table/Query) and therefore have a Double-Click event to open the report matching the name of report in a hidden column. (Or just create a user system table to enumerate the report - whatever rocks your boat).

 

[PNGBill]

Well, if you want something more general and reusable, consider using a listbox where you can add the values (use Value List rather than Table/Query) and therefore have a Double-Click event to open the report matching the name of report in a hidden column. (Or just create a user system table to enumerate the report - whatever rocks your boat).

You are saying the procedure can include a ListBox or would this be in the Form??

 

[Banana]

Sorry, let's start again.

I propose a unbound form that contain a unbound listbox. The listbox would list all reports, either by using Value List or a Table/Query referencing a "user system table" which is just a ordinary table you create but lists "metadata", namely the friendly & programmatic name of reports you want to display inside the listbox. You then use listbox's DoubleClick event to open report based on the current selection.

Does that help?

 

[Dairy Farmer]

Whould this help you ?

 

[PNGBill]

Sorry, let's start again.

I propose a unbound form that contain a unbound listbox. The listbox would list all reports, either by using Value List or a Table/Query referencing a "user system table" which is just a ordinary table you create but lists "metadata", namely the friendly & programmatic name of reports you want to display inside the listbox. You then use listbox's DoubleClick event to open report based on the current selection.

Does that help?

Thanks Banana. Sorry for being a bit dumb.

I am using an InputBox for now so we can get the facility used and will make the popup form shortly.
Appreciate the explanation

 

[PNGBill]

Whould this help you ?

Thanks Dairy Farmer I will have a good look at the code later today.
One of problem has emerged in that of the two report options I would like to show (+ nothing = 3 options) really needs some user input to finalise so can't just be "done"
I have redirected the operator to this task and told them to try again when the report is available and then Exited the function.
This appears to work ok. Would be great if it could all be automated but this one report requires a possible operator choice as to charging Late Fees or not.

 

[Dairy Farmer]

All the Access objects are listed in MSysObjects. Reports have a value of -32764. So it is very easy to get a list box to dislay the list of reports. Just the names are a bit of a problem as it uses the actual report name.

Actually if you read further on the author shows more ways. Might be worth reading more of what he wrote.

Here is a screenie of what the link shows:

 

[Banana]

All the Access objects are listed in MSysObjects. Reports have a value of -32764. So it is very easy to get a list box to dislay the list of reports. Just the names are a bit of a problem as it uses the actual report name.

Yeah, which is why I tend to prefer a user system table or value list so we can list both friendly and programmatic users so users get to see human-friendly names.

However, one possible approach to help us "remember" is to run a query on the form's load that does a frustrated join check and if we find any reports listed in MSysObjects that aren't in the user system table, alert the user and if preferred, offer an option to list the programmatic name and thus see all reports. Of course, in production, we really don't want that but this makes it easier for us to verify that we do in fact have all reports listed on our user system table.

 

[DCrake]

Similar to Banana my preferred method is to have a table of report names, both the actual Access name and a user friendly names. Then list them in a list or combo box with the actual name hidden but use this column as the bound one. That way the user has a more clearer indication as to the contents of the report.

Another user friendly approach I use is to have a set of options in an option group relating to dates, such as;

Year to date
Last completed quarter
Last completed month
Month to date
Week to date
etc
Custom Dates

Then when the user chooses an option it completes the start and end date textboxs with the relevant dates. Having the last option being Custom dates then allows the user to enter their own date range if none of the aove are appropriate.

 

[Dairy Farmer]

A Better Way to Fill an Access Listbox With Report Names


How to Keep a Listbox of Access Report Names Current

 

[Banana]

Nice links, Dairy Farmer. I didn't see anything about friendly names but thinking about it, I bet that if we decided to be consistent and have a label somewhere on the report that would give out the friendly name, we can modify the code to peek in the report and extract the friendly name from the label and thus populate the table with both programmatic and friendly names.

 

[Dairy Farmer]

Have 2 tables. One is automatically updated with report names from MSysObjects. The second is manually updated with friendly names by the person who creates the reports.

Query1
Add/Update/Remove table names from MSysObjects to table1 and table2
Table1 (query writes/updates list of tables from MSysObjects)
ID
ReportName

Table2 (Manually edited when creating a report)
ID
ReportName
FriendlyName

Then, in form, link the ReportName in both forms for your list box.