Filtering record using combo box list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lhtan123 - 10 Dec 2007 09:50 GMT

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.

Reply to this Message

Allen Browne - 10 Dec 2007 11:42 GMT

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.

Signature

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.

> I've created a form with a combox box and a button to search for records
> in a
[quoted text clipped - 17 lines]
> to
> have untrue data in my table again.

Reply to this Message

lhtan123 - 11 Dec 2007 01:21 GMT

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 ?

> 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?
[quoted text clipped - 23 lines]
> > to
> > have untrue data in my table again.

Reply to this Message

lhtan123 - 11 Dec 2007 02:56 GMT

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

> 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
[quoted text clipped - 30 lines]
> > > to
> > > have untrue data in my table again.

Reply to this Message

Allen Browne - 11 Dec 2007 10:18 GMT

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.

Signature

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.

> Thank you. It works now. But I'm curious about why that occurs.
>
[quoted text clipped - 43 lines]
>> > > to
>> > > have untrue data in my table again.

Reply to this Message