MultiSearch Custom listbox search

[natekris8183]

I have hit a minor road block. (AC 2010) We are putting together a CRM (this is a patchwork fix probably for the next 24 months while we implement something more robust). There are several custom search boxes that automatically updated on the main member profile record form to show certain data (scheduled follow-up calls, existing accounts, etc.). However, one of the tables is designed with a major product type and a minor product type (i.e. Checking [major], Interest bearing [Minor]).

Below is the vba being used for the search for the individial record per member by social, but a second varWhere for major product type needs to be input so that two separate listboxes can be segregated to allow multiple sections on the form for Major Product types. When Input the LIKE as "Checking" (for example) it searched all boxes instead of just the one where thes str was. Hopefully this makes since as I only consider myself an intermediate when it comes to visual basic code.

'The statement below refers your table you are gathering information for and the required fields, replace "tblSaveLogon" with you table name and the information after "." with all the required fields you need. In my example below i have 4 fields which i want to appear in the list box.

Thanks for the help!

strSql = "SELECT tblMemDepPrdInfo.[MemDepPrdInfoID], tblMemDepPrdInfo.[MemDepPrdInfoMem],tblMemDepPrdInfo.[MemDepPrdInfoMajType], tblMemDepPrdInfo.[MemDepPrdInfoMinType], tblMemDepPrdInfo.[MemDepPrdInfoDateOpen], tblMemDepPrdInfo.[MemDepPrdInfoStatus], tblMemDepPrdInfo.[MemDepPrdInfoDateClosed], tblMemDepPrdInfo.[MemDepPrdInfoReasonClosed], tblMemDepPrdInfo.[MemDepPrdInfoNotes] FROM tblMemDepPrdInfo"
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 tblMemDepPrdInfo.[MemDepPrdInfoDateOpen];"

' Check for LIKE Social
If Me.MemSSN > "" Then
varWhere = varWhere & "(tblMemDepPrdInfo.[MemDepPrdInfoMem]) LIKE """ & Me.MemSSN & "*"" "
varWhere = varWhere & "(tblMemDepPrdInfo.[MemDepPrdInfoMajType]) LIKE """ & "Checking" & "*"" "
End If

If varWhere = "" Then
varWhere = "9999999999999"
End If
Me.List247.RowSource = strSql & " " & varWhere & "" & strOrder
varWhere = Mid(varWhere, 1, Len(varWhere) - 8)

 

[natekris8183]

I apologize, please ignore the descriptions. This VBA was written for a member search function then just copied over to the main form, so I haven't cleaned up the descriptions.

Hopefully this makes since, but there are 2 listboxes searching for deposit products (one list box filters SSN and Majtype for "Checking" and the other the same but for "Savings" so the str are simultaneous). If need be I can provide the into VBA code to show them as they run one after the other.

 

[Isskint]

If you are useing multiple WHERE criteria, you need an exclusive operator (AND OR etc). So if you want your citeria to return [MemDepPrdInfoMem]) LIKE """ & Me.MemSSN & "*"" " AND [MemDepPrdInfoMajType]) LIKE """ & "Checking" & "*"" " then you would need;

varWhere = varWhere & "(((tblMemDepPrdInfo.[MemDepPrdInfoMem]) LIKE '" & Me.MemSSN & "*')"

varWhere = varWhere & " AND ((tblMemDepPrdInfo.[MemDepPrdInfoMajType]) LIKE 'Checking*'))"

 

[natekris8183]

Worked perfectly. I HATE those ah-ha moments, because as soon as you said it I realized how simple it was. THANKS!

 

[natekris8183]

OK... so I am having a similiar issue, and likely it will be as simple a fix, but it's between to filtered listboxes however instead of text such as above, this time I need two different AND's. The first to look and see if the "date" rate is <>0, so that it filters the first listbox for SFMem AND SFDate, this will be those scheduled follow-ups still "open". However, the second search listbox would consist of those completed which would be denoted by a checkbox "yes/no" (yes obviously meaning completely). I've written what I thought would be correct and have no errors, but no records are being located. Any thoughts?

'This would be the search for opened Scheduled Follow-ups.

strSql = "SELECT tblScheduledFollowup.[ID], tblScheduledFollowup.[SFBranchNum], tblScheduledFollowup.[SFUserID], tblScheduledFollowup.[SFMem], tblScheduledFollowup.[SFDate], tblScheduledFollowup.[SFReasons], tblScheduledFollowup.[SFResult], tblScheduledFollowup.[SFNotes] FROM tblScheduledFollowup"
varWhere = "WHERE"

' Check for LIKE Social
If Me.MemSSN > "" Then
varWhere = varWhere & "(((tblScheduledFollowup.[SFMem]) LIKE '" & Me.MemSSN & "*')"
varWhere = varWhere & " AND ((tblScheduledFollowup.[SFDate] <>) & 0)"
End If

If varWhere = "" Then
varWhere = "9999999999999"
End If
Me.List231.RowSource = strSql & " " & varWhere & "" & strOrder

'This would be the search for closed Scheduled Follow-ups

strSql = "SELECT tblCompletedFollowup.[ComSFID], tblCompletedFollowup.[ComSFBranchNum], tblCompletedFollowup.[ComSFUserID], tblCompletedFollowup.[ComSFMem], tblCompletedFollowup.[ComSFDate], tblCompletedFollowup.[ComSFReasons], tblCompletedFollowup.[ComSFResult], tblCompletedFollowup.[ComSFNotes] FROM tblCompletedFollowup"
varWhere = "WHERE"

' Check for LIKE Social
If Me.MemSSN > "" Then
varWhere = varWhere & "(((tblCompletedFollowup.[ComSFMem]) LIKE '" & Me.MemSSN & "*')"
varWhere = varWhere & " AND ((tblCompletedFollowup.[SFCompleted] =) & -1)"
End If

If varWhere = "" Then
varWhere = "9999999999999"
End If
Me.List233.RowSource = strSql & " " & varWhere & "" & strOrder

 

[Isskint]

Just missing a couple of bits wrong in the first code. Missing a closing bracket and you dont the ) & 0. So:

varWhere = varWhere & "(((tblScheduledFollowup.[SFMem]) LIKE '" & Me.MemSSN & "*')"
varWhere = varWhere & " AND ((tblScheduledFollowup.[SFDate]) <> 0)))"
And the same in the second bit of code also;

varWhere = varWhere & "(((tblCompletedFollowup.[ComSFMem]) LIKE '" & Me.MemSSN & "*')"
varWhere = varWhere & " AND ((tblCompletedFollowup.[SFCompleted]) = -1))"

Try these and see if that works