Solved If Then Combobox based on value (1 Viewer)

[foshizzle]

I have a combobox on a form which is used to build report criteria. The user selects a customer from the combobox, enters a begin/end date then a report is opened based on those values. The report is based off a query and shows only the customer selected in the combobox. I've recently been asked to include a report for all customers. Instead of making a new form, I added an "(All)" option to the combobox as follows:

SELECT tblAirlines.AirlineID, tblAirlines.AirlineCompany FROM tblFuelProviders INNER JOIN tblAirlines ON tblFuelProviders.ProviderID = tblAirlines.ProviderNameLookup WHERE (((tblAirlines.AirlineStatus)="Active")) ORDER BY tblAirlines.AirlineCompany UNION SELECT "(All)","(All)" FROM tblFuelProviders
ORDER BY tblAirlines.AirlineCompany;

I've also built a separate report which includes all customers. I'm trying to use an IF statement which would open the report for all customers if "(All)" is selected in the combobox. However, Access now crashes when trying to execute the IF statement for the combobox if "(All)" is selected. My statement is as follows:

If Me.cboAirlineCompany.Value = "(All)" Then
strReport = "rptTransactionsAll"
Else
strReport = "rptTransactions"
End If

- Using a msgbox prompt, I am able to validate "(All)" is returned when selected in the combobox
- Both reports work properly outside of the IF statement

 

[The_Doc_Man]

Did you get ANY error message from Access on this?

If not, can you get to the system's Event Viewer to look for an Access-related event at the time of that program crash?

We would need to see some error messages to figure out what it is trying to tell you.

 

[pbaldy]

