Solved

opening form base on criteria from combo(s)

Posted on 2007-03-20
6
273 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

863 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

18 Experts available now in Live!

Get 1:1 Help Now