Solved

opening form base on criteria from combo(s)

Posted on 2007-03-20
6
261 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:Karen Schaefer
  • 3
  • 3
6 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 18760108
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
 

Author Comment

by:Karen Schaefer
ID: 18760120
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
 
LVL 28

Expert Comment

by:omgang
ID: 18760195
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Karen Schaefer
ID: 18760336
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
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 18763836
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
 

Author Comment

by:Karen Schaefer
ID: 18765165
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now