command button to open and filter form

[tizwaz]

I want to have one form which can be opened filtered 3 ways dependant on which command button I click.

HOw do I code the command button to apply the filter that the field office is equal to a certain name?

 

[fneily]

There are a number of ways. Don't know how your form is set up, but I would have a combobox(dropdown) with the names. When one is selectd, on the AfterUpdate event of the combobox, I'd put:
DoCmd.OpenForm "YourFormName"

The RecordSource for the form would be a query with a criteria on the name field that looks like:
Forms![FormnameWithCombobox]!Me![comboboxname]

Now you don't need multiple command buttons.

 

[PHV]

HOw do I code the command button to apply the filter that the field office is equal to a certain name
In the Click event procedure of your button:

Me.FilterOn = False
Me.Filter = "[field office]='" & Me![certain name] & "'"
Me.FilterOn = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tizwaz]

I can't get this to work. The combo box idea sound good but one of the options I want is for the form to open with all records. The other 2 options is for it to filter by a particular office.

I have tried putting this code in the on click event of my command button but when the form opens all the records are there. What am I doing wrong?

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stroffice As String
stroffice = "City Tower"

stDocName = "frmAllIssues"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me.FilterOn = False
Me.Filter = "[office] = '" & stroffice & "'"
Me.FilterOn = True

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

 

[PHV]

What about this ?

Private Sub Command8_Click()
    Dim stroffice As String
    stroffice = "City Tower"
    DoCmd.OpenForm "frmAllIssues", , , "[office] = '" & stroffice & "'"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tizwaz]

Thanks v much that works fine

 

[tizwaz]

sorry to be a pain - could you explain how the syntax works. I'm now trying to do a different command button to open a form based on 2 criteria. I am trying to say that [category] = "maintenance" and isnull([dateresolved]). I don't understand where to put the quotes and the ampersands

 

[Remou]

Have you had a chance to read about the OpenForm method?

http://msdn.microsoft.com/en-us/library/aa141520(office.10).aspx

 

[tizwaz]

Thanks I've read that and have partly got what I want working. The following worked when I just had the 1st part but when I added isnull etc it didn't return anything

DoCmd.OpenForm stDocName, , , "[category] = 'maintenance' & isnull ([dateresolved])

 

[PHV]

DoCmd.OpenForm stDocName, , , "category='maintenance' AND dateresolved Is Null"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886