Feeling dunb today can't get my head round a seemingl;y simple query...

Hi all,

I have a form with some unbound combo's and a list box that are used to
requery the dataset behind the form.

All was working fine until i added the list box.

I have the record set of the form looking at that value sin the combo
boxes and handling everything loverly but when i try to use the data
from the list box, it all goes a bit wierd...

I want someone to be able to have a blank in the list box and get
everything or click on one or more items in the list box and get those
items only... it's not difficult right??? But null's have left me in a
dilema and i'm struggling to get this working...

After much faffing about i have the following criteria in the list box
field selection:

Like IIf([forms]![frm_audit_form]![listbox] Is
Null,"*",[forms]![frm_audit_form]![listbox]) Or In
([forms]![frm_audit_form]![listbox])

So if I just use 'In' the list and the list is blank i don't get
everything...

I'm obviously missing the point and i'm sure i've had this working once
before at another company i worked at but I just can;t remember how...

Any ideas? Do you need more info to bea ble to help???

many Thanks,

Rob.

re: Feeling dunb today can't get my head round a seemingl;y simple query...

> After much faffing about i have the following criteria in the list box[color=blue]
> field selection:
>
> Like IIf([forms]![frm_audit_form]![listbox] Is
> Null,"*",[forms]![frm_audit_form]![listbox]) Or In
> ([forms]![frm_audit_form]![listbox])
> [/color]

Hi Rob,
Try to change the IIf(whatever Is Null, DoThis , DoThat) to
IIf(Isnull(whatever), this, that)
Also: Is your listbox.value Null or "" ??

You also could use some simple code to requery your data on the AfterUpdate_Event of your listbox
Private Sub Listbox_AfterUpdate(Cancel As Integer)
If IsNull(Me!Listbox)
Me.recordsource="QryShowAll"
Me.requery 'Not sure this is even needed here ...
Else
Me.recordsource="QryFilteredRecs"
Me.requery 'Not sure this is even needed here ...
End if
End Sub


--
Hope this helps
Arno R

re: Feeling dunb today can't get my head round a seemingl;y simple query...

Arno,

Good to hear from you.

The list box value can be null (i.e. blanked out) or one or more
values. Hmmm perhaps it isn't after all and i'm checking the wrong way
round...

I want the form to work with a refresh button (for the users sake)
rather than a lovely live update selection... This is set up and
working - i could add that code in there.

So i select from the various combo boxes (all handled and working
together fine) with the list box... However, it is something in the
logic of the query that is not working.

The query is the record source for the form and so i want to just put
in the right criteria to get the list box to be used in the query...
ack my brain hurts!

Rob.

re: Feeling dunb today can't get my head round a seemingl;y simple query...

Arno,

Good to hear from you.

The list box value can be null (i.e. blanked out) or one or more
values. Hmmm perhaps it isn't after all and i'm checking the wrong way
round...

I want the form to work with a refresh button (for the users sake)
rather than a lovely live update selection... This is set up and
working - i could add that code in there.

So i select from the various combo boxes (all handled and working
together fine) with the list box... However, it is something in the
logic of the query that is not working.

The query is the record source for the form and so i want to just put
in the right criteria to get the list box to be used in the query...
ack my brain hurts!

Rob.

re: Feeling dunb today can't get my head round a seemingl;y simple query...

If i debug.print Me!ListBox.Value during the refresh I get Null as the
Value no matter what values are in the listbox... I am obviously
missing something fundamental...

Rob.

re: Feeling dunb today can't get my head round a seemingl;y simple query...

dkintheuk wrote:[color=blue]
> If i debug.print Me!ListBox.Value during the refresh I get Null as the
> Value no matter what values are in the listbox... I am obviously
> missing something fundamental...
>
> Rob.[/color]

Is the Listbox Multi-Select set to None? If not then the value(s) are in
the .ItemsSelected property of the Listbox.

--
Bri

re: Feeling dunb today can't get my head round a seemingl;y simple query...

Oh well, i need to really read my typing more effectivly, missed out
one letter and it made all the difference... have now altered the way
i'm doing this but have a new problem. Will work out what is going on
and ask if i need to.

Thanks Arno.

Rob.