How to pass a filter to a form that I am opening with Cmd Button

[Callreluctance]

I wrote up some code to pass a filter onto a form that I am opening. Basically I want the user to be able to specify in one or all of three text boxes (LastName, Company, and ProjDev) requirements or a letter to start with from a form (MainPage), and restrict the form they are opening (Contacts) with a command button to the records in the filter. Here is the code, any ideas?

Private Sub OpenContactsFiltered_Click()

On Error GoTo Err_Openform_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Contacts"
DoCmd.Openform stDocName, , , stLinkCriteria

Dim strFilter As String 'Used to build a filter

strFilter = "" 'Start with a blank filter
'Check to see if LastName is filled in
If (Not IsNull(Me![LastName])) Then
'Build the filter
strFilter = "([LastName])"
End If

'Check to see if Company is filled in
If (Not IsNull(Me![Company])) Then
'If the filter is not blank, then AND this criteria
If (Not IsNull(strFilter)) Then
strFilter = strFilter + " AND "
strFilter = strFilter & "([Company])"
Else
strFilter = "([Company])"
End If
End If

'Check to see if Project Developer is selected
If (Not IsNull(Me![ProjDev])) Then
'If the filter is not blank, then AND this criteria
If (Not IsNull(strFilter)) Then
strFilter = strFilter + " AND "
strFilter = strFilter & ([ProjDev] = "& Str(me![ProjDev]) & &quot
Else
strFilter = "([ProjDev])"
End If
End If

If Len(strFilter) Then
[Forms]![Contacts].Filter = strFilter
[Forms]![Contacts].FilterOn = True
End If

Exit_Openform_Click:
Exit Sub

Err_Openform_Click:
MsgBox Err.Description
Resume Exit_Openform_Click
End Sub

The problem is the form is opened but the filter is not applied. I do not want the user to have to click an apply filter to the new form (Contacts).

Thanks,

 

[chrissie1]

try adding this

If Len(strFilter) Then
[Forms]![Contacts].Filter = strFilter
[Forms]![Contacts].FilterOn = True
[forms]![contacts].requery
End If
"What a wonderfull world" - Louis armstrong

 

[Callreluctance]

No, I still get the same problem. I then moved the Open Command to the end, and added the StrFilter to the command like this. I now get a type mismatch message. I thought it might be the lookupfield versus a text with ProjectDev, but I commended it out, and I still get a type mismatch. strFilter = "([LastName])" should make LastName text correct?

 

[chrissie1]

I think you have some things confused
the trouble you have is difficult to explain but i think if you want to add the value you can see on the screen to a string value you need something like this

strfilter = "([company] = '" & me!company & "')"

where the first [company] is the name of the field on the second form
the things you are writing just put this in the filter

[company] AND [projectdev] AND [lastname]

while you need this

[company] = 'companyname' AND [lastname] = 'hisname'

hope that helps "What a wonderfull world" - Louis armstrong