is this wierd or is my code wrong...

  • Thread starter Thread starter Cire via AccessMonster.com
  • Start date Start date
C

Cire via AccessMonster.com

ok in a nutshell, i've got several combo boxes and textboxes in a form that
acts as a seach filter for a set of data. All criteria are optional so the
user can select any criteria he wants. The problem is i'm not capturing the
user input correctly...this is what i use:

'if combo box priority is not empty, capture user input
If Not IsNull(Me.cbx_priority.Value) Then
sqlmainstring = sqlmainstring & " AND request.chg.priority = " & quot
& Me.cbx_priority.Value & quot
End If

'if combo box prod category is not empty, capture user input
If Not IsNull(Me.cbx_prod_cat.Value) Then
sqlmainstring = sqlmainstring & " AND request.chgcat.sym = " & quot &
Me.cbx_prod_cat.Value & quot
End If

If IsNull(Me.cbx_priority.Value) And IsNull(Me.cbx_prod_cat.Value) Then
MsgBox ("You have not entered any criteria, please do so before
searching")

End If
sqlmainstring = sqlmainstring & " ORDER BY request.chg.id DESC "
mainqdf.Sql = sqlmainstring
set mainqdf = nothing

of course before all these is the main sql statemetn with the SELECT, FROM
and WHERE statements.
all these are under the on_click portion of a command button. Right now if i
leave the whole form blank, the msgbox doesnt appear!! instead the
sqlstatement with the request.chgcat.sym is being built even though the
condition is not satisfied!! Whats wrong??? i even tried changing the
statement sot if and elseif but it doesnt work...is that the right code?
there is no syntax errors within vba and that statement sounds totally
logical..
 
Cire said:
ok in a nutshell, i've got several combo boxes and textboxes in a form that
acts as a seach filter for a set of data. All criteria are optional so the
user can select any criteria he wants. The problem is i'm not capturing the
user input correctly...this is what i use:

'if combo box priority is not empty, capture user input
If Not IsNull(Me.cbx_priority.Value) Then
sqlmainstring = sqlmainstring & " AND request.chg.priority = " & quot
& Me.cbx_priority.Value & quot
End If

'if combo box prod category is not empty, capture user input
If Not IsNull(Me.cbx_prod_cat.Value) Then
sqlmainstring = sqlmainstring & " AND request.chgcat.sym = " & quot &
Me.cbx_prod_cat.Value & quot
End If

If IsNull(Me.cbx_priority.Value) And IsNull(Me.cbx_prod_cat.Value) Then
MsgBox ("You have not entered any criteria, please do so before
searching")

End If
sqlmainstring = sqlmainstring & " ORDER BY request.chg.id DESC "
mainqdf.Sql = sqlmainstring
set mainqdf = nothing

of course before all these is the main sql statemetn with the SELECT, FROM
and WHERE statements.
all these are under the on_click portion of a command button. Right now if i
leave the whole form blank, the msgbox doesnt appear!! instead the
sqlstatement with the request.chgcat.sym is being built even though the
condition is not satisfied!! Whats wrong??? i even tried changing the
statement sot if and elseif but it doesnt work...is that the right code?
there is no syntax errors within vba and that statement sounds totally
logical..
Click to expand...

something to add: do i have to put .requery in the cmd button for all the
combo and text boxes after the if else loops?

something like
with me
.cbx_priority.Requery
.cbx_prod_cat.Requery
End with
 
Cire said:
something to add: do i have to put .requery in the cmd button for all the
combo and text boxes after the if else loops?

something like
with me
.cbx_priority.Requery
.cbx_prod_cat.Requery
End with
Click to expand...

You haven't said anything that imploes the need for Requery.

I don't see why the message doesn't appear unless the combo
boxes really don't have a Null value. Try adding another
MsgBox at the top of the procedure to display the values of
the combo boxes.

Also add a MsgBox to display the entire SQL statement. The
syntax in "request.chgcat.sym" doesn't look right with those
two dots. There should be at most one dot as in
tablename.fieldname
 
Back
Top