Results 1 to 4 of 4

Thread: combo box woes

  1. #1
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Unanswered: combo box woes

    My combo box has 6 columns, and (right now) 11 rows. The Row Source is a very simple query. The Bound Column data looks like this:

    CustInfo
    BAOH01
    BHIA01
    BHNY06
    GMAL01
    IMC*
    MMMO02
    MMMO04
    NDC*
    OPT*
    PBI*
    PSS*

    The AfterUpdate event follows:

    Code:
    Private Sub cmbSelRebCust_AfterUpdate()
    
        Dim Filters As String, LI As Integer
        
        If Me.cmbSelRebCust = "BAOH01" Then
            DoCmd.OpenReport Me.cmbSelRebCust.Column(5), acViewPreview, , Me.cmbSelRebCust.Column(1)
        ElseIf (Me.cmbSelRebCust = "GMAL01") Or (Me.cmbSelRebCust = "IMC*") Then
            Filters = Me.cmbSelRebCust.Column(1)
            If Me.cmbSelRebCust.Column(3) > " And " Then
                Filters = Filters & " " & Me.cmbSelRebCust.Column(3)
            End If
            DoCmd.OpenReport Me.cmbSelRebCust.Column(5), acViewPreview, , Filters, , _
                Me.cmbSelRebCust.Column(4)
        Else
            Filters = Me.cmbSelRebCust.Column(1)
            If Me.cmbSelRebCust.Column(3) > " And " Then
                Filters = Filters & " " & Me.cmbSelRebCust.Column(3)
            End If
            DoCmd.OpenReport Me.cmbSelRebCust.Column(LI, 5), acViewPreview, , Filters
        End If
        Me.cmdCloseForm.SetFocus
        Me.cmbSelRebCust.Visible = False
        Me.cmbSelRebCust_Label.Visible = False
        
    End Sub
    When I select a customer, it does NOTHING. I even inserted a 'Stop' line as the first statement in the event, and it doesn't even do that. Can somebody tell me the error of my ways? Whatever is left of my hair doesn't pull out well. Thanks a lot in advance.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might avoid the Filters command (these never work very well with odd characters). Or don't use it as a variable name! (I'd call it perhaps vFilter or something like that). MSAccess 'might' get confused on the reserved Filter word with Filters.

    I think what you're trying to do can be done much easier but I'm hoping this is what you're trying to do. A typical opening of a report might be like this (I'm guessing though based on your explanation). Notice the RptName variable designates the report to be opened while the vCriteria uses the designated column of the combobox to apply criteria (using the WHERE parameter when opening the report) to select only the records matching that criteria. If needed, you can use more than 1 variable for the criteria and open the report based on multiple criteria.

    Dim RptName as variant
    dim vCriteria as variant
    RptName = me!MyComboboxName
    Select case RptName
    Case "BAOH01"
    vCriteria = me.mycomboboxname.column(5)
    docmd.openreport "" & RptName & "",,"[FieldName] = '" & vCriteria & "'"
    case "XXXX"
    vCriteria = me.mycomboboxname.column(3)
    docmd.openreport "" & XXXX & "",,"[SomeOtherField] = '" & vCriteria & "'"
    ....
    ....
    end select

    You can also dim another variable for multiple criteria. ex:
    Case "XXXX"
    vCriteria = me.mycombobox.column(3)
    vCriteria2 = me.mycombobox.column(4)
    Docmd.openreport "" & XXXX & "",,"[SomeField] = '" & vCriteria1 & "' and [SomeField2] = '" & vCriteria2 & "'"

    As a last note, get rid of the * in the selection items. This tends to cause problems. Keep it simple with no odd characters such as: !@#$%^&*().
    Last edited by pkstormy; 11-24-09 at 16:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You know what the problem was? I was in an Access project - which I inherited from my boss, not in a database. Once I found that out, I opened a new database and imported all pertinent objects. Remind me to never inherit another Access project from my boss (tongue in cheek).

    At least now the d___ thing stops at the 'Stop' command, which it didn't do before, so I can do some troubleshooting.

    Thanks a bunch,

    SL

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the reply back Sam Landy. I hate those Access Projects.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •