Search a job reference

S

Sunnylei

Registered User.
Local time
Yesterday, 17:42
Joined
Mar 5, 2011
Messages
87
I created a search button for searching a job reference (Called 'CM Code'. The code is below

Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append
Dim stFormName As String
Dim stCmCodeFilter As String


If Not IsNull(Me.txtFilterCmCode) Then

strWhere = strWhere & "([CM Code]=" & Me.txtFilterCmCode & ") AND "
End If

stFormName = "MainContractCost"
stCmCodeFilter = "[CM Code]"
DoCmd.OpenForm stFormName, , , stCmCodeFilter
lngLen = Len(strWhere) - 3 'CM Code is 3 numbers
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

However, it does not work. I'm not sure what is missing. Please help! :confused:
 
I will say it again, put your code in code tags. The link I gave you shows you how to do it.
 
Since CM Code is a textfield you will need to wrap quotes around it.

strWhere = strWhere & "([CM Code]= '" & Me.txtFilterCmCode & "'" & ") AND "

JR
 
Last edited:
vbaInet said:
I will say it again, put your code in code tags. The link I gave you shows you how to do it.
Click to expand...

Code: 
 Dim strWhere As String 'The criteria string.
 Dim lngLen As Long 'Length of the criteria string to append
 Dim stFormName As String
 Dim stCmCodeFilter As String

Code: 
If Not IsNull(Me.txtFilterCmCode) Then
strWhere = strWhere & "([CM Code]=" & Me.txtFilterCmCode & ") AND "
End If

Code: 
stFormName = "MainContractCost"
stCmCodeFilter = "[CM Code]"
DoCmd.OpenForm stFormName, , , stCmCodeFilter

Code: 
lngLen = Len(strWhere) - 3 'CM Code is 3 numbers
If lngLen <= 0 Then 
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)

Code: 
Me.Filter = strWhere
Me.FilterOn = True
 
You can put it in just one block. It makes it easier for us to copy your code and amend it. Try this:
Code: 
    Dim stCmCodeFilter As String

    If Len(Me.txtFilterCmCode & vbNullString) = 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        stCmCodeFilter = "[CM Code] = '" & Me.txtFilterCmCode & "'"
        
        DoCmd.OpenForm "MainContractCost", , , stCmCodeFilter
    
        Me.Filter = stCmCodeFilter
        Me.FilterOn = True
    End If
Same as what JANR advised with redundant code lines removed.
 
Why are you setting/building a filter on the current form when you are opening another form to show the filtered data?

You only need this:

Code: 
Private Sub Search_Click()
If Not IsNull(Me.txtFilterCmCode) Then
   DoCmd.OpenForm "MainContractCost",,, "[CM Code]= '" & Me.txtFilterCmCode & "'"
End Sub

JR
 
vbaInet said:
You can put it in just one block. It makes it easier for us to copy your code and amend it. Try this:
Code: 
    Dim stCmCodeFilter As String
 
    If Len(Me.txtFilterCmCode & vbNullString) = 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        stCmCodeFilter = "[CM Code] = '" & Me.txtFilterCmCode & "'"
 
        DoCmd.OpenForm "MainContractCost", , , stCmCodeFilter
 
        Me.Filter = stCmCodeFilter
        Me.FilterOn = True
    End If
Same as what JANR advised with redundant code lines removed.
Click to expand...

I have tried, there is a Error '3464' Date type mismatch in criteria expression.
Debug:
Code: 
DoCmd.OpenForm "MainContractCost", , , stCmCodeFilter
 
JANR said:
Why are you setting/building a filter on the current form when you are opening another form to show the filtered data?

You only need this:

Code: 
Private Sub Search_Click()
If Not IsNull(Me.txtFilterCmCode) Then
   DoCmd.OpenForm "MainContractCost",,, "[CM Code]= '" & Me.txtFilterCmCode & "'"
End Sub

JR
Click to expand...

I have tried it. There is Error Message:

Run-time error'3464'
Data type mismatch in criteria expression
Debug:
Code: 
DoCmd.OpenForm "MainContractCost", , , stCmCodeFilter
 
What is you current complete code for your searchbutton click event? It looks like you are mixing the code from vbaInet and mine.

Also the popup form "MainContractCost" does it have the field [CM Code] in it's recordsource and is it a textfield or a numberfield?

The code I gave you copy it as it is, the only thing to match the name of your click button marked in red with what you have on your form.

Code: 
Private Sub [COLOR=red]Search[/COLOR]_Click()
If Not IsNull(Me.txtFilterCmCode) Then
   DoCmd.OpenForm "MainContractCost",,, "[CM Code]= '" & Me.txtFilterCmCode & "'"
End Sub

JR
 
JANR said:
What is you current complete code for your searchbutton click event? It looks like you are mixing the code from vbaInet and mine.

Also the popup form "MainContractCost" does it have the field [CM Code] in it's recordsource and is it a textfield or a numberfield?

The code I gave you copy it as it is, the only thing to match the name of your click button marked in red with what you have on your form.

Code: 
Private Sub [COLOR=red]Search[/COLOR]_Click()
If Not IsNull(Me.txtFilterCmCode) Then
   DoCmd.OpenForm "MainContractCost",,, "[CM Code]= '" & Me.txtFilterCmCode & "'"
