Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 24th, 2007, 02:31 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default Show all values in a combo box in a report

Hiya folks!

I have a report which shows a list of all menu items sold in a restaurant. Each menu item belongs to a specific category, such as fish dishes, meat dishes and desserts.

Before accessing the report, the user is asked to select the dates for the period that he wants to view in the report, and the specific categories that he wants to view.

I have been able to do all of this. The problem is, let us assume that the user wants to view all the categories in the report (i.e. the user selects the dates, but leaves the combo blank. This is what I tried in the query of the "category" field in the report (frmSelectDatesSingle is the form where the user would select the data he wishes to view):

[Forms]![frmSelectDatesSingle]![Category]

I also tried the following but I am not sure what to put in the "True" part of the statement:

iif([Forms]![frmSelectDatesSingle]![Category]=Null;*****;[Forms]![frmSelectDatesSingle]![Category])

Hope you can help!

Thank you!!!
 
Old July 24th, 2007, 06:57 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would try to pass this part of the parameter in a WHERE clause in the OpenReport line.

Dim sCategory As String

If IsNull(Me.cboCategory) or Me.cboCategory = "" Then
   sCategory = ""
Else
   sCategory = Me.cboCategory
End If

If sCategory = "" Then
   sLink = sCategory
Else
   sLink = "[Category] = '" & sCategory & "'"
End If

DoCmd.OpenReport sReportName, , sLink

Make sure you put the sLink reference in the WHERE clause of the DoCmd. I am not sure if it is the second or third place here, but autosense will tell you the right one.

This allows the user to pass a null value.

You may also want to add this line after the DoCmd. line above:

Me.cboCategory = ""

This will reset the category combo so that if the user pulls meats the first time, and then wants to see everything, they will have a blank combo to allow them to do this.

Did that help?


mmcdonal
 
Old July 24th, 2007, 07:32 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Thanks for your reply!

I read the code and it looks like it will help. But I am not so sure where to create this or to plug it in.

Sorry but I am not so good a code. If you can tell me where or how to create this it would be a great help.

Thank you!
 
Old July 24th, 2007, 07:37 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am assuming that in order to open this report, the user selects values from some combo boxes on a form, and then clicks a button to open the report. This code should be part of the On Click event of the button to open the report.

Use the button wizard to creat the button to open the report, and then add this code between the Dim statements at the top, and the DoCmd.OpenReport statement at the bottom. Then replace the Dim sLinkCriteria line with

Dim sCategory As String
Dim sLink As String

Then on the DoCmd line, remove the word sLinkCriteria and replace it in the same place with sLink.

Did that help?

mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo Box Show all bound columns Coby Excel VBA 2 October 22nd, 2007 02:24 PM
Combo Box to Show Multi Column Info After Select Coby Excel VBA 0 August 14th, 2007 05:23 PM
values in combo box Vince_421 Access VBA 8 May 10th, 2007 08:46 AM
Testing for NULL Values in a Combo Box Aaron Edwards Access 2 September 28th, 2005 09:37 AM
Find Record Combo Box Wizard Won't Show 3rd Choice HenryE Access 0 February 2nd, 2004 11:52 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.