Can't seem to call a Macro within Access (1 Viewer)

kengooch

Member
Local time
Today, 06:30
Joined
Feb 29, 2012
Messages
137
I have two buttons on a form that are designed to clear my multi-select list boxes. I was trying to call the first macro from the 2nd button so that I wouldn't have to duplicate the code. I tried the CALL statement which works in Excel but then found a post that said in Access VBA to use DoCmd.RunMacro "bClrFilters" then another post that said the same thing except to use parenthasis DoCmd.RunMacro (bClrFilters) neither one works. The bClrFilters macro is part of a form named fAccListMSLB the clear macro is initiated by a click and is called, Private Sub bClrFilters_Click() if I create an exact duplicate of that macro and then rename it to Private Sub bClrFilters2_Click() and put that name on the 2nd button it obviously works. But there has to be a way to call an existing macro in Access like you can in excel. Any help would be appreciated!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:30
Joined
May 7, 2009
Messages
19,169
you can try creating a Public function that will Clear the selection from the Listbox or Combobox:
Code:
Public Function fncClearList(ByRef lst As Object)
    ' arnelgp
    Dim i As Integer
    If TypeOf lst Is ListBox Then
        For i = 0 To lst.ListCount - 1
            lst.Selected(i) = False
        Next i
    ElseIf TypeOf lst Is ComboBox Then
        lst = ""
    End If
End Function

then call the function on the Click event of your Button:

private sub buttonName_Click()
Call fncClearList(Me!theListboxName)
end sub

//////////////
note that you need to replace with actual control name the bold lettered text.

Edit:
another demo below:
 

Attachments

  • clearList.accdb
    388 KB · Views: 168
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 13:30
Joined
Sep 21, 2011
Messages
14,041
Are we talking macros here or vba code?
Macros in Excel are just Vba code
Call the click event of the first button, but if it does the same thing why the need for two buttons?
 

kengooch

Member
Local time
Today, 06:30
Joined
Feb 29, 2012
Messages
137
Are we talking macros here or vba code?
Macros in Excel are just Vba code
Call the click event of the first button, but if it does the same thing why the need for two buttons?
It is vba code. and the dual buttons is simply for aesthetics.
You would think that this would be as simple as it is in Excel...
 

kengooch

Member
Local time
Today, 06:30
Joined
Feb 29, 2012
Messages
137
you can try creating a Public function that will Clear the selection from the Listbox or Combobox:
Code:
Public Function fncClearList(ByRef lst As Object)
    ' arnelgp
    Dim i As Integer
    If TypeOf lst Is ListBox Then
        For i = 0 To lst.ListCount - 1
            lst.Selected(i) = False
        Next i
    ElseIf TypeOf lst Is ComboBox Then
        lst = ""
[QUOTE="arnelgp, post: 1827028, member: 64504"]
you can try creating a Public function that will Clear the selection from the Listbox or Combobox:
[CODE]Public Function fncClearList(ByRef lst As Object)
    ' arnelgp
    Dim i As Integer
    If TypeOf lst Is ListBox Then
        For i = 0 To lst.ListCount - 1
            lst.Selected(i) = False
        Next i
    ElseIf TypeOf lst Is ComboBox Then
        lst = ""
    End If
End Function

then call the function on the Click event of your Button:

private sub buttonName_Click()
Call fncClearList(Me!theListboxName)
end sub

//////////////
note that you need to replace with actual control name the bold lettered text.

Edit:
another demo below:

End Function[/CODE] End If

then call the function on the Click event of your Button:

private sub buttonName_Click()
Call fncClearList(Me!theListboxName)
end sub

//////////////
note that you need to replace with actual control name the bold lettered text.

Edit:
another demo below:
[/QUOTE]
you can try creating a Public function that will Clear the selection from the Listbox or Combobox:
Code:
Public Function fncClearList(ByRef lst As Object)
    ' arnelgp
    Dim i As Integer
    If TypeOf lst Is ListBox Then
        For i = 0 To lst.ListCount - 1
            lst.Selected(i) = False
        Next i
    ElseIf TypeOf lst Is ComboBox Then
        lst = ""
    End If
End Function

then call the function on the Click event of your Button:

private sub buttonName_Click()
Call fncClearList(Me!theListboxName)
end sub

//////////////
note that you need to replace with actual control name the bold lettered text.

Edit:
another demo below:
Wow... that's a lot of coding, and if I am seeing what you're saying, I would have to include each of the 6 mutli-select list boxes into that code. So what I already did in creating a 2nd ClrFilters2 macro would be sort of the same thing. Here is a picture of the form and the code that generates it.

Code:
Option Compare Database
Private Sub mslBox1_Click()
    Me.Form.Filter = vSetFilters
    Me.Form.FilterOn = True
    Me.Form.Refresh
End Sub
Private Sub mslBox2_Click()
    Me.Form.Filter = vSetFilters
    Me.Form.FilterOn = True
    Me.Form.Refresh
End Sub
'Third List Box
    Me.Form.Filter = vSetFilters
    Me.Form.FilterOn = True
    Me.Form.Refresh
End Sub
'Fourth List Box
    Me.Form.Filter = vSetFilters
    Me.Form.FilterOn = True
    Me.Form.Refresh
End Sub
'Fifth List Box
Private Sub mslBox5_Click()
    Me.Form.Filter = vSetFilters
    Me.Form.FilterOn = True
    Me.Form.Refresh
