Hi Quin,
I'm glad it worked OK for you but I can take no credit for this
really. It is the sort of thing which is described in the book called
Access 2000 Developer's Handbook, Volume 1 by Getz, Litwin and
Gilbert. I would recommend you purchase this book if you have any
interest in Access at all. As Aaron said, you should also try and
learn a bit of SQL (the book has a whole chapter on this subject), you
will find it invaluable later.
You are basically correct about how the Combo box works. This method
adds two columns to the Combo box, column 0 is hidden (by setting the
Column Width to 0) and is used to supply the required criteria to the
query.
Column 1 is visible and shows the various items for the user in the
Combo box. You need them twice because the query uses the first item
and the user sees the second item. Try changing one of the items to
something different and see what happens. If you change the second
Grave to xyz, for example, the query will work exactly the same except
the user will see xyz instead of Grave in the Combo box. If you change
the first Grave to xyz the user will see Grave in the Combo box but
the query will return no records because it cannot find a match for
xyz in the table.
The Like command in the query is used because the * character returns
a match on any value in the table which is what you want for the ALL
option.
Hope that is a bit clearer.
Good luck with your project.
Peter.
On Thu, 18 Jan 2007 20:36:01 -0800, Quin
<(E-Mail Removed)> wrote:
>Peter, your answer was exactly what I was hoping for. I understood how to
>impliment every step and it only took me a few minutes to test it and get it
>working!
>
>I'm not exactly sure why it works. I take it that the "like" statement in
>the query lets the "*" work when it gets sent to the query and I think maybe
>the 2 in the column count along with the 0cm;1cm in the Column Widths
>property has something to do with hiding unwanted text in the combo box but
>I'm not too sure why the Dayshift, Swingshift, Grave is repeated in the row
>source.
>
>Apparently you know exactly what you are doing because it works great. This
>will be a great piece of knowledge for me to use on a regular basis.
>
>Thanks,
>
>Quin
>
>"Peter Hibbs" wrote:
>
>> Quin,
>>
>> If the number of items in the Combo box is large and is likely to
>> change as users add more items then you should use a table to store
>> those items and give the users a method to add new ones.
>>
>> If on the other hand, the number of items is small and is not likely
>> to change significantly then the method you are already using would be
>> preferable. To do what you want do as follows :-
>>
>> In the Combo box control leave the Row Source Type as Value List.
>> In the Row Source property enter -
>> *;<< ALL >>;DayShift;DayShift;SwingShift;SwingShift;Grave;Grave
>> Enter 2 in the Column Count property.
>> Enter 0cm;1cm in the Column Widths property.
>> Enter "*" in the Default Value property (if you want the << ALL >>
>> item to be the default).
>> Make sure the Limit To List property is set to Yes.
>> In the After Update event you should requery the form or list box that
>> is displaying your data. Something like ListBox.Requery.
>> I enclosed the ALL in << >> to make sure it was the first item in the
>> list (although in your example it would be anyway).
>>
>> In the query change the criteria to -
>> Like [Forms]![frm_Employees]!Combo10]
>>
>> Incidentally, you should really give your controls more meaningful
>> names such as cboEmployees. It's not essential but it will save time
>> later when your database gets more complicated.
>>
>> Hope that helps.
>>
>> Peter Hibbs.
>>
>> On Wed, 17 Jan 2007 23:02:00 -0800, Quin
>> <(E-Mail Removed)> wrote:
>>
>> >I would like to add All to a combo box to select all records listed in the
>> >drop down box.
>> >
>> >I have created a combo box within a form that will select DayShift
>> >SwingShift or Grave employees from a list of employees. The Row Source
>> >type is a value list but I could change that if needed. If I select
>> >DayShift SwingShift or Grave from the combo box, a query returns to
>> >the form only names of employees within that category.
>> >
>> >I would also like to have an All function that will show the entire list
>> >of employees if it is selected in the combo box.
>> >
>> >My query currently uses [forms]![frm_Employees]!Combo10] as the criteria.
>> >
>> >I spent several hours today trying to follow instructions from Microsoft
>> >Support at http://support.microsoft.com/kb/210290 That site has some code to
>> >accomplish what I need. Unfortunately after following their directions my
>> >combo box does not include the word All or any other options to select. I
>> >believe I am pasting the code correctly into the form and setting the row
>> >source correctly per instructions. Im not sure if I am following the
>> >instruction for the Tag property correctly.
>> >
>> >I have also looked at information on using a Union Query Method but I dont
>> >understand it.
>> >
>> >Any suggestions or simplifications on how to accomplish this will be
>> >appreciated.
>> >
>> >Quin
>>
Similar Threads
