Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2010
    Location
    California
    Posts
    15

    Question Unanswered: Need VBA to open forms with specific queries based upon a list box

    Hello,

    A developer on a different form started to help me with the below vba, but no further response. I am having trouble getting it to work.
    Basically I have a 3 column list box on a form called "NavPageCaseStatusListBox1" and I have a command button with the below vba. I need the below vba to open the form name in the list box and change the Record Source of that form to the query name in the list box. I will eventually have many records in the List Box and need to fix this.

    Option Compare Database
    Option Explicit

    Private Sub CaseStatus1_Click()
    With Me.NavPageCaseStatusListBox1

    If .ListIndex < 0 Then
    MsgBox "Please Choose a Case Status and/or Team"
    Else
    '0 = Status Description
    '1 = FormName
    '2 = QueryName

    If CurrentProject.AllForms(.Column(1, .ListIndex)).IsLoaded Then
    Forms(.Column(1, .ListIndex)).RecordSource = .Column(2, .ListIndex)
    Else
    DoCmd.OpenForm .Column(1, .ListIndex), acNormal, .Column(2, .ListIndex)
    End If
    End If
    End With
    End Sub

    When I select a row in the list box that has a description, FormName and Queryname and i click on the "CaseStatus1" button that runs the above vba, a form opens but it opens the wrong form and doesn't put in the Queryname into the "Record Source" of that form. It is actually opening the form above of my selection and NOT putting in the Record Source. How do I get it to select the corresponding form AND put the queryname into the record source?

    Should I have 0 or 1 for the "Bound Column" on the "NavPageCaseStatusListBox1" List Box or should I change the VBA? If so how should I change it?

    Your help is greatly appreciated.

    Iram/mcp

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think rewriting your IF statement like this
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub CaseStatus1_Click()
        With Me.NavPageCaseStatusListBox1
        
            If .ListIndex < 0 Then
                MsgBox "Please Choose a Case Status and/or Team"
            Else
                '0 = Status Description
                '1 = FormName
                '2 = QueryName
            
                If Not CurrentProject.AllForms(.Column(1, .ListIndex)).IsLoaded Then
                    DoCmd.OpenForm .Column(1, .ListIndex), acNormal
                End If
                Forms(.Column(1, .ListIndex)).RecordSource = .Column(2, .ListIndex)
                Forms(.Column(1, .ListIndex)).Requery ' not sure this is necessary but cannot hurt?
            End If
        End With
    End Sub
    may sort the lack of RecordSource update.

    With regard to opening the wrong form, its impossible to say without seeing the ListBox RowSource query, but that is where I would start!

    You coud try adding
    MsgBox .Column(1, .ListIndex)
    at a appropreate position to check what name is being returned from the ListBox?

    HTH


    MTB

  3. #3
    Join Date
    Sep 2010
    Location
    California
    Posts
    15

    Need VBA to open forms with specific queries based upon a list box

    Mike you are the man! I had to add +1 after each of the .listindex's because I am using a column header but now the correct form and query are working together!

    There is just one little problem. One of the forms that I have in the list box opens but the "Form Load" event doesn't launch, see vba below.

    Private Sub Form_Load()
    DoCmd.Maximize
    Me.Recordset.FindFirst "StatsDate=" & Format(Date, "\#m\/d\/yyyy\#")
    End Sub


    Is there anyway I can modify your vba below to allow the forms vba to run?


    Below is the current vba.

    Private Sub CaseStatus1_Click()
    With Me.NavPageCaseStatusListBox1

    If .ListIndex < 0 Then
    MsgBox "Please Choose a Case Status and/or Team"
    Else
    '0 = Status Description
    '1 = FormName
    '2 = QueryName

    If Not CurrentProject.AllForms(.Column(1, .ListIndex + 1)).IsLoaded Then
    DoCmd.OpenForm .Column(1, .ListIndex + 1), acNormal
    End If
    Forms(.Column(1, .ListIndex + 1)).RecordSource = .Column(2, .ListIndex + 1)
    'Forms(.Column(1, .ListIndex + 1)).Requery ' not sure this is necessary but cannot hurt?
    End If
    End With
    End Sub



    Your help is greatly appreciated.
    Iram/mcp
    Iram/mcp

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Not sure what youmean, but I would want to now why the Form_Load event doesn’t fire!?

    However, I my guess is correct I suggest this mod
    Code:
     Private Sub CaseStatus1_Click()
        With Me.NavPageCaseStatusListBox1
        
            If .ListIndex < 0 Then
                MsgBox "Please Choose a Case Status and/or Team"
            Else
                '0 = Status Description
                '1 = FormName
                '2 = QueryName
            
                If Not CurrentProject.AllForms(.Column(1, .ListIndex + 1)).IsLoaded Then
                    DoCmd.OpenForm .Column(1, .ListIndex + 1), acNormal
                End If
                With Forms(.Column(1, .ListIndex) + 1)
                    .RecordSource = .Column(2, .ListIndex + 1)
                    .Requery ' not sure this is necessary but cannot hurt?
                    .Recordset.FindFirst "StatsDate=" & Format(Date, "\#m\/d\/yyyy\#")
                    DoCmd.Maximize
                End With
            End If
        End With
    End Sub
    ?

    MTB

  5. #5
    Join Date
    Sep 2010
    Location
    California
    Posts
    15

    Wink Need VBA to open forms with specific queries based upon a list box

    Again Mike the Bike you are awesome!

    Thanks for your help. You saved me from tons of work of having individual forms and queries.

    How do I close this thread and give you credit for answering my questions?



    Iram
    Iram/mcp

  6. #6
    Join Date
    Sep 2010
    Location
    California
    Posts
    15

    Open Forms based on List Box

    Mike the Bike,

    Below is the vba you gave me and it works beautifully but I need to add a little more functionality. Currently I have about 200 queries in my db because for every query the users need I need to make a "My", "All", and "Team" version of the criteria which means that my list boxes become cluttered big time.

    Right now I have the below ...OkButton vba but what I need to do is remove the Ok button and add three seperate buttons in its place on the form: "My", "All", "Team". When I click the "My" button I need a modified version of the below vba to add "My" to the end of the "QueryName". And I need seperate vba for the All and Team buttons too, where "All" is added to the end of "QueryName" for the All Button and "Team" is added to the end of "QueryName" in the Team Button vba.

    Would you be able to help with this?


    Private Sub ListBoxMiscOkButton_Click()
    With Me.ListBoxMisc

    If .ListIndex < 0 Then
    MsgBox "Please Choose a Case Status and/or Team"
    Else
    '0 = Status Description
    '1 = FormName
    '2 = QueryName
    'MsgBox .Column(1, .ListIndex + 1) Informs you the value being queried for column 1, for troubleshooting purposes only
    'MsgBox .Column(2, .ListIndex + 1) Informs you the value being queried for column 2, for troubleshooting purposes only

    If Not CurrentProject.AllForms(.Column(1, .ListIndex + 1)).IsLoaded Then
    DoCmd.OpenForm .Column(1, .ListIndex + 1), acNormal
    End If
    Forms(.Column(1, .ListIndex + 1)).RecordSource = .Column(2, .ListIndex + 1)

    End If
    End With
    Me.Requery
    Me.Refresh

    End Sub



    Thanks.
    Iram/mcp
    Iram/mcp

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    Sorry for the delay in replying, but have been on hols.

    If my interpretation of you was request is correect, then I would move the current code to a module level sub procedure that take a string argument for the 'query name suffix' and then concatenate the suffix to '.Column(2, .ListIndex + 1)' like this
    Code:
    Sub OpenForm(ByVal strQuerySuffix As String)
        With Me.ListBoxMisc
    
            If .ListIndex < 0 Then
                MsgBox "Please Choose a Case Status and/or Team"
            Else
                '0 = Status Description
                '1 = FormName
                '2 = QueryName
                
                If Not CurrentProject.AllForms(.Column(1, .ListIndex + 1)).IsLoaded Then
                    DoCmd.OpenForm .Column(1, .ListIndex + 1), acNormal
                End If
                Forms(.Column(1, .ListIndex + 1)).RecordSource = .Column(2, .ListIndex + 1) & strQuerySuffix
                
            End If
        End With
        
        Me.Requery
        Me.Refresh
    End Sub
    and call the procedure from the three button's click event like this
    Code:
    Private Sub cmdMy_Click()
        OpenForm "My"
    End Sub
    
    Private Sub cmdAll_Click()
        OpenForm "All"
    End Sub
    
    Private Sub cmdTeam_Click()
        OpenForm "Team"
    End Sub
    Hope that makes sense.

    MTB

  8. #8
    Join Date
    Sep 2010
    Location
    California
    Posts
    15

    Concatenating

    Again Mike, you are the MAN!!!!


    Thanks.

    Iram/mcp
    Iram/mcp

  9. #9
    Join Date
    Sep 2010
    Location
    California
    Posts
    15

    Need to change acNormal so that a form opens in Data Sheet View

    Sorry added post by accident.

    How do I delete this post?
    Last edited by iramalvarez; 09-29-10 at 17:06. Reason: Mistake, need to delete this post.
    Iram/mcp

Tags for this Thread

Posting Permissions

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