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

iramalvarez · #1 (**permalink**) 09-01-10, 19:34

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

MikeTheBike · #2 (**permalink**) 09-02-10, 09:11

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

iramalvarez · #3 (**permalink**) 09-02-10, 14:33

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

MikeTheBike · #4 (**permalink**) 09-03-10, 10:14

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

iramalvarez · #5 (**permalink**) 09-03-10, 12:47

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

iramalvarez · #6 (**permalink**) 09-13-10, 18:10

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

MikeTheBike · #7 (**permalink**) 09-17-10, 13:56

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

iramalvarez · #8 (**permalink**) 09-17-10, 14:26

Again Mike, you are the MAN!!!!

Thanks.

Iram/mcp

iramalvarez · #9 (**permalink**) 09-29-10, 15:06

Sorry added post by accident.

How do I delete this post?

Internet Services

Web Development

Digital Marketing

Consumer Tech