Help!!!

[giacomo1]

Help!!!!


Here's the rundown...

Have a table called "general" built a form called "findForm." I have some option buttons on the form that determin what to search by. Here is my code!!!! Why won't it work??????????? no error comes up...there's sample data in the table.....
CODE:

Option Compare Database
Option Explicit

Private mstrSQL As String

Private Sub cmdClose_Click()
On Error Resume Next
DoCmd.Close
End Sub

Private Sub cmdGo_Click()
' Requery the General form based on
' the selectioned items

On Error GoTo HandleErr

DoCmd.OpenForm "General"
With Forms!General
If Len(Me!cboSelect & "") > 0 Then
' Construct SQL for General's Recordsource
Select Case optChoose
Case 1
' Wireless Number
mstrSQL = "SELECT * FROM General Where " _
& " Customer_Cell_Number Like '*" & DoubleQuote(Me![cboSelect]) & "*'"
Case 2
' last name
mstrSQL = "SELECT * FROM General WHERE " _
& " Customer_Last_Name Like '*" & DoubleQuote(Me![cboSelect]) & "*')"
Case 3
' Serial Number
mstrSQL = "SELECT * " _
& " FROM General WHERE E_S_N_Dec Like '*" _
& DoubleQuote(Me![cboSelect]) & "*')"
Case 4
' Cre Invoice Number
mstrSQL = "SELECT * " _
& "FROM General WHERE CRE_Invoice_Number Like '*" _
& DoubleQuote(Me![cboSelect]) & "*')"
Case Else
End Select
.RecordSource = mstrSQL
!cmdFind.Caption = "&Show All"
End If
End With
DoCmd.Close acForm, "FindForm"

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindForm.cmdGo_Click"
End Select
Resume ExitHere
Resume
End Sub

Private Sub optChoose_AfterUpdate()
' Populate rowsource of cboSelect

Dim strSQL As String

On Error GoTo HandleErr

Select Case optChoose
Case 1
' Wireless Number
strSQL = "Select Distinct Customer_Cell_Number from General " _
& "Order By Customer_Cell_Number"
Case 2
' last name
strSQL = "Select Distinct Customer_Last_Name from General " _
& "Where Customer_Last_Name Is Not Null Order By Customer_Last_Name"
Case 3
' Serial Number
strSQL = "Select Distinct E_S_N_Dec from General " _
& "Where E_S_N_Dec Is Not Null Order By E_S_N_dec"
Case 4
' Cre Invoice Number
strSQL = "Select Distinct CRE_Invoice_Number from General " _
& "Where CRE_Invoice_Number Is Not Null Order By CRE_Invoice_Number"
Case Else
End Select

With Me!cboSelect
.Value = Null
.RowSource = strSQL
.Requery
.Value = .ItemData(0)
End With

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_FindForm.optChoose_AfterUpdate"
End Select
Resume ExitHere
Resume
End Sub

Private Function DoubleQuote(strIn As String) As String
Dim i As Integer
Dim strtemp As String
For i = 1 To Len(strIn)
If Mid(strIn, i, 1) = "'" Then
strtemp = strtemp & "''"
Else
strtemp = strtemp & Mid(strIn, i, 1)
End If
Next i
DoubleQuote = strtemp
End Function


Thanks
Giacomo

 

[giacomo1]

in addition!!!

The ComboBox "cboSelect" is not being populated with the information....

 

[dgm]

Hi giacomo,
I think if you add [] around general it should work.
e.g.

strSQL = "Select Distinct Customer_Cell_Number from [General] " _ 
& "Order By Customer_Cell_Number"

Dave

 

[giacomo1]

Woohoo!!!

It worked!!!! Imagine a minute detail causing major problems!!!

Thanks Again!!!!