Filtering on form using multiple combo boxes

G

Guest

Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo boxes and a
'Go' button to find (filter) records based on selections. Only modified
existing code in the new form to match new combo box names and related
fieldnames. On break, iget the error "Error '2001' - you cancelled the
previous operation" Debug points me to the following line near the bottom of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not IsNull(Me.cbo_CaseFileNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" & Me.cbo_ROCITSIssueNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote & Me.cbo_BeneficiaryLastName & Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not IsNull(Me.cbo_InquirerName) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName & "'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not IsNull(Me.cbo_MedicareNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out where. Any help
would be greatly appreciated! Thanks in advance.
 
T

tina

wow, that's a whole lot of code to return a recordset based on specified
criteria. is the form bound to a table? if so, suggest you build a SQL
statement for the form's RecordSource, using the Build button. you can set
criteria on the appropriate fields in the builder, as

[Forms]![NameOfForm]![NameOfControl] Or
[Forms]![NameOfForm]![NameOfControl] Is Null

the above goes all on one line in the criteria section of the field's
column, in the builder. substitute the correct name of the form, and name of
the control, of course. add code to the Go button to requery the form, as

Me.Requery

that's all the code you need on the button. add a "Clear" or "Reset" button
with code to reset the controls to Null and requery the form, as

Me!ControlName = Null
Me!AnotherControlName = Null
Me.Requery

you'll need a line of code to set *each* control's value back to Null, of
course. the result is that the user can make a selection from any
combination of the available combo box controls (and/or textbox controls),
then click the Go button, and the form's RecordSource is requeried to
present only the records matching the specified criteria. clicking the Clear
button returns all the records, with no criteria applied.

hth


FedBrad said:
Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo boxes and a
'Go' button to find (filter) records based on selections. Only modified
existing code in the new form to match new combo box names and related
fieldnames. On break, iget the error "Error '2001' - you cancelled the
previous operation" Debug points me to the following line near the bottom of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not IsNull(Me.cbo_CaseFileNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" & Me.cbo_ROCITSIssueNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote & Me.cbo_BeneficiaryLastName & Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not IsNull(Me.cbo_InquirerName) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName & "'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not IsNull(Me.cbo_MedicareNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out where. Any help
would be greatly appreciated! Thanks in advance.
Click to expand...
 
G

Guest

Thanks... I thought it seemed like more code than necessary as well but, as I
indicated, I was being lazy and used a form that already existed and seemed
to work fine.

And, the form's record source is a query - only because it displays one
field from a related table. But I assume I can still put the criteria into
the criteria field(s) in the query. True?

At any rate, I'll rewrite, and let you know... Thanks again!

Brad

tina said:
wow, that's a whole lot of code to return a recordset based on specified
criteria. is the form bound to a table? if so, suggest you build a SQL
statement for the form's RecordSource, using the Build button. you can set
criteria on the appropriate fields in the builder, as

[Forms]![NameOfForm]![NameOfControl] Or
[Forms]![NameOfForm]![NameOfControl] Is Null

the above goes all on one line in the criteria section of the field's
column, in the builder. substitute the correct name of the form, and name of
the control, of course. add code to the Go button to requery the form, as

Me.Requery

that's all the code you need on the button. add a "Clear" or "Reset" button
with code to reset the controls to Null and requery the form, as

Me!ControlName = Null
Me!AnotherControlName = Null
Me.Requery

you'll need a line of code to set *each* control's value back to Null, of
course. the result is that the user can make a selection from any
combination of the available combo box controls (and/or textbox controls),
then click the Go button, and the form's RecordSource is requeried to
present only the records matching the specified criteria. clicking the Clear
button returns all the records, with no criteria applied.

hth


FedBrad said:
Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo boxes and a
'Go' button to find (filter) records based on selections. Only modified
existing code in the new form to match new combo box names and related
fieldnames. On break, iget the error "Error '2001' - you cancelled the
previous operation" Debug points me to the following line near the bottom of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not IsNull(Me.cbo_CaseFileNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" & Me.cbo_ROCITSIssueNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote & Me.cbo_BeneficiaryLastName & Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not IsNull(Me.cbo_InquirerName) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName & "'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not IsNull(Me.cbo_MedicareNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out where. Any help
would be greatly appreciated! Thanks in advance.
Click to expand...
Click to expand...
 
T

tina

you're welcome, and yes, you should still be able to put the criteria, as
described, in the appropriate field(s) in the query.

