Custom Filter

[Keith]

A2003, XP Pro SP2.

I'm developing a simple custom filter form for a client and keep running
into the same error. I've posted a simplified version in my web space at

http://keith.wilby.users.btopenworld.com/dbase/db4.zip

Try this:

Open frmTest, then open fdlgFilter.
Select DateType from the first field list combo then select a value in
the value list combo. Apply the filter - it works!
Now unhide fdlgFilter and change the field selected to TextType then try
to select a value. See the error? "The value you entered isn't valid
....", so the combo on the right still thinks its data type is "Date".

I'm sure that, like me, this is going to be something simple and daft
but I just can't fathom what. Any help or pointers greatly appreciated
as always.

Regards,
Keith.

[Justin Hoffman]

"Keith" <ke***@NOCARPkeithwilby.org.uk> wrote in message
news:d8**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

A2003, XP Pro SP2.

I'm developing a simple custom filter form for a client and keep running
into the same error. I've posted a simplified version in my web space at

http://keith.wilby.users.btopenworld.com/dbase/db4.zip

Try this:

Open frmTest, then open fdlgFilter.
Select DateType from the first field list combo then select a value in the
value list combo. Apply the filter - it works!
Now unhide fdlgFilter and change the field selected to TextType then try
to select a value. See the error? "The value you entered isn't valid ...",
so the combo on the right still thinks its data type is "Date".

I'm sure that, like me, this is going to be something simple and daft but
I just can't fathom what. Any help or pointers greatly appreciated as
always.

Regards,
Keith.

If you set the recordsource of the combo to:
SELECT "" AS X FROM MSysObjects WHERE 1=0
when you are in design view, this may rid you of this immediate problem,
telling the combobox to expect text. Just make sure you're careful around
sql strings, dates and regional settings.

I guess you are ultimately going somewhere where the built in filter-by-form
functionality can't be used, but it's extremely flexible, tested and
hopefully bug-free.

[Keith]

Justin Hoffman wrote:

If you set the recordsource of the combo to:
SELECT "" AS X FROM MSysObjects WHERE 1=0
when you are in design view, this may rid you of this immediate problem,
telling the combobox to expect text. Just make sure you're careful around
sql strings, dates and regional settings.

I guess you are ultimately going somewhere where the built in filter-by-form
functionality can't be used, but it's extremely flexible, tested and
hopefully bug-free.

I can't thank you enough for this Justin, you're a star! I don't
suppose you could explain exactly why this fix works?

I've tried to persuade my client to use filter-by-form but he's quite
adamant that this is what his company wants.

Regards,
Keith.

[Ozzone]

Why are you binding the filter form to the table?

[Keith]

Ozzone wrote:

Why are you binding the filter form to the table?

In the "real" one I'm not, I'm just trying to replicate the error with
this. In the actual app the filter form's record source is Forms(0)
because only one form will be open at any given time. Why do you ask?

[Justin Hoffman]

"Keith" <ke***@NOCARPkeithwilby.org.uk> wrote in message
news:d8**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

Justin Hoffman wrote:

If you set the recordsource of the combo to:
SELECT "" AS X FROM MSysObjects WHERE 1=0
when you are in design view, this may rid you of this immediate problem,
telling the combobox to expect text. Just make sure you're careful
around sql strings, dates and regional settings.

I guess you are ultimately going somewhere where the built in
filter-by-form functionality can't be used, but it's extremely flexible,
tested and hopefully bug-free.

I can't thank you enough for this Justin, you're a star! I don't suppose
you could explain exactly why this fix works?

I've tried to persuade my client to use filter-by-form but he's quite
adamant that this is what his company wants.

Regards,
Keith.

Hi Keith
All this does is create a query based on a system table which returns no
rows and is only one field wide. In fact you could have used any table to
replace MSysObjects in the query, the point is that the calculated field (X)
is text and so right from the start the combobox is not expecting any
special format (like numbers or dates). It will accept anything provided it
can be represented as text - and afterall - what can't?
Of course the customer is always right and all that, but since you already
have a fixed number of comboboxes, I would be tempted to fix the fields they
represent. Normally when I build something like this I would have, say the
SomeDate field shown in a fixed position on that form. It allows me to have
special functions (like having a 'Today' button or showing a calendar)
dedicated to selecting from this particular field. You also might have
txtFromDate and txtToDate for date criteria whereas for other fields a
single control may be all that's required, eg chkSendNoEmail.

[Ozzone]

Just wondering is all. Justin offered a good solution.

[Keith]

"Justin Hoffman" <[e-mail]> wrote in message
news:d8**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

All this does is create a query based on a system table which returns no
rows and is only one field wide. In fact you could have used any table to
replace MSysObjects in the query, the point is that the calculated field
(X) is text and so right from the start the combobox is not expecting any
special format (like numbers or dates). It will accept anything provided
it can be represented as text - and afterall - what can't?
It seems so obvious now you've explained it, many thanks.
Of course the customer is always right and all that, but since you already
have a fixed number of comboboxes, I would be tempted to fix the fields
they represent. Normally when I build something like this I would have,
say the SomeDate field shown in a fixed position on that form. It allows
me to have special functions (like having a 'Today' button or showing a
calendar) dedicated to selecting from this particular field. You also
might have txtFromDate and txtToDate for date criteria whereas for other
fields a single control may be all that's required, eg chkSendNoEmail.

All good suggestions but I think the idea is to have this form used by
whatever main form is opened at the time, hence the data source in the
'real' app is that for Forms(0).

Once again Justin, many thanks for helping me out on this one.

Keith.