[Solved] Listbox using rowsource generated by querydef

See more:

MS-Access

VBA

Hi,

I have an Access 2007 form containing an option frame with 5 search options, and a search button. The button generates a querydef for "qrySearch" based on search option,and results are to appear in a listbox on the same form.

When I click search the first time, results appear in the lsitbox for the search option. I can search again by the same option and correct results will also appear in the listbox. However, if I choose to search by any other option nothing will appear in the listbox. I have set up a DoCmd.OpenQuery "qrySearch" and know that the qrydef is working properly. This is not at form level either because I can close and reopen have have the same issue somehow. I think the listbox either needs cleared and requery, or there is some option i'm missing-- anyone know? :)

Thanks! Here's the code:

Private Sub btnSearch_Click()

Dim strSQL As String
Dim qdfNew As DAO.QueryDef
Dim db As DAO.Database

Set db = CurrentDb

Select Case Me.frameSearch.Value

Case 1 'Search by MemberID
'If textbox Null - msgBox
If IsNull(Me.txtMemberID) = True Then
MsgBox "Enter a MemberID to search.", vbExclamation + vbOKOnly, "Enter Search Criteria"
Else
'strSQL
strSQL = "SELECT tblMemberMaster.MemberID, "
strSQL = strSQL & "[MemLName]+', '+[MemFName] AS [Member Name], "
strSQL = strSQL & "tblMemberMaster.Program , "
strSQL = strSQL & "tblMemberMaster.CurrentlyEligible "
strSQL = strSQL & "FROM tblMemberMaster "
strSQL = strSQL & "WHERE (((tblMemberMaster.MemberID) = [Forms]![frmSearch]![txtMemberID]))"
strSQL = strSQL & "ORDER BY tblMemberMaster.MemberID;"
db.QueryDefs.Delete ("qrySearch")
Set qdfNew = db.CreateQueryDef("qrySearch", strSQL)
qdfNew.Close
'requery listbox
DoCmd.OpenQuery "qrySearch"
Me.lstBxResult.Requery

End If

Case 2 'Search by Member Name
'If combobox Null - msgBox
If IsNull(Me.cmbMemberName) = True Then
MsgBox "Select Member Name to search.", vbExclamation + vbOKOnly, "Enter Search Criteria"
Else
'strSQL
strSQL = "SELECT tblMemberMaster.MemberID, "
strSQL = strSQL & "[MemLName]+', '+[MemFName] AS [Member Name], "
strSQL = strSQL & "tblMemberMaster.Program , "
strSQL = strSQL & "tblMemberMaster.CurrentlyEligible "
strSQL = strSQL & "FROM tblMemberMaster "
strSQL = strSQL & "WHERE ((([MemLName]+', '+[MemFName])=[Forms]![frmSearch]![CmbMemberName]))"
strSQL = strSQL & "ORDER BY tblMemberMaster.MemberID;"
db.QueryDefs.Delete ("qrySearch")
Set qdfNew = db.CreateQueryDef("qrySearch", strSQL)
qdfNew.Close
'requery list box
DoCmd.OpenQuery "qrySearch"
Me.lstBxResult.Requery

End If

Case 3 'Search by PCP
'If textbox Null - msgBox
If IsNull(Me.cmbPCP) = True Then
MsgBox "Select PCP to search", vbExclamation + vbOKOnly, "Enter Search Criteria"
Else
'strSQL
strSQL = "SELECT tblMemberMaster.MemberID, "
strSQL = strSQL & "[MemLName]+', '+[MemFName] AS [Member Name], "
strSQL = strSQL & "tblMemberMaster.Program , "
strSQL = strSQL & "tblMemberMaster.CurrentlyEligible "
strSQL = strSQL & "FROM tblMemberMaster "
strSQL = strSQL & "WHERE ((([PcpLname]+', '+[PcpFname])=[Forms]![frmSearch]![CmbPcp]))"
strSQL = strSQL & "ORDER BY tblMemberMaster.MemberID;"
db.QueryDefs.Delete ("qrySearch")
Set qdfNew = db.CreateQueryDef("qrySearch", strSQL)
qdfNew.Close
'requery list box
DoCmd.OpenQuery "qrySearch"
Me.lstBxResult.Requery
End If

Case 4 'Search by Practice Location
'If textbox Null - msgBox
If IsNull(Me.cmbPracticeLocation) = True Then
MsgBox "Select Practice Location to search.", vbExclamation + vbOKOnly, "Enter Search Criteria"
Else
'strSQL
strSQL = "SELECT tblMemberMaster.MemberID, "
strSQL = strSQL & "[MemLName]+', '+[MemFName] AS [Member Name], "
strSQL = strSQL & "tblMemberMaster.Program , "
strSQL = strSQL & "tblMemberMaster.CurrentlyEligible "
strSQL = strSQL & "FROM tblMemberMaster "
strSQL = strSQL & "WHERE (((tblMemberMaster.PracticeLocation)=[Forms]![frmSearch]![CmbPracticeLocation]))"
strSQL = strSQL & "ORDER BY tblMemberMaster.MemberID;"
db.QueryDefs.Delete ("qrySearch")
Set qdfNew = db.CreateQueryDef("qrySearch", strSQL)
qdfNew.Close
'requery list box
DoCmd.OpenQuery "qrySearch"
Me.lstBxResult.Requery
End If

Case 5 'Search by Identified Date
'If textbox Null - msgBox
If IsNull(Me.txtDateTo) = True Or IsNull(Me.txtDateFrom) = True Then
MsgBox "Enter To Date and From Date to search.", vbExclamation + vbOKOnly, "Enter Search Criteria"
Else
'strSQL
strSQL = "SELECT tblMemberMaster.MemberID, "
strSQL = strSQL & "[MemLName]+', '+[MemFName] AS [Member Name], "
strSQL = strSQL & " tblMemberMaster.Program , "
strSQL = strSQL & "tblMemberMaster.CurrentlyEligible "
strSQL = strSQL & "FROM tblMemberMaster "
strSQL = strSQL & "WHERE (((tblMemberMaster.IdentifiedDate) Between [Forms]![frmSearch]![txtDateFrom] And [Forms]![frmSearch]![txtDateTo]))"
strSQL = strSQL & "ORDER BY tblMemberMaster.MemberID;"
db.QueryDefs.Delete ("qrySearch")
Set qdfNew = db.CreateQueryDef("qrySearch", strSQL)
qdfNew.Close
'requery list box
DoCmd.OpenQuery "qrySearch"
Me.lstBxResult.Requery
End If

End Select

'Clears values in comboboxes and textbox
Me.txtMemberID.DefaultValue = ""
Me.cmbMemberName.DefaultValue = ""
Me.cmbPCP.DefaultValue = ""
Me.cmbPracticeLocation.DefaultValue = ""
Me.txtDateFrom.DefaultValue = ""
Me.txtDateTo.DefaultValue = ""

End Sub

UPDATE:
Resolved by OP himself. Posted as one of the answers.