hth


FedBrad said:
Thanks... I thought it seemed like more code than necessary as well but, as I
indicated, I was being lazy and used a form that already existed and seemed
to work fine.

And, the form's record source is a query - only because it displays one
field from a related table. But I assume I can still put the criteria into
the criteria field(s) in the query. True?

At any rate, I'll rewrite, and let you know... Thanks again!

Brad

tina said:
wow, that's a whole lot of code to return a recordset based on specified
criteria. is the form bound to a table? if so, suggest you build a SQL
statement for the form's RecordSource, using the Build button. you can set
criteria on the appropriate fields in the builder, as

[Forms]![NameOfForm]![NameOfControl] Or
[Forms]![NameOfForm]![NameOfControl] Is Null

the above goes all on one line in the criteria section of the field's
column, in the builder. substitute the correct name of the form, and name of
the control, of course. add code to the Go button to requery the form, as

Me.Requery

that's all the code you need on the button. add a "Clear" or "Reset" button
with code to reset the controls to Null and requery the form, as

Me!ControlName = Null
Me!AnotherControlName = Null
Me.Requery

you'll need a line of code to set *each* control's value back to Null, of
course. the result is that the user can make a selection from any
combination of the available combo box controls (and/or textbox controls),
then click the Go button, and the form's RecordSource is requeried to
present only the records matching the specified criteria. clicking the Clear
button returns all the records, with no criteria applied.

hth


