'All' Option in Combobox?

[klix]

Hello, I have a form which has three comboboxes: Account, Month, and Year. Currently I'm limited to one account's records for one month in one year. However I'd like to add an 'All' option in each of the comboboxes in order to be able to select multiple records.

(For example, I want to display:
1. all records of a particular Month, despite the Account - across 'all' Accounts; or
2. all the records for a particular Year within a certain Account - across 'all' Months...etc.)

I'll appreciate any help!

 

[pbaldy]

Here's a couple of thoughts:

http://www.mvps.org/access/forms/frm0043.htm

http://www.mvps.org/access/queries/qry0001.htm

 

[klix]

What a coincidence: as I was looking for a solution I came across that first article-link you posted. Unfortunately when I tried it it didn't work. When I tried the second one (for queries) I thought it worked at first. But alas on closer inspection it didn't, especially when I tried to place the modified formula in two fields.

Since I got the comboboxes working a couple weeks ago (thanks again to your help) I think this 'All' option might be the last thing I'm missing. I'll tell you more about how the comboboxes work so maybe you can think of another solution.

First of all, I have two queries. One displays credit card-type information, so it's not organized by actual transaction date but rather by billing date. For that one I have a special field in my table for the billing month which I enter manually. In my other query (based on the calendar month), a 'Month' expression calculates the month name from the 'TDate' using "Month: MonthName(Month([TDate]))". I'm not sure if those details will even matter because both comboboxes display their lists from another table where I've written the name of each month.

So...
Combo0: rowsource is [Month], query criteria is for (calculation) Month field
Combo1: rowsource is [Month], query criteria is for Billing Month field

You can see how in the Billing Month case at least, since 'All' is not a valid month, nothing displays. But it doesn't for the calculation either.

 

[DCrake]

Here is a variation on a theme.

Lets say your original rowsource for the combo box was based on a grouped date in a table, in your example the billing date grouped by the month of the billing date. We won't disgard this but use it in code.

First you need to set the RowsourceType to Value List.
Next on the OnLoad of your form place the following code

Call GetBillingPeriods

GetBillingPeriods is a sub routine that we are going to add to the forms vba section

Public Sub GetBillingPeriods()

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset(QryBillingPeriodsByMonth)

'Test for EOF
'Empty the row source
   Me.CboBillingPeriod.RowSource = ""
If Not Rs.EOF And Not Rs.BOF Then
   Me.CboBillingPeriod.AddItem " (All)"
   Do Until Rs.EOF
       Me.CboBillingPeriod.AddItem Rs("BillingMonth")
       Rs.MoveNext
   Loop
   Rs.Close
Else
   Me.CboBillingPeriod.AddItem "No data available"
End If
Set Rs = Nothing

End Sub

The result of this will first add the (All) option as the first item in the list, followed by all the available periods in the table.

Remember:To use your own query/field names if different from the aircode shown above.

David

 

[pbaldy]

I think the key to the method I posted is that they work together. You'll note that the first link added Null to the first, bound column of the combo, and "All" to the second, visible column. The second demonstrates a way to have all records returned when the combo is null.

 

[klix]

pbaldy:
I just can't quite figure out how to properly incorporate that first link you posted. Whenever I change the code for the combobox, a message pops up saying 'The number of columns in the two selected tables or queries of a union query do not match'. If all I have is one column on the table with a null value at the beginning, I can't understand how an error occurs. This is quite frustrating because it seems so simple.


DCrake: Forgive my unfamiliarity but I can't find an 'On Load' in the properties of my combobox (or command button or form as a whole, for that matter). I'm working with Access 2003 if it makes a difference. I was able to open up the VB code builder though. I inserted your code (with what I thought were the appropriate changes to the syntax to make it fit my db) but when I tried to do anything a message said 'Cannot find GetBillingPeriods'...By the way, in case I want to try again, how many changes will I have to make to that aircode to customize it properly to my own db? I can't tell the difference between what's general code and what's a reference to my db.

 

[pbaldy]

The sample has the more traditional 2 columns (ID field and text field), so your fix would be to select your single field twice, like this adaptation of the link's example:

SELECT Field1, Field1 FROM Customers UNION Select Null as AllChoice , "(All)" as Bogus From Customers ORDER BY Field1

Then make sure your combo is set to have 2 columns, with the first one the bound column, and hiding the first.

 

[DCrake]

For future reference:

Public Sub [B]GetBillingPeriods[/B]()

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset([COLOR="Red"]QryBillingPeriodsByMonth[/COLOR])

'Test for EOF
'Empty the row source
   Me.[COLOR="Lime"]CboBillingPeriod[/COLOR].RowSource = ""
If Not Rs.EOF And Not Rs.BOF Then
   Me.[COLOR="lime"]CboBillingPeriod[/COLOR].AddItem " (All)"
   Do Until Rs.EOF
       Me.[COLOR="lime"]CboBillingPeriod[/COLOR].AddItem Rs("[COLOR="Blue"]BillingMonth[/COLOR]")
       Rs.MoveNext
   Loop
   Rs.Close
