Error when re-querying subform (1 Viewer)

[Chumpalot]

Hi all, I'm hoping someone could help me with an issue I have with a new database I've created.

For now the setup is simple, I have a form with subform. This subform displays a table, the contents of which are altered depending on what you choose in three combo boxes.

I receive the following error when using either cbo_winegrade or cbo_vintage. If I start building the query from the first combobox (cbo_winetype) then everything works OK.

Here's the code:

Option Compare Database

Private Sub but_clearcbos_Click()
Me.cbo_winetype = Null
Me.cbo_winegrade = Null
Me.cbo_vintage = Null
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 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


strCriteria = MyWineType & "And" & strWineGrade & "And" & strVintage
    task = "Select * from tbl_winestock where " & strCriteria
    Me.tbl_winestock_subform1.Form.RecordSource = task
    Me.tbl_winestock_subform1.Form.Requery

End Function

Thanks very much for any help you can provide.

David

 

[Chumpalot]

I have just spotted this...

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

Clearly the = shouldn't be in there.

Everything now works as it should. Apologies.

This has however thrown up a second query I had though. On form load, the contents of the subform table lists only one record. How can I get this to populate with all of the entries from the table?

Thanks again.

 

[arnelgp]

strCriteria = MyWineType & "And" & strWineGrade & "And" & strVintage
should have extra space between the word And.

Should be:

strCriteria = MyWineType & " And " & strWineGrade & " And " & strVintage

 

[Chumpalot]

strCriteria = MyWineType & "And" & strWineGrade & "And" & strVintage
should have extra space between the word And.

Should be:

strCriteria = MyWineType & " And " & strWineGrade & " And " & strVintage

Thanks for your reply.

Making this change doesn't seem to have any impact and it still works

Any idea how I can get the tbl to show all records on load?

Thanks again.

 

[Chumpalot]

It seems I am gradually fixing my own issues as I go along here. For some reason the RecordSource of the subform had criteria in it. I changed this to * and everything shows on load

How do I show all records in the table when the clear filters button is clicked? At the moment this is set to simply clear the combo boxes.

Thanks again.

 

[arnelgp]

add this on but_clearcbo_click sub:

task = "Select * from tbl_winestock;"
Me.tbl_winestock_subform1.Form.RecordSource = task