Combo box filter

M

Mary

I have the following code on the OnClick event of a command button on a Form
to restrict the results of a report.

Private Sub Command24_Click()
Dim strWhere As String
On Error GoTo ErrorHandler

If Not IsNull(Me.Director) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Director] = '" & Me.Director & "'"
End If

If Not IsNull(Me.ProjectName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ProjectName] = '" & Me.ProjectName & "'"
End If

If Not IsNull(Me.Vendor) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Vendor] = '" & Me.Vendor & "'"
End If

DoCmd.OpenReport "SSExpDtl", acViewPreview, , strWhere

GetOut:
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & _
Err.Description
Resume GetOut
End Select
End Sub

I have added a new combo box to the form to further limit the results based
on a Yes/No field named Active. I'm having trouble getting the desired
results. Where would I add that piece in the code above. I also have the
default value of the Active control set to Yes, and the Format to Yes/No.

BTW - I got that code from this newsgroup and it's been working great.

Thanks!
 
M

Mary

Addl info...

I'd also like to add ALL to the list in the combo box. I currently have two
columns so it will display "Open" and "Closed". I removed the Yes/No format
mentioned below.

SELECT DISTINCT [Expense Details].Active, IIf([Active]=Yes,"Open","Closed")
AS Status FROM [Expense Details];

I know I need to use UNION SELECT "All" for this, but not sure how to do it
with two columns the way I have them formatted.

Still need help with original question - my report isn't recognizing the
entry in this Active field, pulling all records instead of based on selection
in that field.

Appreciate any help you can offer.

Thanks,
Mary
 
M

Mr B

Mary,

You can add the new code just below the code for the Vendor. Something like:

if me.NameOfYesNoControl = 0 then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = 0"
else
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = -1"
endif

The code above is "Air code" and not tested. Replace the
"NameOfYesNoControl" with the actual name of your control. You may need to
make some modifications to it to accomplish what you want. This code assumes
that there will always be only two values available from the Active combo box
and that it cannot be left blank. If this control can be left blank or in a
null state, you can wraper this code with a line of code like the ones you
already have for other controls"

If not isnull(me.NameOfYesNoControl) then
'Your code .....
endif

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com
 
M

Mary

Thanks so much, works like a charm!

Mr B said:
Mary,

You can add the new code just below the code for the Vendor. Something like:

if me.NameOfYesNoControl = 0 then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = 0"
else
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = -1"
endif

The code above is "Air code" and not tested. Replace the
"NameOfYesNoControl" with the actual name of your control. You may need to
make some modifications to it to accomplish what you want. This code assumes
that there will always be only two values available from the Active combo box
and that it cannot be left blank. If this control can be left blank or in a
null state, you can wraper this code with a line of code like the ones you
already have for other controls"

If not isnull(me.NameOfYesNoControl) then
'Your code .....
endif

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com


Mary said:
I have the following code on the OnClick event of a command button on a Form
to restrict the results of a report.

Private Sub Command24_Click()
Dim strWhere As String
On Error GoTo ErrorHandler

If Not IsNull(Me.Director) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Director] = '" & Me.Director & "'"
End If

If Not IsNull(Me.ProjectName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ProjectName] = '" & Me.ProjectName & "'"
End If

If Not IsNull(Me.Vendor) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Vendor] = '" & Me.Vendor & "'"
End If

DoCmd.OpenReport "SSExpDtl", acViewPreview, , strWhere

GetOut:
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & _
Err.Description
Resume GetOut
End Select
End Sub

I have added a new combo box to the form to further limit the results based
on a Yes/No field named Active. I'm having trouble getting the desired
results. Where would I add that piece in the code above. I also have the
default value of the Active control set to Yes, and the Format to Yes/No.

BTW - I got that code from this newsgroup and it's been working great.

Thanks!
Click to expand...
Click to expand...
 
M

Mr B

Your quite welcom, glad to help
--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com


Mary said:
Thanks so much, works like a charm!

Mr B said:
Mary,

You can add the new code just below the code for the Vendor. Something like:

if me.NameOfYesNoControl = 0 then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = 0"
else
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Actibve] = -1"
endif

The code above is "Air code" and not tested. Replace the
"NameOfYesNoControl" with the actual name of your control. You may need to
make some modifications to it to accomplish what you want. This code assumes
that there will always be only two values available from the Active combo box
and that it cannot be left blank. If this control can be left blank or in a
null state, you can wraper this code with a line of code like the ones you
already have for other controls"

If not isnull(me.NameOfYesNoControl) then
'Your code .....
endif

--
HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com


Mary said:
I have the following code on the OnClick event of a command button on a Form
to restrict the results of a report.

Private Sub Command24_Click()
Dim strWhere As String
On Error GoTo ErrorHandler

If Not IsNull(Me.Director) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Director] = '" & Me.Director & "'"
End If

If Not IsNull(Me.ProjectName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ProjectName] = '" & Me.ProjectName & "'"
End If

If Not IsNull(Me.Vendor) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[Vendor] = '" & Me.Vendor & "'"
End If

DoCmd.OpenReport "SSExpDtl", acViewPreview, , strWhere

GetOut:
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox Err.Number & ": " & _
Err.Description
Resume GetOut
End Select
End Sub

I have added a new combo box to the form to further limit the results based
on a Yes/No field named Active. I'm having trouble getting the desired
results. Where would I add that piece in the code above. I also have the
default value of the Active control set to Yes, and the Format to Yes/No.

BTW - I got that code from this newsgroup and it's been working great.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form to pass strWhere to open report 2
Search Form with a SubForm 3
Filtering a multiple item form 1
Error on modified Allenbrowne SearchForm 6
Modified Allenbrowne Search Form displays error 2
Require a field in search form 3
Search a word 12
runtime error 3075 2

Top