End Sub

I have tried both you and vabInet. I got same result. I set txtFilterCmCode as Number in table and Form.
Click to expand...
 
Well if it is a number then remove the single quotes.

Code: 
Private Sub [COLOR=red]Search[/COLOR]_Click()
If Not IsNull(Me.txtFilterCmCode) Then
   DoCmd.OpenForm "MainContractCost",,, "[CM Code]= " & Me.txtFilterCmCode
End Sub

However this:
There is a Message: Run-time error '3075' Syntax (Missing operator) in query expression '([CM Code] = 200 A'
Click to expand...

whould suggest that it is a textfield since a number field can't have a alpha character in it, which confused me.

JR
 
Actually it's a number field. I should have spotted that. What that line is reading is:

([CM Code] = 200 AND ...

So if you're going to use the refurbished code based on yours then here:
Code: 
    Dim stCmCodeFilter As String

    If Len(Me.txtFilterCmCode & vbNullString) = 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        stCmCodeFilter = "[CM Code] = " & Me.txtFilterCmCode
        
        DoCmd.OpenForm "MainContractCost", , , stCmCodeFilter
    
        Me.Filter = stCmCodeFilter
        Me.FilterOn = True
    End If

If you're going with JANR's suspicion of you not needing the extra bits of code, then I would suggest you do it this way:
Code: 
If Len(Me.txtFilterCmCode & vbNullString) <> 0 Then
   DoCmd.OpenForm "MainContractCost",,, "[CM Code]= " & Me.txtFilterCmCode
End Sub
 
vbaInet said:
Actually it's a number field. I should have spotted that. What that line is reading is:

([CM Code] = 200 AND ...

So if you're going to use the refurbished code based on yours then here:
Code: 
    Dim stCmCodeFilter As String
 
    If Len(Me.txtFilterCmCode & vbNullString) = 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        stCmCodeFilter = "[CM Code] = " & Me.txtFilterCmCode
 
        DoCmd.OpenForm "MainContractCost", , , stCmCodeFilter
 
        Me.Filter = stCmCodeFilter
        Me.FilterOn = True
    End If

If you're going with JANR's suspicion of you not needing the extra bits of code, then I would suggest you do it this way:
Code: 
If Len(Me.txtFilterCmCode & vbNullString) <> 0 Then
   DoCmd.OpenForm "MainContractCost",,, "[CM Code]= " & Me.txtFilterCmCode
End Sub
Click to expand...

Thank you very much! Again it works for me!!!!
 
Sunnylei said:
Thank you very much! Again it works for me!!!!
Click to expand...

I trying to filter out text codes and limiting report to text codes range, I set CM Code as text type. I pasted the following codes in Event Procedure, however, there is an error come up: 'Run-time error '3075' Syntax error (missing operator) in query (expression '[CM Code]'245 AND ([CM Code] >=246)'. Can you have a look what is wrong. :confused:

Thanks

Code: 
Private Sub cmdPreview_Click()
    Dim strReport As String
    Dim strCmCodeField As String
    Dim strWhere As String
    Dim lngView As Long
     
   strReport = "rptMainContractLabourCosting"     
   strCmCodeField = "[CM Code]" 
    lngView = acViewPreview    
   
    'Build the filter string.
    If Not IsNull(Me.txtStartCmCode) Then
        strWhere = "(" & strCmCodeField & "'" & Format(Me.txtStartCmCode, 0) & ")"
    End If
    If Not IsNull(Me.txtEndCmCode) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strCmCodeField & " >= " & Format(Me.txtEndCmCode + 1, 0) & ")"
    End If
    
    'Open the report.
     DoCmd.OpenReport strReport, lngView, , strWhere
      Exit_Handler:
    Exit Sub
    Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation,     
        "Cannot open report"
    End If
    Resume Exit_Handler
End Sub
 
Your code is not in the format I gave you in my last post. Look at my last post and follow exactly that format.
 
vbaInet said:
Your code is not in the format I gave you in my last post. Look at my last post and follow exactly that format.
Click to expand...

Sorry, this is differ from last one. I'm trying to use different approach to set a filter for different types data. This form is a bridge between MainContractLabourCosting and Report. I set up two text boxes. One is txtStartCmcode, one is txtEndCmcode, use a preview button to show the report which contains CmCode which I expected range. this time I define [CmCode] filed as text type. I'm learning VBA, I don't know how to define text. I hope you give me a tip. Many Thanks
 
If you have a Text field then it should be in this format:
Code: 
strWhere = "[TheTextField] =[COLOR=Red] '[/COLOR]" & Me.txtControlName & "[COLOR=Red]'[/COLOR]"
You're using Not IsNull, but in the code I wrote it's Len(). Your code is fine if the Allow Zero Length property of the Text field is set to No.
 
You must log in or register to reply here.

Similar threads

J
Solved searching with a partial string
Replies
15
Views
907
John Sh
J
F
Search form
Replies
4
Views
898
bastanu
bastanu
smtazulislam
Solved Unbound Combo box need multiple value
Replies
18
Views
3,489
smtazulislam
smtazulislam
D
Clearing data in a form after clicking print report
Replies
9
Views
1,255
Ken Sheridan
Ken Sheridan
T
Solved Filter form after update with code
Replies
8
Views
1,116
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom