Using VB code to run search from comboboxes & place results in form

[Michelle86]

Hi,

I'm trying to have a form that allows the users to select search criteria from 3 drop down boxes. They can choose to select criteria from 1, 2 or all of the boxes.

However when I choose the criteria it only seems to work for the Division criteria on its own and none of the others!! Please help!!!!

Here's my code:

Private Sub btnSearchForProspect_Click()

    Dim StrCompany As String
    Dim StrDivision As String
    Dim StrStatus As String
    Dim StrFilter As String
    
'Code to automatically open form
    If SysCmd(acSysCmdGetObjectState, acForm, "frmEditProspect") <> acObjStateOpen Then
       DoCmd.OpenForm "frmEditProspect", acViewNormal, StrFilter
       
    End If
    
'Build Criteria string for Company
    If IsNull(Me.cboCompanyDD.Value) Then
        StrCompany = "Like '*'"
    Else
        StrCompany = "='" & Me.cboCompanyDD.Value & "'"
    End If
    
    
'Build Criteria string for Division
    If IsNull(Me.cboDivisionDD.Value) Then
        StrDivision = "Like '*'"
    Else
        StrDivision = "='" & Me.cboDivisionDD.Value & "'"
    End If
    
    
'Build Criteria string for Status
    If IsNull(Me.cboStatusDD.Value) Then
        StrStatus = "Like '*'"
    Else
        StrStatus = "='" & Me.cboStatusDD.Value & "'"
    End If
    
'Combine criteria strings into WHERE clause for the filter
    StrFilter = " [Company] " & StrCompany & " AND [Division] " & StrDivision & " AND [CurrentStatus] " & StrStatus
   
    
'Apply the filter and switch on
    With Forms![frmEditProspect]
        .Filter = StrFilter
        .FilterOn = True

    End With
    
 
End Sub

Hope the above made sense...
Any suggestions greatly received!

Thanks

Michelle

 

[scottsanpedro]

I would do something like this;

Dim strSQL as String

If IsNull(Me.cboCompanyDD.Value) Then
strSQL = strSQL
Else
strSQL = strSQL & [Company] = "' & Me.cboCompanyDD.Value & "' AND"
End If

If IsNull(Me.cboDivisionDD.Value) Then
strSQL = strSQL
Else
strSQL = strSQL & [Division] = "' & Me.cboCompanyDD.Value & "' AND"
End If

If IsNull(Me.cboStatusDD.Valuee) Then
strSQL = strSQL
Else
strSQL = strSQL & [CurrentStatus] = "' & Me.cboCompanyDD.Value & "' AND"
End If

If Len(strSQL) > 0 Then
intLength = Len(strSQL)
End If

strSQL = Mid(strSQL, 1, intLength - 5) 'This will take off leading "AND"

'Apply the filter and switch on
With Forms![frmEditProspect]
.Filter = StrSQL
.FilterOn = True

End With

Hope it helps

Scott

 

[scottsanpedro]

sorry slight typo

Update each line to have extra " in front

i.e. strSQL = strSQL & "[Company] = "' & Me.cboCompanyDD.Value & "' AND"

 

[scottsanpedro]

oh dear..should think more...

make sure tou have extra space after each AND

i.e. strSQL = strSQL & "[Company] = "' & Me.cboCompanyDD.Value & "' AND "

sorry

 

[Michelle86]

Thanks scottsanpedro for your quick reply!

When I select a criteria and run it, a run time error occurs '2448'. Saying "you can't assign a value to this object".

When I go to debug it highlights the following code:

.Filter = strSQL

Can you suggest any reasons?

Thanks

 

[scottsanpedro]

Hi,

try changing all strSQL to your original strFilter so it matches as before.

Let me know if the error re-occurs

Scott

 

[scottsanpedro]

and maybe post your code again

 

[Michelle86]

If i chang it back to strFilter is brings up a form with no details in it.

Here's my code:

Private Sub btnSearchForProspect_Click()

   Dim strSQL As String
   Dim strFilter As String
      
'Code to automatically open form
    If SysCmd(acSysCmdGetObjectState, acForm, "frmEditProspect") <> acObjStateOpen Then
       DoCmd.OpenForm "frmEditProspect", acViewNormal, strFilter
    End If

If IsNull(Me.cboCompanyDD.Value) Then
    strSQL = strSQL
    Else
    strSQL = strSQL & "[Company] = " ' & Me.cboCompanyDD.Value & " ' AND"
End If