Else
   Me.[COLOR="Lime"]CboBillingPeriod[/COLOR].AddItem "No data available"
End If
Set Rs = Nothing

End Sub

The Public Sub GetBillingPeriods() is a sub routine that you can either save in a module or in the code part of a form. By saving it in a module it makes it available anywhere in the application. Storing it in a form restricts its usage to that form or an direct references to the form.


The Red highlights a query that would be created by you and should be replaced by the name of your query. Or you could save it using the name I have suggested in the code. Likewise the green represents the combo boxes on your form and the blue represents a field in the query.

There is no OnLoad property for a combobox what I was referring to is the OnLoad event of the Form.

 

[klix]

pbaldy:
Thanks for the clarification. After fixing the sql I'm pretty sure I got it working now. I want to have more time to run further tests and make sure that it's in fact working, that I've covered all the bases I want to. But I just wanted to give you an initial confirmation. I'll post back soon to let you know for sure. One other thing: is the 'all' option supposed to be blank? Or is it actually supposed to say 'All'? Mine's blank. That's ok but if the words are an option I'd like to add them.

DCrake:
Your clarifications were also a great help but unfortunatly I'm still severely lacking vb codewriting skills and understanding. I changed the highlighted portions per your instructions but kept getting error messages.

"Set Rs = CurrentDb.OpenRecordset(Account Transactions)" was highlighted blue and a popup said it had a 'compile error: syntax error'. ('Account Transactions' is the name of my query. I tried putting it in brackets too but that didn't work either.) Then once I clicked ok "Public Sub GetBillingPeriods()" was highlighted yellow and a yellow arrow was pointing to it.

 

[pbaldy]

Set up as designed, the first column of the combo would be hidden, and that's where the Null (blank) would be. The user would see "All". The column count would be 2, the bound column would be 1, and the column widths would look like:

0";1"

 

[klix]

Sorry I'm delayed in response. I hope you had an enjoyable Memorial Day weekend.

I'm satisfied with what your advice has accomplished with my form. Thank you so much. But since I 'have you here' I want to ask another question (I had two but forgot the second one right now). Now that I can display an 'All' category, can I order the months properly?

With 'ORDER BY Month' they're ordered alphabetically. If I add a column to my original table with each number next to the corresponding month, then change the combobox to 'ORDER BY Number', the numbers are displayed 1, 10, 11, 12, 2, 3, etc. Go figure. Can you think of a way to make the months order by their proper place in the calendar?

... Just thought of the other: Can I have the form display every time I open the db? I've seen it before; is it too complicated? Thanks!

 

[pbaldy]

It sounds like that field is text rather than numeric. Try changing it to Number/Integer.

Look in Tools/Startup for the setting of which form is displayed at startup.

 

[klix]

I've been toying around some more and everything works great. Thanks again - I appreciate the help. As for my two questions the other day:

I had the 'Number' field as Number/Byte. But I've since then tried to change it to every category under Number with no results. I finally had to change the numbers to 11, 12, 13...22. I'm still puzzled as to why the db ordered the numbers based on the first digit period but I can at live with it.

I did change the settings so my form displays on startup. However (and this was half of my question, I just forgot to write it) the form doesn't display full screen at startup. If possible I'd like it to not only display, but full screen at startup. Easily possible? Thanks.

 

[pbaldy]

Try this in the load event:

DoCmd.Maximize

 

[klix]

Worked like a charm! Thanks once again for all your help.

 

[ghouse]

Set up as designed, the first column of the combo would be hidden, and that's where the Null (blank) would be. The user would see "All". The column count would be 2, the bound column would be 1, and the column widths would look like:

0";1"

Hi all,

I hate to dig up an old thread but this one answered one of my questions but has left me with a slightly different problem.

When setting things up as described above I can get the combobox and result query to function properly when all is selected. However, when all is selected in the combobox it shows the Null value because the bound column is 1 rather than 2 which displays (All).

Is there a way to have the combobox display column 2 after selection when the bound column is 1? Now when I select (All) from my drop down menu the query works but the combobox is showing Null after the selection has occurred.

It seems as if what I have should work because everything I have read is that the combobox will display the first non-zero column and it does while the user is attempting a selection. However, after the selection it is the bound column that is being displayed even though the width for this column is set to 0".

 

[pbaldy]

Did you set up the column widths as described?

 

[ghouse]

Did you set up the column widths as described?

Yes the column widths were set-up as described. It appears as if the combobox is always displaying the bound column once a selection has occurred.

 

[pbaldy]

Can you post the db? It should not display a hidden column.

 

[ghouse]

Can you post the db? It should not display a hidden column.

pbaldy,

Sorry I didn't get back to you on this yesterday. Swamped with another project. I will see if I can't get something up today.