Filtering record using combo box list

  • Follow


I've created a form with a combox box and a button to search for records in a 
table. The button is linked to a macro which filters the table, opens and 
shows the desired record(s) in another form.

The 'where' condition in the macro specifies the criteria for filtering, ie. 
the value selected in the combo box should be equal to a table field. The 
desired records did come out but the strange thing is that, an extra record 
will also appear. To my horror, the field which is being used as a criteria 
has been altered. So I tested out with other values in the combo box and each 
time this happened again. I'm playing hide-and-seek with the software and 
changing the values back to their original.

I've being trying to tackle this problem but to no avail. Does anyone ever 
encounter this problem before or know what's causing it ? I cannot afford to 
have untrue data in my table again.
0
Reply Utf 12/10/2007 9:50:02 AM

The combo you are using to filter the form - is it bound to a field?
Or does it show "Unbound" when you open the form in design view?

You need to use an *unbound* combo to filter the form. Don't try to use the 
one combo for both data entry and for filtering.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lhtan123" <lhtan123@discussions.microsoft.com> wrote in message
news:2B3B5B1E-8494-4930-A04E-887FDE410EA0@microsoft.com...
> I've created a form with a combox box and a button to search for records 
> in a
> table. The button is linked to a macro which filters the table, opens and
> shows the desired record(s) in another form.
>
> The 'where' condition in the macro specifies the criteria for filtering, 
> ie.
> the value selected in the combo box should be equal to a table field. The
> desired records did come out but the strange thing is that, an extra 
> record
> will also appear. To my horror, the field which is being used as a 
> criteria
> has been altered. So I tested out with other values in the combo box and 
> each
> time this happened again. I'm playing hide-and-seek with the software and
> changing the values back to their original.
>
> I've being trying to tackle this problem but to no avail. Does anyone ever
> encounter this problem before or know what's causing it ? I cannot afford 
> to
> have untrue data in my table again. 

0
Reply Allen 12/10/2007 11:42:06 AM

So are you suggesting that I create the combo box list using another table 
instead of binding the combox box to a field in the table which is the 
recordset for the form ?

In that way, I'll have to update the combo box list seperately from the 
table everytime when there's a new value. There's no other convenient way ?

"Allen Browne" wrote:

> The combo you are using to filter the form - is it bound to a field?
> Or does it show "Unbound" when you open the form in design view?
> 
> You need to use an *unbound* combo to filter the form. Don't try to use the 
> one combo for both data entry and for filtering.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "lhtan123" <lhtan123@discussions.microsoft.com> wrote in message
> news:2B3B5B1E-8494-4930-A04E-887FDE410EA0@microsoft.com...
> > I've created a form with a combox box and a button to search for records 
> > in a
> > table. The button is linked to a macro which filters the table, opens and
> > shows the desired record(s) in another form.
> >
> > The 'where' condition in the macro specifies the criteria for filtering, 
> > ie.
> > the value selected in the combo box should be equal to a table field. The
> > desired records did come out but the strange thing is that, an extra 
> > record
> > will also appear. To my horror, the field which is being used as a 
> > criteria
> > has been altered. So I tested out with other values in the combo box and 
> > each
> > time this happened again. I'm playing hide-and-seek with the software and
> > changing the values back to their original.
> >
> > I've being trying to tackle this problem but to no avail. Does anyone ever
> > encounter this problem before or know what's causing it ? I cannot afford 
> > to
> > have untrue data in my table again. 
> 
> 
0
Reply Utf 12/11/2007 1:21:01 AM

Thank you. It works now. But I'm curious about why that occurs. 

"lhtan123" wrote:

