Solved

opening form base on criteria from combo(s)

Posted on 2007-03-20
6
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 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