If function help...

[MOTOWN44]

Hi guys & gals

Can some try and give me a hand with some VBA stuff?

Im not necessarily new to access but I am new to the script side of it (hard to believe I know) because I’ve never really had the need to use it in my job.

Basically I have a search form with the location of the 6 sites of our business in a combo.
I also have other combos with things such as case status in etc and I can search my records easily using this.

The issue has arisen because they now want to be able to search for new cases that haven’t been assigned to anybody yet on the form.
My solution is just make a new query with the value of the Site Combo box on the search form as the criteria with the Assessor column of the query as Is Null.

This works fine but me boss doesn’t want to clutter the search form with another combo box.
Is there a way of using the site combo box on the search form to search for both New and all record assigned to a specific site?

Now my idea was to add a tick box to the form and put some sort of if statement in the onClick event of the search button of the form (not the tick box) to say search all records assigned to London but if the box is ticked only search unassigned records.

My attempt at this is below. Feel free to laugh lol!

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

If Me.[NewTick] = True Then

Dim stdocname As String
Dim stlinkcriteria As String

stdocname = "FrmNEW"
DoCmd.OpenForm stdocname, , , stlinkcriteria

Else

Dim stdocname As String
Dim stlinkcriteria As String

stdocname = "FrmRESULTS"
DoCmd.OpenForm stdocname, , , stlinkcriteria

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

Now I’ve used excel a lot in the last and have written this like an excel IF formula but it doesn’t seem to work.

Any help will be appreciated.
Thanks J


Key –

NewTick = tick box to signify I want to search for new cases.
FrmNEW = form attached to my new query that searches unassigned cases.
FrmRESULTS = form that displays the results of a normal search on the search form

 

[DCrake]

You could acutally make it more neater by bringing up a message box asking the user if they want all records or not.

If MsgBox("Do you want to view all records?",vbQuestion+vbYesNo,"Apply Filter") = vbYes Then

  DoCmd.This

Else

   DoCmd.That

End If

You can still open the same form but apply open arguments to it to filter records or not.

David

 

[MOTOWN44]

do i apply this to the form/search button or tick box.
i would assume the search button but i just want to be sure lol.

also will this allow me to search as before ?