End Sub
Private Sub mslBox6_Click()
'Sixth List Box
    Me.Form.Filter = vSetFilters
    Me.Form.FilterOn = True
    Me.Form.Refresh
End Sub

Public Function vSetFilters() As String
On Error GoTo vSetFilters_Err
' Assign Variables
    Dim vIndvSel As String
    Dim varItem As Variant
    Dim vListBox1, vListBox2, vListBox3, vListBox4, VListBox5, mslBox6 As Control
'Define List Boxes
  Set vListBox1 = Me.mslBox1
  Set vListBox2 = Me.mslBox2
  Set vListBox3 = Me.mslBox3
  Set vListBox4 = Me.mslBox4
  Set VListBox5 = Me.mslBox5
  Set VListBox6 = Me.mslBox6
'1st Multi Select List Box
    Select Case vListBox1.ItemsSelected.Count
    Case Is = 1
    If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
        vIndvSel = vIndvSel & "ABS([tProvID])='"
    For Each varItm In vListBox1.ItemsSelected
        vIndvSel = vIndvSel & vListBox1.ItemData(varItm) & "'"
    Next varItm
   Case Is > 1
    If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
        vIndvSel = vIndvSel & "ABS([tProvID]) IN ("
        i = 0
    For Each varItm In vListBox1.ItemsSelected
        If i > 0 Then
            vIndvSel = vIndvSel & ", "
    End If
        i = i + 1
        vIndvSel = vIndvSel & Chr(34) & vListBox1.ItemData(varItm) & Chr(34)
    Next varItm
        vIndvSel = vIndvSel & ") "
    End Select
'Assign String to Isolate Records
    vSetFilters = vIndvSel
'2nd Multi Select List Box
    Select Case vListBox2.ItemsSelected.Count
    Case Is = 1
    If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
        vIndvSel = vIndvSel & "ABS([tPathID])='"
'(Duplicate code as noted above in 1st box)

'3rd Multi Select List Box
     code...
'4th Multi Select List Box
     code...
'5th Multi Select List Box
    code...
'6th Multi Select List Box
     Code...
'Assign String to Isolate Records
    vSetFilters = vIndvSel
vSetFilters_Exit:
    Exit Function
vSetFilters_Err:
     code....
    Resume vSetFilters_Exit
    End Select
End Function
Private Sub bClrFilters_Click()
' Clear all  Multi-select List Boxes with a Clear Button    Dim iCount As Integer
'mslBox1
    For iCount = Me!mslBox1.ListCount To 0 Step -1
        Me!mslBox1.Selected(iCount) = False
    Next iCount
'mslBox2
    For iCount = Me!mslBox2.ListCount To 0 Step -1
        Me!mslBox2.Selected(iCount) = False
    Next iCount
'mslBox3
    For iCount = Me!mslBox3.ListCount To 0 Step -1
        Me!mslBox3.Selected(iCount) = False
    Next iCount
'mslBox4
    For iCount = Me!mslBox4.ListCount To 0 Step -1
        Me!mslBox4.Selected(iCount) = False
    Next iCount
'mslBox5
    For iCount = Me!mslBox5.ListCount To 0 Step -1
        Me!mslBox5.Selected(iCount) = False
    Next iCount
'mslBox6
    For iCount = Me!mslBox6.ListCount To 0 Step -1
        Me!mslBox6.Selected(iCount) = False
    Next iCount
'Reset the form to show all records
    Me.Filter = ""
    Me.FilterOn = False
End Sub

Private Sub bClrFilters2_Click()
' Clear all  Multi-select List Boxes with a Clear Button
'Attempt to call the bClrFilters macro
   DoCmd.RunMacro (bClrFilters)   'Attempt to call macro above
End Sub

Private Sub Form_Open(Cancel As Integer)
'Open form to specific size and location
On Error Resume Next
With DoCmd
    .SelectObject acForm, "fAccListMSLBFilter"
    .MoveSize 3400, 300, 16150, 11150
End With
   With Me.Recordset
      .MoveLast
      .Move -14
   End With
   luAccNum = ""
   vFilterSpec = ""
   luCode = ""
   luPath = ""
   DoCmd.GoToControl "vFilterSpec"
End Sub

Private Sub bRefresh_Click()
    Me.Requery
    Me.Refresh
   With Me.Recordset
      .MoveLast
      .Move -14
   End With
   luAccNum = ""
   vFilterSpec = ""
   luCode = ""
   luPath = ""
   DoCmd.GoToControl "bEdit"
   DoCmd.RunMacro (bClrFilters)    'Attempt to call macro not working
End Sub
 

Attachments

  • Screenshot.png
    Screenshot.png
    3.1 MB · Views: 175

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:30
Joined
May 7, 2009
Messages
19,169
on your code, you remove this:

DoCmd.RunMacro (bClrFilters)

and replace it with:

Call bClrFilters_Click()
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2002
Messages
42,970
You CANNOT reference procedures or controls of a form UNLESS that form is open. That is why @arnelgp told you to make a public module and pass in the control name. You do need a line of code for each listbox to call the common function.

And PS - In Access, Macros and VBA are completely different, referring to both as macros will only confuse yourself and the people trying to help you.
 

Users who are viewing this thread

Top Bottom