FedBrad said:
Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo boxes and a
'Go' button to find (filter) records based on selections. Only modified
existing code in the new form to match new combo box names and related
fieldnames. On break, iget the error "Error '2001' - you cancelled the
previous operation" Debug points me to the following line near the
Click to expand...
bottom
of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not IsNull(Me.cbo_CaseFileNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" & Me.cbo_ROCITSIssueNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote & Me.cbo_BeneficiaryLastName
Click to expand...
&
Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not IsNull(Me.cbo_InquirerName) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName & "'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not IsNull(Me.cbo_MedicareNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out where. Any help
would be greatly appreciated! Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

So far, you're "da bomb"!!! But am still having a glitch. Sorry to bother
again.

The filter currently includes five separate combo boxes to choose from.
Three seem to work just fine (as well as the Reset button) - but two of them
still don't seem to function properly when I click the Go button.

One of them provides the hourglass (looking...), I get a quick flash, but
the records don't filter. Another simply causes the whole form to go blank
(like when there are no records...).

The RecordSource for all of the combo boxes concerned query the same table
as the form for existing field values. And, I have double-checked to be
certain the control names and fieldnames are accurate.

I initially entered the criteria into the field colmuns (all on first
criteria row of each field). After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid, adding
additional 'Or' lines - is that normal?


tina said:
you're welcome, and yes, you should still be able to put the criteria, as
described, in the appropriate field(s) in the query.

hth


FedBrad said:
Thanks... I thought it seemed like more code than necessary as well but, as I
indicated, I was being lazy and used a form that already existed and seemed
to work fine.

And, the form's record source is a query - only because it displays one
field from a related table. But I assume I can still put the criteria into
the criteria field(s) in the query. True?

At any rate, I'll rewrite, and let you know... Thanks again!

Brad

tina said:
wow, that's a whole lot of code to return a recordset based on specified
criteria. is the form bound to a table? if so, suggest you build a SQL
statement for the form's RecordSource, using the Build button. you can set
criteria on the appropriate fields in the builder, as

[Forms]![NameOfForm]![NameOfControl] Or
[Forms]![NameOfForm]![NameOfControl] Is Null

the above goes all on one line in the criteria section of the field's
column, in the builder. substitute the correct name of the form, and name of
the control, of course. add code to the Go button to requery the form, as

Me.Requery

that's all the code you need on the button. add a "Clear" or "Reset" button
with code to reset the controls to Null and requery the form, as

Me!ControlName = Null
Me!AnotherControlName = Null
Me.Requery

you'll need a line of code to set *each* control's value back to Null, of
course. the result is that the user can make a selection from any
combination of the available combo box controls (and/or textbox controls),
then click the Go button, and the form's RecordSource is requeried to
present only the records matching the specified criteria. clicking the Clear
button returns all the records, with no criteria applied.

hth


Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo boxes and a
'Go' button to find (filter) records based on selections. Only modified
existing code in the new form to match new combo box names and related
fieldnames. On break, iget the error "Error '2001' - you cancelled the
previous operation" Debug points me to the following line near the bottom
of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not IsNull(Me.cbo_CaseFileNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not
IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" & Me.cbo_ROCITSIssueNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote & Me.cbo_BeneficiaryLastName &
Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not IsNull(Me.cbo_InquirerName) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName & "'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not IsNull(Me.cbo_MedicareNumber) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out where. Any help
would be greatly appreciated! Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid, adding
additional 'Or' lines - is that normal?
Click to expand...

yes, it's normal.

re your problem with two of the combo box controls, you're not using Lookup
fields in your *table*, are you? if so, get rid of them! convert the fields
to "regular" Text or Number data type, as appropriate.

hth


FedBrad said:
So far, you're "da bomb"!!! But am still having a glitch. Sorry to bother
again.

The filter currently includes five separate combo boxes to choose from.
Three seem to work just fine (as well as the Reset button) - but two of them
still don't seem to function properly when I click the Go button.

One of them provides the hourglass (looking...), I get a quick flash, but
the records don't filter. Another simply causes the whole form to go blank
(like when there are no records...).

The RecordSource for all of the combo boxes concerned query the same table
as the form for existing field values. And, I have double-checked to be
certain the control names and fieldnames are accurate.

I initially entered the criteria into the field colmuns (all on first
criteria row of each field). After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid, adding
additional 'Or' lines - is that normal?


tina said:
you're welcome, and yes, you should still be able to put the criteria, as
described, in the appropriate field(s) in the query.

hth


FedBrad said:
Thanks... I thought it seemed like more code than necessary as well
Click to expand...
but,
as I
indicated, I was being lazy and used a form that already existed and seemed
to work fine.

And, the form's record source is a query - only because it displays one
field from a related table. But I assume I can still put the criteria into
the criteria field(s) in the query. True?

At any rate, I'll rewrite, and let you know... Thanks again!

Brad

:

wow, that's a whole lot of code to return a recordset based on specified
criteria. is the form bound to a table? if so, suggest you build a SQL
statement for the form's RecordSource, using the Build button. you
Click to expand...
can
set
criteria on the appropriate fields in the builder, as

[Forms]![NameOfForm]![NameOfControl] Or
[Forms]![NameOfForm]![NameOfControl] Is Null

the above goes all on one line in the criteria section of the field's
column, in the builder. substitute the correct name of the form, and name of
the control, of course. add code to the Go button to requery the
Click to expand...
form,
as
Me.Requery

that's all the code you need on the button. add a "Clear" or "Reset" button
with code to reset the controls to Null and requery the form, as

Me!ControlName = Null
Me!AnotherControlName = Null
Me.Requery

you'll need a line of code to set *each* control's value back to
Click to expand...
Null,
of
course. the result is that the user can make a selection from any
combination of the available combo box controls (and/or textbox controls),
then click the Go button, and the form's RecordSource is requeried to
present only the records matching the specified criteria. clicking
Click to expand...
the
Clear
button returns all the records, with no criteria applied.

hth


Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo boxes
Click to expand...
and
a
'Go' button to find (filter) records based on selections. Only modified
existing code in the new form to match new combo box names and related
fieldnames. On break, iget the error "Error '2001' - you
Click to expand...
cancelled
the
previous operation" Debug points me to the following line near
Click to expand...
the
bottom
of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not
Click to expand...
IsNull(Me.cbo_CaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not
IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" & Me.cbo_ROCITSIssueNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote &
Click to expand...
Me.cbo_BeneficiaryLastName
&
Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not IsNull(Me.cbo_InquirerName) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName & "'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not
Click to expand...
IsNull(Me.cbo_MedicareNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out where.
Click to expand...
Any
help
would be greatly appreciated! Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Nope... One of them is a number field (a old record ID field migrated from an
older db), so that users can still find a record by its old ID number; and
the other is a text field containing a Last name entry in existing records.

Hmmmm......

tina said:
After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid, adding
additional 'Or' lines - is that normal?
Click to expand...

yes, it's normal.

re your problem with two of the combo box controls, you're not using Lookup
fields in your *table*, are you? if so, get rid of them! convert the fields
to "regular" Text or Number data type, as appropriate.

hth


FedBrad said:
So far, you're "da bomb"!!! But am still having a glitch. Sorry to bother
again.

The filter currently includes five separate combo boxes to choose from.
Three seem to work just fine (as well as the Reset button) - but two of them
still don't seem to function properly when I click the Go button.

One of them provides the hourglass (looking...), I get a quick flash, but
the records don't filter. Another simply causes the whole form to go blank
(like when there are no records...).

The RecordSource for all of the combo boxes concerned query the same table
as the form for existing field values. And, I have double-checked to be
certain the control names and fieldnames are accurate.

I initially entered the criteria into the field colmuns (all on first
criteria row of each field). After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid, adding
additional 'Or' lines - is that normal?


tina said:
you're welcome, and yes, you should still be able to put the criteria, as
described, in the appropriate field(s) in the query.

hth


Thanks... I thought it seemed like more code than necessary as well but,
as I
indicated, I was being lazy and used a form that already existed and
seemed
to work fine.

And, the form's record source is a query - only because it displays one
field from a related table. But I assume I can still put the criteria
into
the criteria field(s) in the query. True?

At any rate, I'll rewrite, and let you know... Thanks again!

Brad

:

wow, that's a whole lot of code to return a recordset based on specified
criteria. is the form bound to a table? if so, suggest you build a SQL
statement for the form's RecordSource, using the Build button. you can
set
criteria on the appropriate fields in the builder, as

[Forms]![NameOfForm]![NameOfControl] Or
[Forms]![NameOfForm]![NameOfControl] Is Null

the above goes all on one line in the criteria section of the field's
column, in the builder. substitute the correct name of the form, and
name of
the control, of course. add code to the Go button to requery the form,
as

Me.Requery

that's all the code you need on the button. add a "Clear" or "Reset"
button
with code to reset the controls to Null and requery the form, as

Me!ControlName = Null
Me!AnotherControlName = Null
Me.Requery

you'll need a line of code to set *each* control's value back to Null,
of
course. the result is that the user can make a selection from any
combination of the available combo box controls (and/or textbox
controls),
then click the Go button, and the form's RecordSource is requeried to
present only the records matching the specified criteria. clicking the
Clear
button returns all the records, with no criteria applied.

hth


Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo boxes and
a
'Go' button to find (filter) records based on selections. Only
modified
existing code in the new form to match new combo box names and related
fieldnames. On break, iget the error "Error '2001' - you cancelled
the
previous operation" Debug points me to the following line near the
bottom
of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not IsNull(Me.cbo_CaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not
IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not
IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" & Me.cbo_ROCITSIssueNumber & "'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote & Me.cbo_BeneficiaryLastName
&
Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not IsNull(Me.cbo_InquirerName) Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName & "'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not IsNull(Me.cbo_MedicareNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out where. Any
help
would be greatly appreciated! Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

the only thing i can suggest is that you doublecheck the criteria on those
two fields to make sure it's correctly referring to the form name and
control names, then.

hth


FedBrad said:
Nope... One of them is a number field (a old record ID field migrated from an
older db), so that users can still find a record by its old ID number; and
the other is a text field containing a Last name entry in existing records.
Hmmmm......

tina said:
After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid, adding
additional 'Or' lines - is that normal?
Click to expand...

yes, it's normal.

re your problem with two of the combo box controls, you're not using Lookup
fields in your *table*, are you? if so, get rid of them! convert the fields
to "regular" Text or Number data type, as appropriate.

hth


FedBrad said:
So far, you're "da bomb"!!! But am still having a glitch. Sorry to bother
again.

The filter currently includes five separate combo boxes to choose from.
Three seem to work just fine (as well as the Reset button) - but two
Click to expand...
of
them
still don't seem to function properly when I click the Go button.

One of them provides the hourglass (looking...), I get a quick flash, but
the records don't filter. Another simply causes the whole form to go blank
(like when there are no records...).

The RecordSource for all of the combo boxes concerned query the same table
as the form for existing field values. And, I have double-checked to be
certain the control names and fieldnames are accurate.

I initially entered the criteria into the field colmuns (all on first
criteria row of each field). After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid, adding
additional 'Or' lines - is that normal?


:

you're welcome, and yes, you should still be able to put the
Click to expand...
criteria,
as
described, in the appropriate field(s) in the query.

hth


Thanks... I thought it seemed like more code than necessary as
Click to expand...
well
but,
as I
indicated, I was being lazy and used a form that already existed and
seemed
to work fine.

And, the form's record source is a query - only because it
Click to expand...
displays
one
field from a related table. But I assume I can still put the criteria
into
the criteria field(s) in the query. True?

At any rate, I'll rewrite, and let you know... Thanks again!

Brad

:

wow, that's a whole lot of code to return a recordset based on specified
criteria. is the form bound to a table? if so, suggest you build
Click to expand...
a
SQL
statement for the form's RecordSource, using the Build button.
Click to expand...
you
can
set
criteria on the appropriate fields in the builder, as

[Forms]![NameOfForm]![NameOfControl] Or
[Forms]![NameOfForm]![NameOfControl] Is Null

the above goes all on one line in the criteria section of the field's
column, in the builder. substitute the correct name of the form, and
name of
the control, of course. add code to the Go button to requery the form,
as

Me.Requery

that's all the code you need on the button. add a "Clear" or "Reset"
button
with code to reset the controls to Null and requery the form, as

Me!ControlName = Null
Me!AnotherControlName = Null
Me.Requery

you'll need a line of code to set *each* control's value back to Null,
of
course. the result is that the user can make a selection from any
combination of the available combo box controls (and/or textbox
controls),
then click the Go button, and the form's RecordSource is
Click to expand...
requeried
to
present only the records matching the specified criteria.
Click to expand...
clicking
the
Clear
button returns all the records, with no criteria applied.

hth


Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo
Click to expand...
boxes
and
a
'Go' button to find (filter) records based on selections. Only
modified
existing code in the new form to match new combo box names and related
fieldnames. On break, iget the error "Error '2001' - you cancelled
the
previous operation" Debug points me to the following line
Click to expand...
near
the
bottom
of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not IsNull(Me.cbo_CaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber
Click to expand...
&
"'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not
IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber
Click to expand...
&
"'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not
IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" &
Click to expand...
Me.cbo_ROCITSIssueNumber &
"'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote & Me.cbo_BeneficiaryLastName
&
Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not
Click to expand...
IsNull(Me.cbo_InquirerName)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName
Click to expand...
&
"'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not IsNull(Me.cbo_MedicareNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out
Click to expand...
where.
Any
help
would be greatly appreciated! Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Okay, thanks... I w-a-s burning some oil.

tina said:
the only thing i can suggest is that you doublecheck the criteria on those
two fields to make sure it's correctly referring to the form name and
control names, then.

hth


FedBrad said:
Nope... One of them is a number field (a old record ID field migrated from an
older db), so that users can still find a record by its old ID number; and
the other is a text field containing a Last name entry in existing records.

Hmmmm......

tina said:
After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid,
adding
additional 'Or' lines - is that normal?

yes, it's normal.

re your problem with two of the combo box controls, you're not using Lookup
fields in your *table*, are you? if so, get rid of them! convert the fields
to "regular" Text or Number data type, as appropriate.

hth


So far, you're "da bomb"!!! But am still having a glitch. Sorry to
bother
again.

The filter currently includes five separate combo boxes to choose from.
Three seem to work just fine (as well as the Reset button) - but two of
them
still don't seem to function properly when I click the Go button.

One of them provides the hourglass (looking...), I get a quick flash, but
the records don't filter. Another simply causes the whole form to go
blank
(like when there are no records...).

The RecordSource for all of the combo boxes concerned query the same table
as the form for existing field values. And, I have double-checked to be
certain the control names and fieldnames are accurate.

I initially entered the criteria into the field colmuns (all on first
criteria row of each field). After saving then returning, I noticed that
Access added each of those fields again to the end of the query grid,
adding
additional 'Or' lines - is that normal?


:

you're welcome, and yes, you should still be able to put the criteria,
as
described, in the appropriate field(s) in the query.

hth


Thanks... I thought it seemed like more code than necessary as well
but,
as I
indicated, I was being lazy and used a form that already existed and
seemed
to work fine.

And, the form's record source is a query - only because it displays
one
field from a related table. But I assume I can still put the criteria
into
the criteria field(s) in the query. True?

At any rate, I'll rewrite, and let you know... Thanks again!

Brad

:

wow, that's a whole lot of code to return a recordset based on
specified
criteria. is the form bound to a table? if so, suggest you build a
SQL
statement for the form's RecordSource, using the Build button. you
can
set
criteria on the appropriate fields in the builder, as

[Forms]![NameOfForm]![NameOfControl] Or
[Forms]![NameOfForm]![NameOfControl] Is Null

the above goes all on one line in the criteria section of the
field's
column, in the builder. substitute the correct name of the form, and
name of
the control, of course. add code to the Go button to requery the
form,
as

Me.Requery

that's all the code you need on the button. add a "Clear" or "Reset"
button
with code to reset the controls to Null and requery the form, as

Me!ControlName = Null
Me!AnotherControlName = Null
Me.Requery

you'll need a line of code to set *each* control's value back to
Null,
of
course. the result is that the user can make a selection from any
combination of the available combo box controls (and/or textbox
controls),
then click the Go button, and the form's RecordSource is requeried
to
present only the records matching the specified criteria. clicking
the
Clear
button returns all the records, with no criteria applied.

hth


Rats! After much fiddling, I obviusly need help with this.

Copied existing form (works fine), which uses multiple combo boxes
and
a
'Go' button to find (filter) records based on selections. Only
modified
existing code in the new form to match new combo box names and
related
fieldnames. On break, iget the error "Error '2001' - you
cancelled
the
previous operation" Debug points me to the following line near
the
bottom
of
the Sub:

Me.FilterOn = True

Following is the code concerned:

Private Sub cmdGo_Click()
Dim TheFilter As String
Dim FilterStarted As Boolean
Dim Quote As String
Quote = Chr$(34)
'Dummy filter for all records
TheFilter = "[BeneLName] Like " & Quote & "*" & Quote
'When FilterStarted is true, the string needs an AND next
FilterStarted = False
Me.DataEntry = False
Call ShowForm
Me.NavigationButtons = True
Me.cmdFindCase.Visible = False
Me.cmdDeleteCase.Visible = True
Me.cmdSaveClose.Visible = True
Me.cmdPrintCase.Visible = True
'This builds a filter based on what they enter
If Me.cbo_CaseFileNumber <> "" And Not
IsNull(Me.cbo_CaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquiryID] = '" &
Me.cbo_CaseFileNumber & "'"
Else
TheFilter = "[InquiryID] = '" & Me.cbo_CaseFileNumber &
"'"
FilterStarted = True
End If
End If
If Me.cbo_BradCaseFileNumber <> "" And Not
IsNull(Me.cbo_BradCaseFileNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BRADID] = '" &
Me.cbo_BradCaseFileNumber & "'"
Else
TheFilter = "[BRADID] = '" & Me.cbo_BradCaseFileNumber &
"'"
FilterStarted = True
End If
End If
If Me.cbo_ROCITSIssueNumber <> "" And Not
IsNull(Me.cbo_ROCITSIssueNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [ROCITSID] = '" &
Me.cbo_ROCITSIssueNumber & "'"
Else
TheFilter = "[ROCITSID] = '" & Me.cbo_ROCITSIssueNumber &
"'"
FilterStarted = True
End If
End If
If Me.cbo_BeneficiaryLastName <> "" And Not
IsNull(Me.cbo_BeneficiaryLastName) Then
TheFilter = "[BeneLName] = " & Quote &
Me.cbo_BeneficiaryLastName
&
Quote
FilterStarted = True
End If
If Me.cbo_InquirerName <> "" And Not IsNull(Me.cbo_InquirerName)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [InquirerName] = '" &
Me.cbo_InquirerName & "'"
Else
TheFilter = "[InquirerName] = '" & Me.cbo_InquirerName &
"'"
FilterStarted = True
End If
End If
If Me.cbo_MedicareNumber <> "" And Not
IsNull(Me.cbo_MedicareNumber)
Then
If FilterStarted = True Then
TheFilter = TheFilter & " AND [BeneHICN] = '" &
Me.cbo_MedicareNumber & "'"
Else
TheFilter = "[BeneHICN] = '" & Me.cbo_MedicareNumber & "'"
FilterStarted = True
End If
End If
Me.FilterOn = True
DoCmd.ApplyFilter , TheFilter
If Me.CurrentRecord = 0 Then
MsgBox "Your search returned no records. Please try again"
FilterOn = False
Me.cmdGo.SetFocus
Call HideForm
Else
Call ShowForm
Me.InquiryID.SetFocus
End If

End Sub

I clearly need to modify something, but can't figure out where.
Any
help
would be greatly appreciated! Thanks in advance.
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

Filtering and Removing Filter based on a combo box 4
Runtime error 2001 2
Filtered field goes blank 2
Using two combo boxes to filter a form 1
Creating a filter using VBA 5
Using combo box to search form 4
2 Combo Box Filter for a Form 1
Split Form filter problem 4

Top