L
Leslie
I am developing a find function that uses a form with an
option group which then passes a SQL string to another form
where the user can view the results and select a record.
Originally I had only five option buttons and everything
was working fine but the user requested that he be able to
search in a couple of additional ways so I attempted to add
in the additional criteria by creating a new option group
with 7 options and adding a couple of new cases to my
Select Case statement. When I tried to compile the new
code it stops at case 7 and says it cannot compile because
there is a "case without a select case". This is not true.
I have tested it numerous ways: first by commenting out
the later cases one by one and trying to find where it was
failing. It seemed that the magic number it could still
process was Case 5. I tried nesting the later cases in a
new select case under "case else" (no go), and finally I
tried breaking the search out into two different forms, one
for a text search based on the first 5 options, and a
second based on the latter two options which involve
searching two numeric fields (the data type doesn't matter
though because it could search either numbers or text in my
original form). Now the "Find" form with the 5 cases seems
to work fine, but I seem to still have the same compile
error on the second form if the Select Case statement
contains more than one case. The only thing I can think of
now is that maybe some bit of the code got corrupted when I
was copying and pasting the new code. I have tried to
compact and repair the database but that hasn't helped.
Here is the code that fails in the hopes that someone can
see something that I am missing:
*********************************
Private Function FindRecords2()
On Error GoTo HandleErr
'If the user has typed or selected text in the Find Orders
dialog box then open the search
If Len(Me.cboSelect & "") > 0 Then
Dim rst As New ADODB.Recordset
'Construct SQL for ViewOrders Recordsource based on the
selected number
Select Case optChooseNumber
Case 1
'Invoice number
rst.Open "SELECT * FROM qryFindOrders WHERE Invoice
In (SELECT DISTINCTROW Invoice " _
& "FROM qryFindOrders " _
& "WHERE Invoice Like '%" &
DoubleQuote(Me![cboSelect]) & "%')", _
CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
'If the records have been searched and
there was no match, ask the user if they want to keep searching
If rst.EOF Then
If MsgBox("No Match. Cannot locate an
invoice like " & Me.cboSelect & "." & _
" Do you want to search
another way?", vbYesNo, "Invoice Not Found") = _
vbYes Then
Me.cboSelect.SetFocus
Else
Me.cmdClose.SetFocus
End If
Else
rst.MoveLast
If rst.RecordCount > 0 Then
'Open the search results form
DoCmd.OpenForm
"frmdlgFindSearchResults", acNormal, , , , acDialog, _
OpenArgs:="SELECT Invoice, Invoice,
BillFirstName, BillLastName, CCNu, Email, Month, " & _
"Day, Year FROM
qryFindOrders " & _
"WHERE Invoice Like '*" &
DoubleQuote(Me.cboSelect) & "*' ORDER BY " & _
"Invoice ASC"
End If
End If
Case 2
'Club Card number
rst.Open "SELECT * FROM qryFindOrders WHERE Invoice
In (SELECT DISTINCTROW Invoice " _
& "FROM qryFindOrders " _
& "WHERE CCNu Like '%" &
DoubleQuote(Me![cboSelect]) & "%')", _
CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
'If the records have been searched and
there was no match, ask the user if they want to keep searching
If rst.EOF Then
If MsgBox("No Match. Cannot locate a
Credit Card Number like " & Me.cboSelect & "." & _
" Do you want to search
another way?", vbYesNo, "Credit Card Not Found") = _
vbYes Then
Me.cboSelect.SetFocus
Else
Me.cmdClose.SetFocus
End If
Else
rst.MoveLast
If rst.RecordCount > 0 Then
'Open the search results form
DoCmd.OpenForm
"frmdlgFindSearchResults", acNormal, , , , acDialog, _
OpenArgs:="SELECT Invoice,
BillFirstName, BillLastName, Invoice, CCNu, Email, Month, " & _
"Day, Year FROM
qryFindOrders " & _
"WHERE CCNu Like '*" &
DoubleQuote(Me.cboSelect) & "*' ORDER BY " & _
"CCNu ASC"
End If
Case Else
End Select
End If
rst.Close
Set rst = Nothing
ExitHere:
Exit Function
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in FindRecords function"
End Select
Resume ExitHere
Resume
End Function
******************************************
I really appreciate your help!
Thanks
Leslie
option group which then passes a SQL string to another form
where the user can view the results and select a record.
Originally I had only five option buttons and everything
was working fine but the user requested that he be able to
search in a couple of additional ways so I attempted to add
in the additional criteria by creating a new option group
with 7 options and adding a couple of new cases to my
Select Case statement. When I tried to compile the new
code it stops at case 7 and says it cannot compile because
there is a "case without a select case". This is not true.
I have tested it numerous ways: first by commenting out
the later cases one by one and trying to find where it was
failing. It seemed that the magic number it could still
process was Case 5. I tried nesting the later cases in a
new select case under "case else" (no go), and finally I
tried breaking the search out into two different forms, one
for a text search based on the first 5 options, and a
second based on the latter two options which involve
searching two numeric fields (the data type doesn't matter
though because it could search either numbers or text in my
original form). Now the "Find" form with the 5 cases seems
to work fine, but I seem to still have the same compile
error on the second form if the Select Case statement
contains more than one case. The only thing I can think of
now is that maybe some bit of the code got corrupted when I
was copying and pasting the new code. I have tried to
compact and repair the database but that hasn't helped.
Here is the code that fails in the hopes that someone can
see something that I am missing:
*********************************
Private Function FindRecords2()
On Error GoTo HandleErr
'If the user has typed or selected text in the Find Orders
dialog box then open the search
If Len(Me.cboSelect & "") > 0 Then
Dim rst As New ADODB.Recordset
'Construct SQL for ViewOrders Recordsource based on the
selected number
Select Case optChooseNumber
Case 1
'Invoice number
rst.Open "SELECT * FROM qryFindOrders WHERE Invoice
In (SELECT DISTINCTROW Invoice " _
& "FROM qryFindOrders " _
& "WHERE Invoice Like '%" &
DoubleQuote(Me![cboSelect]) & "%')", _
CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
'If the records have been searched and
there was no match, ask the user if they want to keep searching
If rst.EOF Then
If MsgBox("No Match. Cannot locate an
invoice like " & Me.cboSelect & "." & _
" Do you want to search
another way?", vbYesNo, "Invoice Not Found") = _
vbYes Then
Me.cboSelect.SetFocus
Else
Me.cmdClose.SetFocus
End If
Else
rst.MoveLast
If rst.RecordCount > 0 Then
'Open the search results form
DoCmd.OpenForm
"frmdlgFindSearchResults", acNormal, , , , acDialog, _
OpenArgs:="SELECT Invoice, Invoice,
BillFirstName, BillLastName, CCNu, Email, Month, " & _
"Day, Year FROM
qryFindOrders " & _
"WHERE Invoice Like '*" &
DoubleQuote(Me.cboSelect) & "*' ORDER BY " & _
"Invoice ASC"
End If
End If
Case 2
'Club Card number
rst.Open "SELECT * FROM qryFindOrders WHERE Invoice
In (SELECT DISTINCTROW Invoice " _
& "FROM qryFindOrders " _
& "WHERE CCNu Like '%" &
DoubleQuote(Me![cboSelect]) & "%')", _
CurrentProject.Connection,
adOpenKeyset, adLockOptimistic
'If the records have been searched and
there was no match, ask the user if they want to keep searching
If rst.EOF Then
If MsgBox("No Match. Cannot locate a
Credit Card Number like " & Me.cboSelect & "." & _
" Do you want to search
another way?", vbYesNo, "Credit Card Not Found") = _
vbYes Then
Me.cboSelect.SetFocus
Else
Me.cmdClose.SetFocus
End If
Else
rst.MoveLast
If rst.RecordCount > 0 Then
'Open the search results form
DoCmd.OpenForm
"frmdlgFindSearchResults", acNormal, , , , acDialog, _
OpenArgs:="SELECT Invoice,
BillFirstName, BillLastName, Invoice, CCNu, Email, Month, " & _
"Day, Year FROM
qryFindOrders " & _
"WHERE CCNu Like '*" &
DoubleQuote(Me.cboSelect) & "*' ORDER BY " & _
"CCNu ASC"
End If
Case Else
End Select
End If
rst.Close
Set rst = Nothing
ExitHere:
Exit Function
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in FindRecords function"
End Select
Resume ExitHere
Resume
End Function
******************************************
I really appreciate your help!
Thanks
Leslie