opening form base on criteria from combo(s)

Having issue that using the following code it opens new record and not a filtered record -

Private Sub cmdEditSR_Click()
On Error GoTo Err_cmdEditSR_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FE_SRForm"
    stLinkCriteria = (Requestor_ID = Me![cmbRequestor]) And (Project = Me![cmbProject]) And (Job_Group = Me![cmbJob_Group])
   
    DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit, acWindowNormal
Exit_cmdEditSR_Click:
    Exit Sub

Err_cmdEditSR_Click:
    MsgBox Err.Description
    Resume Exit_cmdEditSR_Click
   
End Sub
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Are all three criteria values type string?
stLinkCriteria = "Requestor_ID = '" & Me.cmbRequestor & "' And Project = '" & Me.cmbProject & "' And Job_Group = '" & Me.cmbJob_Group & "'"

OM Gang
0
Karen SchaeferBI ANALYSTAuthor Commented:
My form has combo boxes and the user can select 1 or all to determine the criteria for the form that is opening.

k
0
omgangIT ManagerCommented:
stLinkCriteria = ""
If Not IsNull(Me.cmbRequestor) Then
    stLinkCriteria = "Requestor_ID = '" & Me.cmbRequestor & "'"
End If
If Not IsNull(Me.cmbProject) Then
    If stLinkCriteria <> "" Then
        stLinkCriteria = stLinkCriteria & " And Project = '" & Me.cmbProject & "'"
    Else
        strLinkCriteria = "Project = '" & Me.cmbProject & "'"
    End If
End If
If Not IsNull(Me.cmbJob_Group) Then
    If stLinkCriteria <> "" Then
        stLinkCriteria = stLinkCriteria & " And Job_Group = '" & Me.cmbJob_Group & "'"
    Else
        stLinkCriteria = "Job_Group = '" & Me.cmbJob_Group & "'"
    End If
End If
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Karen SchaeferBI ANALYSTAuthor Commented:
thanks for your assist.

This doesn'g  quite work correctly  the above does not allow for and or situations.

Example Combo1 = Name and combo2 = "" and combo 3 = "" then the criteria would equal only combo1.  Your cuurent code does not allow for just 1 combo selected.

This code would return wher criteria = Combo1 is not null and combo2 is null and of course no records will be found.

How do I modify this code to handle this.

Karen
0
omgangIT ManagerCommented:
I'm not following you.  The routine I posted 'says'
If combo1 isn't null (i.e. combo1 has been selected) then set our criteria (WHERE clause) to
Requestor_ID = 'value of combo1'
If combo2 isn't null (i.e. combo2 has been selected) then check to see if we already have begun building criteria (WHERE clause)
   If a criteria expression exists (i.e. stLinkCriteria isn't a zero length string) then we'll add to it
      criteria string equals existing string plus And Project = 'combo2'
   Else (criteria expression doesn't exist yet)
      criteria string equals Project = 'combo2'
Regardless, if combo2 is null (i.e. combo2 hasn't been selected) then the criteria expression will be whatever it was when we got here.

The routine will allow for any circumstance of the three combo boxes being selected.
For testing I built a form with the three combos and the command button and added the following sub
Private Sub cmdEdit_SR_Click()

    Dim stLinkCriteria As String

    stLinkCriteria = ""
    If Not IsNull(Me.cmbRequestor) Then
        stLinkCriteria = "Requestor_ID = '" & Me.cmbRequestor & "'"
    End If
    If Not IsNull(Me.cmbProject) Then
        If stLinkCriteria <> "" Then
            stLinkCriteria = stLinkCriteria & " And Project = '" & Me.cmbProject & "'"
        Else
            stLinkCriteria = "Project = '" & Me.cmbProject & "'"
        End If
    End If
    If Not IsNull(Me.cmbJob_Group) Then
        If stLinkCriteria <> "" Then
            stLinkCriteria = stLinkCriteria & " And Job_Group = '" & Me.cmbJob_Group & "'"
        Else
            stLinkCriteria = "Job_Group = '" & Me.cmbJob_Group & "'"
        End If
    End If
   
    Debug.Print Nz(Me.cmbRequestor, "Null")
    Debug.Print Nz(Me.cmbProject, "Null")
    Debug.Print Nz(Me.cmbJob_Group, "Null")
    Debug.Print stLinkCriteria

End Sub

Here's the out put
(combo1 selected)
Bookcases/Cabinets
Null
Null
Requestor_ID = 'Bookcases/Cabinets'

(combo2 selected)
Null
AO2
Null
Project = 'AO2'

(combo3 selected)
Null
Null
 1
Job_Group = '1'

(combos 1 & 3 selected)
Bookcases/Cabinets
Null
 1
Requestor_ID = 'Bookcases/Cabinets' And Job_Group = '1'

OM Gang
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Here is my solution:  Thanks for your assist

   Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FE_SRForm"
    stLinkCriteria = ""
        If Not IsNull(Me.cmbRequestor) Then
            stLinkCriteria = "Requestor_ID = '" & Me.cmbRequestor & "'"
        End If
        If Nz(Me.cmbProject, vbstringNull) = "" Then
            stLinkCriteria = stLinkCriteria
        ElseIf Not IsNull(Me.cmbProject) Then
            If stLinkCriteria <> "" Then
                stLinkCriteria = stLinkCriteria & " And Project = '" & Me.cmbProject & "'"
            Else
                strLinkCriteria = "Project = '" & Me.cmbProject & "'"
            End If
        End If
        If Nz(Me.cmbJob_Group, vbstringNull) = "" Then
            stLinkCriteria = stLinkCriteria
        ElseIf Not IsNull(Me.cmbJob_Group) Then
            If stLinkCriteria <> "" Then
                stLinkCriteria = stLinkCriteria & " And Job_Group = '" & Me.cmbJob_Group & "'"
            Else
                stLinkCriteria = "Job_Group = '" & Me.cmbJob_Group & "'"
            End If
        End If

    DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit, acWindowNormal
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.