Can't help with the error but you don't need (and shouldn't have) 2 reports. Have the combo return Null for the bound column, which I assume is hidden, and use this in the report's query:

Queries: Use a parameter to return all records if Null

Edit: Not code required with this method.

 

[foshizzle]

Did you get ANY error message from Access on this?

If not, can you get to the system's Event Viewer to look for an Access-related event at the time of that program crash?

We would need to see some error messages to figure out what it is trying to tell you.

Hi Doc - this is the only type Access error shown in the Event Viewer.

Faulting application name: MSACCESS.EXE, version: 15.0.5267.1000, time stamp: 0x5f0df938
Faulting module name: MSACCESS.EXE, version: 15.0.5267.1000, time stamp: 0x5f0df938
Exception code: 0xc0000005
Fault offset: 0x00135245
Faulting process id: 0x2668
Faulting application start time: 0x01d73dffe80fa2e5
Faulting application path: C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE
Faulting module path: C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE
Report Id: a5b41844-2fa7-4ea7-846e-ea8fdfcea390
Faulting package full name:
Faulting package-relative application ID:

 

[foshizzle]

Can't help with the error but you don't need (and shouldn't have) 2 reports. Have the combo return Null for the bound column, which I assume is hidden, and use this in the report's query:

Queries: Use a parameter to return all records if Null

Edit: Not code required with this method.

I've built two reports due to the way the report is designed, but let me look into this.

 

[foshizzle]

I've built two reports due to the way the report is designed, but let me look into this.

How would I make the original report work by using "all" in the combobox? I dont want to have to instruct the users to leave the combo box blank for all. The original query is as follows:

SELECT tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup, tblAirlineTransactions.FlightNumber, tblAirlineTransactions.AircraftNumber, tblAirlineTransactions.GallonsIssued, tblAirlineTransactions.GallonsReceived, tblIssueTypes.IssueType
FROM tblIssueTypes INNER JOIN tblAirlineTransactions ON tblIssueTypes.IssueTypeID = tblAirlineTransactions.IssueTypeLookup
GROUP BY tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup, tblAirlineTransactions.FlightNumber, tblAirlineTransactions.AircraftNumber, tblAirlineTransactions.GallonsIssued, tblAirlineTransactions.GallonsReceived, tblIssueTypes.IssueType, tblIssueTypes.IssueTypeID
HAVING (((tblAirlineTransactions.TransactionDate) Between [TempVars]![tmpStartDate] And [TempVars]![tmpEndDate]) AND ((tblAirlineTransactions.AirlineCompanyLookup)=[Forms]![frmReportBuildTransactions]![cboAirlineCompany]))
ORDER BY tblAirlineTransactions.TransactionDate DESC;

 

[pbaldy]

How would I make the original report work by using "all" in the combobox?

You'd use the method in the link. You'd get the Null value in the combo when All was selected like this:

SELECT tblAirlines.AirlineID, tblAirlines.AirlineCompany FROM tblFuelProviders INNER JOIN tblAirlines ON tblFuelProviders.ProviderID = tblAirlines.ProviderNameLookup WHERE (((tblAirlines.AirlineStatus)="Active")) ORDER BY tblAirlines.AirlineCompany UNION SELECT Null,"(All)" FROM tblFuelProviders

 

[foshizzle]

You'd use the method in the link. You'd get the Null value in the combo when All was selected like this:

SELECT tblAirlines.AirlineID, tblAirlines.AirlineCompany FROM tblFuelProviders INNER JOIN tblAirlines ON tblFuelProviders.ProviderID = tblAirlines.ProviderNameLookup WHERE (((tblAirlines.AirlineStatus)="Active")) ORDER BY tblAirlines.AirlineCompany UNION SELECT Null,"(All)" FROM tblFuelProviders

ok, this is what the form looks like now. I've set it to show 2 columns so you can see the underlying value.
When I select (All) from the combobox, the selection turns blank (null). Any thoughts on keeping keeping (All) after the selection has been made so the user understands their selection is all and not null?

 

[arnelgp]

set the Bound Column to 2.

 

[foshizzle]

set the Bound Column to 2.

ok; i still have a problem here bc Access crashes when bound to column 2.

 

[pbaldy]

What column does the query use, the ID or the text? Normally it would be the ID. It sounds like that's what it's using if your query worked before, so changing the bound column to 2 would break the query. Your combo would have the ID as the bound column but hidden. The user would see "All" but the query would see the Null and show all records. Can you attach the db here?

 

[foshizzle]

What column does the query use, the ID or the text? Normally it would be the ID. It sounds like that's what it's using if your query worked before, so changing the bound column to 2 would break the query. Your combo would have the ID as the bound column but hidden. The user would see "All" but the query would see the Null and show all records. Can you attach the db here?

that sounds right. please see attached.
I'm using the dates 4/1/21 - 4/7/21 for testing.

 

[pbaldy]

Sorry, I misunderstood what you were saying. It is curious that the "All" disappears, though the report works as desired, but I haven't used this method myself. My users like to leave things blank rather than choose "All". In testing it seemed to be because the bound value was Null. I tested using -1 instead, to keep it at the top of the list, and changing the query to look for -1 instead of Null. That seemed to work, but I won't be surprised if someone jumps in and points out how I'm goofing up. I'd be interested to hear the proper solution.

 

[Pat Hartman]

I would have taken a different tact. Build a single report and add a break by Customer. This has no effect when you select a single customer but when you select all, it will print each Customer starting on a new page. Then you have two options for how to control this and the option you choose will be dependent on whether you need to run the report from multiple forms or from only a single form. If you only run from a single form, then the criteria could be:

From yourtableOrQuere WHERE somefield = Forms!yourform!yourcombo OR Forms!yourform!yourcombo Is Null

So, if you select one Customer from the combo, you get a report for just the one customer. Otherwise, you get all customers.

If you need to run the report from more than one form, then you need to pass the criteria using the OpenForms where argument.

If Me.cboCustID& "" = "" Then
    DoCmd.OpenReport "yourreportname", acViewPreview
Else
    DoCmd.OpenReport "yourreportname",  acViewPreview,, "CustID = " Me.cbpCustID
End If

 

[arnelgp]

i did not encounter Any error and i am using:

UNION "(All)", "(All)"

 

[pbaldy]

And you recommend maintaining 2 reports just to display different criteria?

 

[arnelgp]

the "other" report is not on the db.
if they are exactly the same report structure, then just use
a WhereCondition on opening the report (single report against 2 separate report) for a specific
Airline company.

you only use 2 separate report if the reports are different and it is more
easier to maintain each separately.

 

[The_Doc_Man]

Hi Doc - this is the only type Access error shown in the Event Viewer.

Faulting application name: MSACCESS.EXE, version: 15.0.5267.1000, time stamp: 0x5f0df938
Faulting module name: MSACCESS.EXE, version: 15.0.5267.1000, time stamp: 0x5f0df938
Exception code: 0xc0000005
Fault offset: 0x00135245
Faulting process id: 0x2668
Faulting application start time: 0x01d73dffe80fa2e5
Faulting application path: C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE
Faulting module path: C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE
Report Id: a5b41844-2fa7-4ea7-846e-ea8fdfcea390
Faulting package full name:
Faulting package-relative application ID:

Code 0xC0000005 is a fatal memory management error. The application AND module is MSACCESS.EXE which suggests a problem when calling something that isn't properly defined. My first guess is a broken reference because this application/module combo says it is not one of YOUR modules and not some foreign library. Usually the module doesn't match the app for these. Can you go into your VBA page and check the Tools >> References for a missing or broken reference?

The other possibility is a bad installation of Access itself or an incorrect choice of which bitness you will use. I have to admit I don't know exactly which bitness (32-bit or 64-bit) you are running based on the fault info, but an incompatibility of addressing could CERTAINLY cause this problem.

Which bitness of Access are you running and which bitness of Access was this app developed under?

 

[foshizzle]

Thanks everyone for your suggestions. I haven't updated this post yet because I started rebuilding the report in order to match both "All" and single item selections from the combo box per Pat's suggestion. Just having trouble making it work in order to display the summary along with the airilne customer since the report was originally built with two sub report in the detail section in order to match the format from the original DB built years ago.

Doc; I'm running Access x86 and developed the app on x86.
Arnelgp; I'm probably going to try the UNION "(All)", "(All)" shortly since im having issues with the report part.

Will update soon. Thanks everyone!

 

[Pat Hartman]

since im having issues with the report part.

Did you try my suggestion?