Search option

[rupes_mahal]

Hi..

I have developed a Search form.

Search Form:

Option group

* Premise Code
* Practice Code
* Partnership Code
* Address

I have a combo box, that relates to the Option group. E.G. if I pick Premise Code in the option group, the drop down values in the combo box all relate to the premise code values. And the same for partnership code practice code and Address. This all works. The code for this is:

Private Sub optChoose_AfterUpdate()
' Populate rowsource of cboSelect

Dim strSQL As String

On Error GoTo HandleErr

Select Case optChoose
Case 1
' premise code
strSQL = "Select Distinct PremiseCode from DoctorsDetailsTBL " _
& "Order By PremiseCode"
Case 2
' Practice code
strSQL = "Select Distinct PracticeCode from DoctorsDetailsTBL " _
& "Order By PracticeCode"
Case 3
' partnership code
strSQL = "Select Distinct PartnershipCode from DoctorsDetailsTBL " _
& "Order By PartnershipCode"
Case 4
' Address
strSQL = "Select Distinct Address from DoctorsDetailsTBL " _
& "Order By Address"
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_searchForm.optChoose_AfterUpdate"
End Select
Resume ExitHere
Resume
End Sub


This all works.

Now what my problem is the OK button. When the user picks an option from the option group and picks a value in the combo box, he/she is now ready to click OK and the search should now bring back the record in the form. I also want it to bring it back in a datasheet view.

I am struck on the code for this problem. So far I have:

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

On Error GoTo HandleErr

DoCmd.OpenForm "DoctorsDetailsForm"
With Forms!DoctorsDetailsForm
If Len(Me!cboSelect & "") > 0 Then
' Construct SQL for Doctor's Recordsource
Select Case optChoose
Case 1
' premise code
mstrSQL = "SELECT * FROM DoctorsDetailsTBL Where " _
& " PremiseCode '*" & (Me![cboSelect]) & "*'"

Case Else
End Select
.RecordSource = mstrSQL

Else
.RecordSource = "DoctorsDetailsTBL"
End If
End With
DoCmd.Close acForm, "searchForm"

ExitHere:
Exit Sub

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

I am trying to make it work for the first option "Premise Code". It DOESNT work. Help.......

(cboSelect = name of the combo box)

ANYONE PLEASE HELP.

Thnak you in advance

Ruby