strsearch not working (1 Viewer)

[Chumpalot]

Hi all,

:banghead:

I have a form with a subform containing 'tbl_winestock' in datasheet view. On the form are some controls.

A text box (txt_searchproductname)
A command button (but_searchProductName)

When I enter text into the txt field and click the button, nothing happens. Focus is set to the first record in the subform datasheet.

Private Sub but_searchProductName_Click()
Dim strsearch As String
Dim Task As String
If IsNull(Me.txt_searchproductname) Or Me.txt_searchproductname = "" Then
    MsgBox "Please type in your search criteria", vbOKOnly, "Keyword Needed"
    Me.txt_searchproductname = vbYellow
    Me.txt_searchproductname.SetFocus
Else
    strsearch = Me.txt_searchproductname.Value
    Task = "Select * From tbl_winestock where ((Product_Name Like ""*" & strsearch & "*""))"
    Me.RecordSource = Task
    Me.txt_searchproductname.BackColor = vbWhite
End If
End Sub

Where on earth am I going wrong? I'm sure it's something simple.

Thanks very much for your help.

 

[Chumpalot]

Sorted it. I was identifying the wrong element here:

Me.RecordSource = Task

Should have been:

Me.tbl_winestock_subform1.Form.Recordsource = Task

Again...:banghead:

 

[Chumpalot]

Taking this a step further now.

I'd like to incorporate this search with some combo boxes. The cbos already work fine in conjunction with one another. I'm having trouble however getting them to work alongside the txt search.

Here's the code I have so far.

Option Compare Database

Private Sub but_clearcbos_Click()
Me.cbo_winetype = Null
Me.cbo_winegrade = Null
Me.cbo_vintage = Null
Me.txt_searchproductname = Null
Task = "Select * from tbl_winestock;"
Me.tbl_winestock_subform1.Form.RecordSource = Task
End Sub

Private Sub but_previewreport_Click()
Dim MyWineType, strWineGrade, strVintage As String
Dim strCriteria As String

If IsNull(Me.cbo_winetype) Then
    MyWineType = "[Type] like '*'"
Else
    MyWineType = "[Type] = '" & Me.cbo_winetype & "'"
End If

If IsNull(Me.cbo_winegrade) Then
    strWineGrade = "[Grade] like '*'"
Else
    strWineGrade = "[Grade] = '" & Me.cbo_winegrade & "'"
End If
If IsNull(Me.cbo_vintage) Then
    strVintage = "[Vintage] like '*'"
Else
    strVintage = "[Vintage] = '" & Me.cbo_vintage & "'"
End If


strCriteria = MyWineType & " And" & strWineGrade & " And" & strVintage
DoCmd.OpenReport "rep_winestock", acViewPreview, , strCriteria
End Sub

Private Sub but_searchproductname_Click()
Dim strsearch As String
Dim Task As String
If IsNull(Me.txt_searchproductname) Or Me.txt_searchproductname = "" Then
    MsgBox "Please type in your search criteria", vbOKOnly, "Keyword Needed"
    Me.txt_searchproductname = vbYellow
    Me.txt_searchproductname.SetFocus
Else
    strsearch = Me.txt_searchproductname.Value
    Task = "Select * From tbl_winestock where ((Product_Name Like ""*" & strsearch & "*""))"
    Me.tbl_winestock_subform1.Form.RecordSource = Task
    Me.txt_searchproductname.BackColor = vbWhite
End If
End Sub

Private Sub cbo_winetype_AfterUpdate()
Call SearchCriteria
End Sub

Private Sub cbo_winegrade_AfterUpdate()
Call SearchCriteria
End Sub

Private Sub cbo_vintage_AfterUpdate()
Call SearchCriteria
End Sub

Function SearchCriteria()
Dim MyWineType, strWineGrade, strVintage, StrProductName As String
Dim Task, strCriteria As String

If IsNull(Me.cbo_winetype) Then
    MyWineType = "[Type] like '*'"
Else
    MyWineType = "[Type] = '" & Me.cbo_winetype & "'"
End If

If IsNull(Me.cbo_winegrade) Then
    strWineGrade = "[Grade] like '*'"
Else
    strWineGrade = "[Grade] = '" & Me.cbo_winegrade & "'"
End If
If IsNull(Me.cbo_vintage) Then
    strVintage = "[Vintage] like '*'"
Else
    strVintage = "[Vintage] = '" & Me.cbo_vintage & "'"
End If
If IsNull(Me.txt_searchproductname) Then
    StrProductName = "[Product_Name] like '*'"
Else
    StrProductName = "[Product_Name] = '" & Me.txt_searchproductname & "'"
End If

strCriteria = MyWineType & " And" & strWineGrade & " And" & strVintage & " And" & StrProductName
    Task = "Select * from tbl_winestock where " & strCriteria
     Me.tbl_winestock_subform1.Form.RecordSource = Task
     Me.tbl_winestock_subform1.Form.Requery
    'DoCmd.ApplyFilter , strCriteria
    'Me.tbl_winestock_subform1.Form.Filter = strCriteria
End Function

Private Sub Form_Load()
Task = "Select * from tbl_winestock;"
Me.tbl_winestock_subform1.Form.RecordSource = Task
End Sub

I guess I have to add the 'Call SearchCriteria' into the OnClick event of my new 'but_searchproductname'. I would then have to alter the SearchCritera() sub. I just don't know how.

Thanks for your help.
David

 

[arnelgp]

Private Sub but_searchproductname_Click()
'Dim strsearch As String
Dim Task As String
If IsNull(Me.txt_searchproductname) Or Me.txt_searchproductname = "" Then
    MsgBox "Please type in your search criteria", vbOKOnly, "Keyword Needed"
    Me.txt_searchproductname = vbYellow
    Me.txt_searchproductname.SetFocus
Else
    'strsearch = Me.txt_searchproductname.Value
    'Task = "Select * From tbl_winestock where ((Product_Name Like ""*" & strsearch & "*""))"
    'Me.tbl_winestock_subform1.Form.RecordSource = Task
    Me.txt_searchproductname.BackColor = vbWhite
    Call SearchCriteria
End If
End Sub

 

[Chumpalot]

arnelgp,

Thank you for your reply. Unfortunately this results in an error on

Function SearchCriteria()
[B]Me.tbl_winestock_subform1.Form.RecordSrouce = Task[/B]

 

[Chumpalot]

So after fixing the 'data type mismatch' the button is now returning no values when only it is used. Bizarre.

All other cbos work fine.