If IsNull(Me.cboDivisionDD.Value) Then
    strSQL = strSQL
    Else
    strSQL = strSQL & "[Division] = " ' & Me.cboDivisionDD.Value & " ' AND"
End If

If IsNull(Me.cboStatusDD.Value) Then
    strSQL = strSQL
    Else
    strSQL = strSQL & "[CurrentStatus] = " ' & Me.cboCurrentStatusDD.Value & " ' AND"
End If

If Len(strSQL) > 0 Then
    intLength = Len(strSQL)
End If

strSQL = Mid(strSQL, 1, intLength - 5) 'This will take off leading "AND"

'Apply the filter and switch on
    With Forms![frmEditProspect]
        .Filter = strFilter
        .FilterOn = True

    End With
    
 
End Sub

Thanks

 

[scottsanpedro]

Hi again,

Try this

Private Sub btnSearchForProspect_Click()

Dim strSQL As String
Dim strFilter As String

'Code to automatically open form
If SysCmd(acSysCmdGetObjectState, acForm, "frmEditProspect") <> acObjStateOpen Then
DoCmd.OpenForm "frmEditProspect", acViewNormal, strFilter
End If

If IsNull(Me.cboCompanyDD.Value) Then
strFilter= strFilter
Else
strFilter = strFilter& "[Company] = " ' & Me.cboCompanyDD.Value & " ' AND "
End If

If IsNull(Me.cboDivisionDD.Value) Then
strFilter = strFilter
Else
strFilter= strFilter& "[Division] = " ' & Me.cboDivisionDD.Value & " ' AND "
End If

If IsNull(Me.cboStatusDD.Value) Then
strFilter = strFilter
Else
strFilter = strFilter& "[CurrentStatus] = " ' & Me.cboCurrentStatusDD.Value & " ' AND "
End If

If Len(strFilter) > 0 Then
intLength = Len(strFilter)
End If

strSQL = Mid(strFilter, 1, intLength - 5) 'This will take off leading "AND"

'Apply the filter and switch on
With Forms![frmEditProspect]
.Filter = strFilter
.FilterOn = True

End With


End Sub


let me know

Scott

 

[Michelle86]

It brought up a syntax error because there was no space between the strFilter and the & e.g. strFilter = strFilter&.

So when you changed it, obviously brought up the same error.

Thanks for your effort

Michelle

 

[scottsanpedro]

so are we in the same boat, or working now?

 

[Michelle86]

unfortunately it still doesn't work!

Michelle

 

[scottsanpedro]

ok...lets try again. my fault

Private Sub btnSearchForProspect_Click()

   Dim strFilter As String, intLength as integer
      
'Code to automatically open form
    If SysCmd(acSysCmdGetObjectState, acForm, "frmEditProspect") <> acObjStateOpen Then
       DoCmd.OpenForm "frmEditProspect", acViewNormal, strFilter
    End If

If IsNull(Me.cboCompanyDD.Value) Then
    strFilter = strFilter
    Else
    strFilter = strFilter & "[Company] = "' & Me.cboCompanyDD.Value & '" AND "
End If

If IsNull(Me.cboDivisionDD.Value) Then
    strFilter = strFilter
    Else
    strFilter = strFilter & "[Division] = "' & Me.cboDivisionDD.Value & '" AND "
End If

If IsNull(Me.cboStatusDD.Value) Then
    strFilter = strFilter
    Else
    strFilter = strFilter & "[CurrentStatus] = "' & Me.cboCurrentStatusDD.Value & '" AND "
End If

If Len(strFilter) > 0 Then
    intLength = Len(strFilter)
End If

strFilter = Mid(strFilter, 1, intLength - 5) 'This will take off leading "AND"

'Apply the filter and switch on
    With Forms![frmEditProspect]
        .Filter = strFilter
        .FilterOn = True
    End With
    
 
End Sub

The only other thing to make sure is that none of your parameters are numbers, if so here is an example.

strFilter = strFilter & "[Division] = ' & Me.cboDivisionDD.Value & ' AND "

let me know..Scott

 

[Michelle86]

No luck I'm afraid! Still can't get it to work.

Michelle

 

[scottsanpedro]

Hi Michelle,

I have been working blind on this and not tested it in an application.
I hope you can see the logic in this code and maybe work around it and see if you can complete it.
I have often used forums for getting answers and found typos etc and worked around them and learnt loads.
This code simply concatenates a string and then is passed to the filter, nothing more.
The logic of the concatenating is often where things go wrong.
I hope you get it sorted as this formula will work for you.
Good luck
Scott