Filter Issue

[mckitlk]

Hello, everyone,

I'm wondering if anyone has any suggestions for this...

I want this data entry form to allow access to only a subset of
records in the underlying table, based on their Type Code. I have a
Type Code lookup table, with a Yes/No column to indicate which Type
Codes are allowed in the form. The lookup is linked to the main table
via a lookup field, and shows in the form in a combo box field.
Setting up the combo box SELECT query to limit the list to just the
allowed values was easy. It just needed a WHERE clause to check for
True in that Yes/No column.

My problem now is how to get the form's filter to block records with
Type Codes that aren't allowed. I've tried various methods of setting
the filter to something like "cbxTypeCode.Column(2) = True", both in
the Properties dialog and in code. I've also tried putting a hidden
check box on the form to pick up the value of this column, then filter
on "HiddenBox = True". Neither work - I get either syntax errors or
parameter requests.

I don't want to hard-code the Type Code into the filter because I want
this to be dynamic. If new Type Codes are added, I shouldn't have to
do more than check that Yes/No box to add them to this form.

I can build the filter string in code by doing a SELECT query and then
looping through the result. But before I do that, is there an easier,
more direct and straightforward way?

Thanks so much in advance!

Laura

 

[Klatuu]

Rather than trying to use form filtering, use a query as the record source of
your form that has a Where clause to filter on your combo box. You may need
to assign a value to the combo in the form Load event so it has a value.
Then in the After Update event of the combo, requery the form.

 

[mckitlk]

This works -- kind of. However, the Requery messes up navigation.
For example, I add Record #10, then press the Up Arrow key, which
should land me on Record #9. Instead, I find myself on Record #7.
That's bound to confuse the users.

 

[Klatuu]

Record numbers have no real meaning in an Access database. The number you
see in the navigation box is only the position of the record in the current
recordset. For example, a record is record 9 in the current recordset. If
you change the Type Code in the combo and requery the form, either the record
will not longer be in the recordset or it may have a different number.

Not confusing users is why I never show the navigation controls on the form.
I use my own custom navigation controls and no record number is show.

I considered showing you the method for staying on the same record when
requering the form, but since you are filtering on a type code, I thought
that any record in the current recordset would not be there after the
requery. Even using that; however, the record number would not be the same.

 

[mckitlk]

Oh, right. I'd forgotten about how Access stores data.

I can get it to go back to the current record. However, most of the
time the AfterUpdate event is going to fire when the user uses PgUp or
PgDn to move to the previous or next record, so that wouldn't be
appropriate.

It does begin to look like I'll have to program a pseudo-hardcoded
filter string, like I mentioned at the beginning...

 

[Klatuu]

The After Update event of the combo box will only fire after the value in the
combo has been changed.

What I don't understand is how you will go back to the record. If it is the
Type Code currently selected and you requery to get a different Type Code,
why would that record still be in the form's recordset?

 

[mckitlk]

Actually there's no need to go back. I was just responding to your
saying you could show me the method for staying on the same record;
that's not what I want to do.

I put Requery in Form_AfterUpdate, rather than the combo box's
AfterUpdate event. Hm...? I suppose the Requery WOULD be needed
somewhere, in a multiuser application?

However, the combo box itself already has been restricted to just the
values that are appropriate to this form. That's easily done in the
combo box's Row Source property. It's the form that the combo box is
part of that I'm having a problem with. The underlying table has
other records with other type codes that aren't supposed to be showing
on this form.

 

[Klatuu]

You would not want to use the Form After Update. That would cause a requery
every time you add or modify a record. And, yes, then it would go back to
the first record of the current set each time. It really does need to be in
the combo's After Update.

Multi User doesn't figure in to this issue. The filtering only applies to
the current session.

 

[mckitlk]

I tried putting the Requery in the Type Code's AfterUpdate event.
That's causing the form's BeforeUpdate event to fire, which is setting
off my validity checks, which fail because this is the first field on
the form and there are others that are required.

This seems to work fine without the Requery. So I need to ask, why is
Requery needed?

 

[Klatuu]

The reason the form is requerying is that your combo is a bound control. You
are chaning the value for the current record when you do that, so now the
form is diry and it will requery the form. A combo used to search should not
be a bound control. A bound combo should only be used to select a value for
the bound fields.

 

[mckitlk]

I've just been checking some Access reference books. It appears from
these that Requery is basically a way of manually overriding a form's
default refresh interval, and pertains primarily to a multiuser
environment.

It looks to me like the form just won't work right with a Requery in
there anywhere, and Access already takes care of refreshing it
periodically anyway. I think I'll try leaving Requery out and see how
it performs with the users. If that doesn't work, I can go back to
using a filter.

Thanks.