Genericise buttons and list calls 1

[Triacona]

Dear All,
Thanks for a great forum.

I want to make my buttons generic for all lists in my report forms.

I have several buttons and each list has their own, Appeals reports, DC reports ect.
The buttons do the exact same thing for all lists.

So I have a Run Report button, a Run Query button etc.

Please see the code below:

Private Sub EnRunQuery_Click()
On Error GoTo Err_EnRunQuery_Click

Dim stDocName As String
    stDocName = ListEn.Column(2)
    
   If Not QueryExists(stDocName) Then
        MsgBox stDocName & " query doesn't exist, RUN the REPORT!", vbExclamation, "RUN The REPORT!!!"
    ElseIf Not Me.txtEndDate.Enabled = True Then
        DoCmd.OpenQuery stDocName, acNormal, acEdit
        DoCmd.Maximize
    ElseIf IsNull(Me.txtStartDate And Me.txtEndDate) Then
        DoCmd.RunMacro "MsgBoxNoDate"
    Else: DoCmd.OpenQuery stDocName, acNormal, acEdit
        DoCmd.Maximize
    End If

Exit_EnRunQuery_Click:
    Exit Sub

Err_EnRunQuery_Click:
    MsgBox Err.Description
    Resume Exit_EnRunQuery_Click
    
End Sub

The

stDocName = ListEn.Column(2)

is want I want to make generic.
So I need code to recognise which List is currently selected and then make that a variable and then use the dot notation for that.
So for example

stDocName = CurrentList.Column(2)

So the variables type has to be of list to get the dot notation.

Any help with the above would be greatly appreciated.
Thanks.

Thank you,

Kind regards

Triacona

 

[Andrzejek]

This is Excel VBA, you may need to 'translate' [red]it[/red] to Access VBA...

In General Declaration:[tt]
Dim [blue]lstMyListBox[/blue] As [red]MSForms.ListBox[/red][/tt]

In every ListBox (Click?) Event:

Private Sub ListEn_Click()[blue]
Set lstMyListBox = ListEn[/blue]
...
End Sub

So you can use it here in one place:

Private Sub EnRunQuery_Click()
On Error GoTo Err_EnRunQuery_Click

Dim stDocName As String
stDocName = [blue]lstMyListBox[/blue].Column(2)
    
...

Or, you may simple re-position your declaration of [tt]stDocName[/tt] and assign its value in different list boxes instead of your [tt]EnRunQuery_Click()[/tt]:

Option Explicit
Dim stDocName As String

Private Sub ListEn_Click()
stDocName = ListEn.Column(2)
...
End Sub

---- Andy

There is a great need for a sarcasm font.