Solved

opening form base on criteria from combo(s)

Posted on 2007-03-20
6
301 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

685 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