> So are you suggesting that I create the combo box list using another table 
> instead of binding the combox box to a field in the table which is the 
> recordset for the form ?
> 
> In that way, I'll have to update the combo box list seperately from the 
> table everytime when there's a new value. There's no other convenient way ?
> 
> "Allen Browne" wrote:
> 
> > The combo you are using to filter the form - is it bound to a field?
> > Or does it show "Unbound" when you open the form in design view?
> > 
> > You need to use an *unbound* combo to filter the form. Don't try to use the 
> > one combo for both data entry and for filtering.
> > 
> > -- 
> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> > 
> > "lhtan123" <lhtan123@discussions.microsoft.com> wrote in message
> > news:2B3B5B1E-8494-4930-A04E-887FDE410EA0@microsoft.com...
> > > I've created a form with a combox box and a button to search for records 
> > > in a
> > > table. The button is linked to a macro which filters the table, opens and
> > > shows the desired record(s) in another form.
> > >
> > > The 'where' condition in the macro specifies the criteria for filtering, 
> > > ie.
> > > the value selected in the combo box should be equal to a table field. The
> > > desired records did come out but the strange thing is that, an extra 
> > > record
> > > will also appear. To my horror, the field which is being used as a 
> > > criteria
> > > has been altered. So I tested out with other values in the combo box and 
> > > each
> > > time this happened again. I'm playing hide-and-seek with the software and
> > > changing the values back to their original.
> > >
> > > I've being trying to tackle this problem but to no avail. Does anyone ever
> > > encounter this problem before or know what's causing it ? I cannot afford 
> > > to
> > > have untrue data in my table again. 
> > 
> > 
0
Reply Utf 12/11/2007 2:56:00 AM

You have it working, so that's good.

The idea was not to bind the combo to a different table, but to leave it 
unbound.

If the combo is bound to a field, it makes sense that the combo will change 
the value of the field *before* you move away from that record and apply a 
filter or jump to another.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lhtan123" <lhtan123@discussions.microsoft.com> wrote in message
news:A801FEF6-4B38-4FDF-944C-8C597E49476C@microsoft.com...
> Thank you. It works now. But I'm curious about why that occurs.
>
> "lhtan123" wrote:
>
>> So are you suggesting that I create the combo box list using another 
>> table
>> instead of binding the combox box to a field in the table which is the
>> recordset for the form ?
>>
>> In that way, I'll have to update the combo box list seperately from the
>> table everytime when there's a new value. There's no other convenient way 
>> ?
>>
>> "Allen Browne" wrote:
>>
>> > The combo you are using to filter the form - is it bound to a field?
>> > Or does it show "Unbound" when you open the form in design view?
>> >
>> > You need to use an *unbound* combo to filter the form. Don't try to use 
>> > the
>> > one combo for both data entry and for filtering.
>> >
>> > "lhtan123" <lhtan123@discussions.microsoft.com> wrote in message
>> > news:2B3B5B1E-8494-4930-A04E-887FDE410EA0@microsoft.com...
>> > > I've created a form with a combox box and a button to search for 
>> > > records
>> > > in a
>> > > table. The button is linked to a macro which filters the table, opens 
>> > > and
>> > > shows the desired record(s) in another form.
>> > >
>> > > The 'where' condition in the macro specifies the criteria for 
>> > > filtering,
>> > > ie.
>> > > the value selected in the combo box should be equal to a table field. 
>> > > The
>> > > desired records did come out but the strange thing is that, an extra
>> > > record
>> > > will also appear. To my horror, the field which is being used as a
>> > > criteria
>> > > has been altered. So I tested out with other values in the combo box 
>> > > and
>> > > each
>> > > time this happened again. I'm playing hide-and-seek with the software 
>> > > and
>> > > changing the values back to their original.
>> > >
>> > > I've being trying to tackle this problem but to no avail. Does anyone 
>> > > ever
>> > > encounter this problem before or know what's causing it ? I cannot 
>> > > afford
>> > > to
>> > > have untrue data in my table again. 

0
Reply Allen 12/11/2007 10:18:46 AM

4 Replies
593 Views

(page loaded in 0.22 seconds)

6/6/2014 7:05:23 AM


Reply: