Multi Listbox Search

[natekris8183]

Earlier I posted about multi searching listbox results, however that was with just at text in addition to a textbox. The problem I am not having is that I have to listbox search that pull from one "Scheduled follow-up" table. I need to pull in one listbox the results for 'open' (which I will be determined with a check box yes/no is checked or unchecked). So "open" meaning unchecked and "closed" meaning checked. Obviously the second listbox will show the "closed" since the first will be the open. Here's what I have:

'This is if it's open
strSql = "SELECT tblScheduledFollowup.[ID], tblScheduledFollowup.[SFBranchNum], tblScheduledFollowup.[SFUserID], tblScheduledFollowup.[SFMem], tblScheduledFollowup.[SFDate], tblScheduledFollowup.[SFReasons], tblScheduledFollowup.[SFResult], tblScheduledFollowup.[SFNotes] FROM tblScheduledFollowup"
varWhere = "WHERE"
'The statement below is to "ORDER" the results by which ever field you require, in my example its the field "Time"
strOrder = "ORDER BY tblScheduledFollowup.[SFDate];"
 
' Check for LIKE Social
If Me.MemSSN > "" Then
varWhere = varWhere & "(((tblScheduledFollowup.[SFMem]) LIKE '" & Me.MemSSN & "*')"
varWhere = varWhere & " AND ((tblScheduledFollowup.[SFCompleted] =) & False))"
End If
 
If varWhere = "" Then
varWhere = "9999999999999"
End If
Me.List231.RowSource = strSql & " " & varWhere & "" & strOrder
 
'This is if its closed 
strSql = "SELECT tblScheduledFollowup.[ID], tblScheduledFollowup.[SFBranchNum], tblScheduledFollowup.[SFUserID], tblScheduledFollowup.[SFMem], tblScheduledFollowup.[SFDate], tblScheduledFollowup.[SFReasons], tblScheduledFollowup.[SFResult], tblScheduledFollowup.[SFNotes] FROM tblScheduledFollowup"
varWhere = "WHERE"
'The statement below is to "ORDER" the results by which ever field you require, in my example its the field "Time"
strOrder = "ORDER BY tblScheduledFollowup.[SFCompletedDate];"
 
' Check for LIKE Social
If Me.MemSSN > "" Then
varWhere = varWhere & "(((tblScheduledFollowup.[SFMem]) LIKE '" & Me.MemSSN & "*')"
varWhere = varWhere & " AND ((tblScheduledFollowup.[SFCompleted] =) & True))"
End If
 
If varWhere = "" Then
varWhere = "9999999999999"
End If
Me.List233.RowSource = strSql & " " & varWhere & "" & strOrder

 

[pr2-eugin]

Hi, your description is a bit unclear.. could you give another shot at explaining, what you are actually doing?

 

[natekris8183]

I have a schedule to schedule follow-up calls, and when it pulls the record up for the parrticular client it would show any current scheduled follow-up calls as well as completed ones. However, to differentiate I want the two listboxes to search when the Completed box is True for completed and False for open.

Does this make since with the screen shots attached?

 

[pr2-eugin]

The Row Source of the First (yet to complete) list box should be..

SELECT [COLOR=RoyalBlue]field1, field2, field3, field4[/COLOR] FROM tblScheduledFollowUp WHERE SFCompleted=False

The Row Source of the Second (that is complete) list box should be..

SELECT [COLOR=RoyalBlue]field1, field2, field3, field4[/COLOR] FROM tblScheduledFollowUp WHERE SFCompleted=True

field1, field2, field3, field4 are the ones that forms the columns of the Listbox.

 

[natekris8183]

I'm not quite sure I am following you. I have several listboxes on another page that look for SFMem (which is triggered by social security number), however rather than having multiple tables with separate data (i.e. checking table, savings table, etc.) I used the following to filter what I wanted to show in each listbox. I am attempting to do the same thing however via Social and SFCompleted Yes/No. I used your WHERE statement in the strSql but that produced no results.

strSql = "SELECT tblMemDepPrdInfoExt.[MemDepPrdInfoExtID], tblMemDepPrdInfoExt.[MemDepPrdInfoExtMem],tblMemDepPrdInfoExt.[MemDepPrdInfoExtFIName], tblMemDepPrdInfoExt.[MemDepPrdInfoExtMajType], tblMemDepPrdInfoExt.[MemDepPrdInfoExtMinType], tblMemDepPrdInfoExt.[MemDepPrdInfoExtRate], tblMemDepPrdInfoExt.[MemDepPrdInfoExtMaturity], tblMemDepPrdInfoExt.[MemDepPrdInfoExtNotes] FROM tblMemDepPrdInfoExt"
varWhere = "WHERE"
 
strOrder = "ORDER BY tblMemDepPrdInfoExt.[MemDepPrdInfoExtMajType];"
 
' Check for LIKE Social and Checking product Type
If Me.MemSSN > "" Then
varWhere = varWhere & "(((tblMemDepPrdInfoExt.[MemDepPrdInfoExtMem]) LIKE '" & Me.MemSSN & "*')"
varWhere = varWhere & " AND ((tblMemDepPrdInfoExt.[MemDepPrdInfoExtMajType]) LIKE 'Checking*'))"
End If
 
If varWhere = "" Then
varWhere = "9999999999999"
End If
Me.List249.RowSource = strSql & " " & varWhere & "" & strOrder

 

[natekris8183]

This is what I came up with, but for some reason its asking me for a paramete value for Me.MemSSN whereas I use that Text Box in all my other listbox searches.

strSql = "SELECT tblScheduledFollowup.[ID], tblScheduledFollowup.[SFBranchNum], tblScheduledFollowup.[SFUserID], tblScheduledFollowup.[SFMem], tblScheduledFollowup.[SFDate], tblScheduledFollowup.[SFReasons], tblScheduledFollowup.[SFResult], tblScheduledFollowup.[SFNotes] FROM tblScheduledFollowup"
strWhere = "WHERE (((tblScheduledFollowup.[SFMem]) = Me.MemSSN) AND ((tblScheduledFollowup.[SFCompleted]) = False))"
strOrder = "ORDER BY tblScheduledFollowup.[SFDate];"
 
Me.List231.RowSource = strSql & " " & strWhere & "" & strOrder