Combo box null value

[Guest]

I seem to be getting caught on all the smaller issue. Sorry if this is too
easy, but...

This question is broken into two parts:
I have a form with three criteria, selectable by one combo box each. When
the criteria is filled out the user operates a command button which opens a
form and list items filtered by the criteria.
I have an IF loop that monitors the values of the criteria combo box and
passes a string to the next form that edits the list box RowSource property.

Question 1:
I use the statment

If (Me.Combo_Plant.Value = Null) Then
QueryPass = "qry_All_DB"
else if ..... more code

QueryPass is a public variable defined as a string.

but even if the value in the combo box is null (as indiciated by the
immediate window) the code jumps this step. i.e. does not acknowledge null
value

Question 2:
Is passing as public variable between two forms the best solution? To use
this method I have to set a query up for each different incident of criteria.
i.e.
all criteria null - 1 query, 1 criteria filled - another query, criteria
filled - another query, all criteria filled - antoher query

Would it be better to pass and SQL statement to the list box so it builds
its own query each time? Which is faster and easier to code?

Thank-you for any help

 

[John Vinson]

I seem to be getting caught on all the smaller issue. Sorry if this is too
easy, but...

This question is broken into two parts:
I have a form with three criteria, selectable by one combo box each. When
the criteria is filled out the user operates a command button which opens a
form and list items filtered by the criteria.
I have an IF loop that monitors the values of the criteria combo box and
passes a string to the next form that edits the list box RowSource property.

Question 1:
I use the statment

If (Me.Combo_Plant.Value = Null) Then
QueryPass = "qry_All_DB"
else if ..... more code

QueryPass is a public variable defined as a string.

NULL is a funny beast. It means "This value is undefined, unspecified,
unknown". As such, nothing is equal to NULL - or for that matter
*unequal* to NULL. Any logical expression comparing something to NULL
is neither true nor false - it's NULL (which gets treated as if it
were false).

Instead, use the builtin IsNull function:

If IsNull(Me!Combo_Plant) Then
...
End IF

Question 2:
Is passing as public variable between two forms the best solution? To use
this method I have to set a query up for each different incident of criteria.
i.e.
all criteria null - 1 query, 1 criteria filled - another query, criteria
filled - another query, all criteria filled - antoher query

Would it be better to pass and SQL statement to the list box so it builds
its own query each time? Which is faster and easier to code?

I'd do the latter. Just build the WHERE clause of the query in your
code as a string:

Dim strSQL As String
strSQL = "SELECT blah blah blah FROM thistable INNER JOIN thattable" _
& " ON thistable.ID = thattable.ID WHERE True"
If Not IsNull(Me!Combo_Plant) Then
strSQL = strSQL & " AND [Plant] = '" & Me!Combo_Plant & "'"
End IF
If Not IsNull(Me!Combo_xyz) Then
strSQL = strSQL & " AND [XYZ] = " & Me!Combo_xyz)
End If
<etc etc>

Carefully keep track of blanks (note the " AND has a blank before the
word AND) and delimiters; Text fields must be delimited by ' or ",
Date/Time fields by #, and number fields must have no delimiter.

John W. Vinson[MVP]

 

[Guest]

What would be the best way of 'passing' the strSQL string to the list box in
the new window.
Do I open the new form, then set the RecordSource propert using the
me.listboxa.RecordSource?

What is the best way of passing information (generally rule) between forms
if you want to close the previous form after it opens the new form?
i.e. in this case, get criteria, open information form, pass criteria filter
via SQL (?), and then close criteria form?

And thank-you for your last post. You know your stuff!

I seem to be getting caught on all the smaller issue. Sorry if this is too
easy, but...

This question is broken into two parts:
I have a form with three criteria, selectable by one combo box each. When
the criteria is filled out the user operates a command button which opens a
form and list items filtered by the criteria.
I have an IF loop that monitors the values of the criteria combo box and
passes a string to the next form that edits the list box RowSource property.

Question 1:
I use the statment

If (Me.Combo_Plant.Value = Null) Then
QueryPass = "qry_All_DB"
else if ..... more code

QueryPass is a public variable defined as a string.

NULL is a funny beast. It means "This value is undefined, unspecified,
unknown". As such, nothing is equal to NULL - or for that matter
*unequal* to NULL. Any logical expression comparing something to NULL
is neither true nor false - it's NULL (which gets treated as if it
were false).

Instead, use the builtin IsNull function:

If IsNull(Me!Combo_Plant) Then
...
End IF

Question 2:
Is passing as public variable between two forms the best solution? To use
this method I have to set a query up for each different incident of criteria.
i.e.
all criteria null - 1 query, 1 criteria filled - another query, criteria
filled - another query, all criteria filled - antoher query

Would it be better to pass and SQL statement to the list box so it builds
its own query each time? Which is faster and easier to code?

I'd do the latter. Just build the WHERE clause of the query in your
code as a string:

Dim strSQL As String
strSQL = "SELECT blah blah blah FROM thistable INNER JOIN thattable" _
& " ON thistable.ID = thattable.ID WHERE True"
If Not IsNull(Me!Combo_Plant) Then
strSQL = strSQL & " AND [Plant] = '" & Me!Combo_Plant & "'"
End IF
If Not IsNull(Me!Combo_xyz) Then
strSQL = strSQL & " AND [XYZ] = " & Me!Combo_xyz)
End If
<etc etc>

Carefully keep track of blanks (note the " AND has a blank before the
word AND) and delimiters; Text fields must be delimited by ' or ",
Date/Time fields by #, and number fields must have no delimiter.

John W. Vinson[MVP]

 

[John Vinson]

What would be the best way of 'passing' the strSQL string to the list box in
the new window.
Do I open the new form, then set the RecordSource propert using the
me.listboxa.RecordSource?

RowSource actually - RecordSource is for forms, RowSource for list and
combo boxes. You can use

Forms!otherform!listboxa.RowSource = strSQL

or open the otherform and use Me!; the former is simpler because you
don't need a non-local variable.

What is the best way of passing information (generally rule) between forms
if you want to close the previous form after it opens the new form?
i.e. in this case, get criteria, open information form, pass criteria filter
via SQL (?), and then close criteria form?

One sneaky way is to, not close the popup form, but set its Visible
property to False. This leaves the contents of all the controls on the
form available for code, but prevents the user seeing it and also
resumes code execution if you've opened it in Dialog mode. You can use

DoCmd.Close acForm, "frmCrit"

to close the form when you're done with it.

And thank-you for your last post. You know your stuff!

Thanks - glad to be of assistance!

John W. Vinson[MVP]

 

[Guest]

Just a side note, what is the exact difference between:
Forms!otherform!listboxa.RowSource = strSQL
and
Forms!otherform.listboxa.RowSource = strSQL

i.e ! and .

 

[John Vinson]

Just a side note, what is the exact difference between:
Forms!otherform!listboxa.RowSource = strSQL
and
Forms!otherform.listboxa.RowSource = strSQL

i.e ! and .

In practice... not too much. The ! delimiter is used to select a
member of a Collection; so Forms!otherform means "select the member
named Otherform from the Forms collection", and otherform!listboxa
means "select the object named listboxa from the default collection of
the otherform object" - as it happens, the default Collection of a
Form object is its Controls collection.

The . operator specifies a Method of an object - this can be used to
actually execute a process, but the default Method of a form object is
to return the cited memeber of its Controls collection, so you get the
same outcome.


John W. Vinson[MVP]