module - variable procedure to detect which button was clicked 2

[sedenter]

First of all I'm not even an amateur in access VBA, I'm the definition of beginner. So, if I'm not explaining very well what I need, please try to be patient with me . Thank in advance!

I'm creating a database in Access 2016 (with the help of one colleague) which looks like an old software with toolbar and submenus and it seems very tedious to write the same procedure for every click or MouseDown, therefore I'm trying to find a way to introduce a procedure (INSIDE A MODULE) which uses a variable to detect the button which was clicked/hovered over inside any form of my project. I also need a variable to detect the last form opened.

I tried to use two text boxes so I can extract the names of every button/form and use the value inside these text boxes to create the variable mentioned above.

When I started to work on this project I named every button using the caption of the button & the extension "bt"

Example:
"File" button is named "filebt"

I used the same method on forms and subforms:

Example:
When I press the "File" button it opens a form, which is named "filefm" inside a subform, which is called "filesfm"


'---"File" button action---
'-click action-
Private Sub filebt_Click()
btnmbx = "File"
If Form_filefm.Visible = False Then
Form_filefm.Visible = True
Form_Initialize.Filebt.SetFocus
Form_editfm.Visible = False
Form_purpfm.Visible = False
Else: Form_filefm.Visible = False
End If

If Form_filefm.Visible = True Then
fmnmbx.Value = Filebt.Caption
Else: fmnmbx.Value = ""
End If
End Sub
'-hover action-
Private Sub Filebt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
btnmbx = "File"
'btnmbx - BuTtonNaMeBoX
If Form_editfm.Visible = True Or (Form_optionsfm.Visible = True) Then
Form_editfm.Visible = False
Form_filefm.Visible = True
Form_Initialize.Filebt.SetFocus
Form_editfm.Visible = False
Form_editfm.Visible = False
Form_purpfm.Visible = False
fmnmbx.Value = "File"
'fmnmbx - ForMNaMeBoX
End If
End Sub


This project will suffer many changes until we're going to finish it, so I figured I will need some variables to be able to call a procedure instead of writing it every time I'm adding a button or a form.

Thanks again!

Eugen

 

[Andrzejek]

A couple of suggestions before any help.
1. You may want to use some common VBA Object Naming Conventions which would improve the communication between programmers, especially when you ask for help outside your office (like here on TT)
2. Use [tt]Option Explicit[/tt] at the top of your code
3. Please, format your code in your posts/replies with TGML tags. It is a LOT easier to read and see what's going on. I.e.

Private Sub filebt_Click()

btnmbx = "File"

If Form_filefm.Visible = False Then
    Form_filefm.Visible = True
    Form_Initialize.Filebt.SetFocus
    Form_editfm.Visible = False
    Form_purpfm.Visible = False
Else 
    Form_filefm.Visible = False
End If
...
End Sub

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

On your form you can make event procedures that call the same procedure, and then pass the procedure a reference to the button that was selected.

Private Sub EditBt_Click()
  ShowSubForm Me.EditBt
End Sub
Private Sub EditBt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
  ShowSubForm Me.EditBt
End Sub
Private Sub FileBt_Click()
  ShowSubForm Me.FileBt
End Sub
Private Sub FileBt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
 ShowSubForm Me.FileBt
End Sub

Here I made two buttons and the buttons call the procedure. I put the procedure in a standard module so that I could use this on multiple forms. The following code could be reused for hundred of buttons and subforms.

Public Sub ShowSubForm(btn As Access.CommandButton)
  Dim ParentForm As Access.Form
  Dim subForm As Access.subForm
  Dim subFormName As String
  Dim WasVisible As Boolean
  'The parent of a control is the form
  Set ParentForm = btn.Parent
  'I think you want to show the subform if not shown and hide if it is
  subFormName = Replace(btn.Name, "bt", "Sfm")
  Set subForm = ParentForm.Controls(subFormName)
  'Determine if the subform already open
  WasVisible = subForm.Visible
  'easier to just close everything then figure out what to reshow
  CloseAllSubs ParentForm
  'If was not already visible then reopen
  subForm.Visible = Not WasVisible
End Sub
Public Sub CloseAllSubs(frm As Access.Form)
  frm.Controls("FileSFM").Visible = False
  frm.Controls("editSFM").Visible = False
  frm.Controls("optionsSFM").Visible = False
  ....
End Sub

If I have a reference to a button, I also can find out the form it is on by the Parent property. When you are hiding and showing controls, I take the short cut and hide all and then show the one I want to show, Instead of a bunch of if checks to see if it is already visible.
A couple of other tricks
1) look at the active control property, this will return the control with focus. So if a button is clicked you know which one through the active control
2) look at the tag property. You can put "instructions" in there. Example would be if file btn clicked then show"FileSFM". I can put FileSFM in the tag property and write code like
me.controls(me.activeControl.tag).visible = true
which would resolve to
me.controls(FileSFM).visible = true
3) You can also loop all controls on form and do something

dim ctrl as access.control
for each ctrl in me.controls
  if ctrl.controltype = acsubform then
   ctrl.visible = false
  end if
next ctrl

4) To toggle visibility you can avoid a bunch of if thens
instead of

   if x = "SubForm1" then
     me.subform1.visible = true
  else
    me.subform1.visible = false
  end if

to

  me.subform1.visible = (x = "Subform1")

or to do the oppossite (turn on if on, turn of if off)

me.subform1.visible = not me.subform1.visible

 

[Andrzejek]

Based on MajP's code, if you want to differentiate between Click and MouseMove in the same Sub (I don't think you want the same behavior...?), you can do:

Private Sub EditBt_Click()
  [blue]Call[/blue] ShowSubForm(Me.EditBt[blue], 0[/blue])
End Sub
Private Sub EditBt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
  [blue]Call[/blue] ShowSubForm(Me.EditBt[blue], 1[/blue])
End Sub

and in the Module:

Public Sub ShowSubForm(btn As Access.CommandButton[blue], i As integer[/blue])
  Dim ParentForm As Access.Form
  Dim subForm As Access.subForm
  Dim subFormName As String
  Dim WasVisible As Boolean [green]
'i = 0 is a Click
'i = 1 is a MouseMove[/green]
...

Using the word [tt]Call[/tt] to 'call' a Sub is a personal coding preference.


---- Andy

There is a great need for a sarcasm font.

 

[MajP]

(I don't think you want the same behavior...?)

Yes definitely do not want the same behavior and to get what you are looking for I think the code has to be more advanced. As i wrote it, that will make the the subforms flicker annoyingly since that event would continuously fire. I think you would want to catch the first mousemove and the exit mouse move. I would have to play with that.

 

[MajP]

I looked at the mouse move assuming you want menues to pop up when over the buttons and disappear when not, and this gets very complicated. The issue for a button is that the mousemove event occurs on every pixel change, and there is no event for when you leave hovering over the button. You can kind of use the sections mouseover for that purpose. So this gets complicated. You want to only trap the first mouse move so you have to set a variable for that so that you do not keep flickering, then reset when leaving. I would stick only with the click events.

 

[sedenter]

Thanks for your answers!
Is there a way for access to detect a mouse click on any button and pass the caption in a variable? - without me having to check every button click event.

Adrian

 

[MajP]

Yes. You have to build a function, it cannot be a sub.

public Function BtnWasClicked ()
  'call another procedure and pass the caption or you could do your code here
   Call SomeProcedure (me.activecontrol.caption)
end function

Public sub SomeProcedure (BtnCaption as string)
  do someting with the caption
end sub

Now select all the buttons in design view. In the event procedure type in the name of the function like

= BtnWasClicked()

Now every button will react to that function

 

[sedenter]

Sorry but i don't know how to post codes

please read below our solution:
(i wanted to post this and I saw you replied meanwhile)


INSERTED INTO MODULE
Option Explicit

Public ini As String
Public strSfmNm As String

Public Sub init()
ini = "Initialize"
End Sub

Public Sub strSfmName()
strSfmNm = Screen.ActiveControl.Caption & "sfm"
End Sub


Public Sub bt_Click()
Call init
Call strSfmName
If Forms(ini).Form(strSfmNm).Visible = False Then
Forms(ini).Form(strSfmNm).Visible = True
Else
Forms(ini).Form(strSfmNm).Visible = False
End If
End Sub


INSERTED INTO "Initialize" FORM
Private Sub filebt_Click()
Call bt_Click
End Sub

THANKS A LOT FOR ALL YOUR ASSISTANCE!
We gave you stars for all your answers

Eugen & Adrian

 

[Andrzejek]

i don't know how to post codes

Paste you code in the post/reply window
Highlight your code
Click on 'code' icon (5th left from Preview button)

Or you can do it 'by-hand'. type:[pre][blue]
[ignore]

[/ignore][/blue]
If something Then
   Do this
Else
   Do something else
End If[blue]
[ignore]

[/ignore][/blue][/pre]

and you get:

If something Then
   Do this
Else
   Do something else
End If

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Sorry, but this makes no sense and I cannot imagine it does anything. What is this supposed to do? You have two procedures that are meaningless; init and strSfmName. The ini variable and the strSfmNm do nothing. The entire code would be equivalent to the following

Public Sub bt_Click()
   forms(“initialize”).form.visible = not forms(“initialize”).form.visible
end sub

All your code does is show or hide whatever is in the subform ini
But even more confusing is this line of code
Forms(ini).Form(strSfmNm).Visible
That should through an object does not support property or method error. That is saying something like
Forms("form1").form("form2).visible. If you are talking about the suborm inside the subform control it would be
Forms("formName").controls("subformcontrolName").form.visible
or if you are talking about the subform control then simply
Forms("formName").controls("subformcontrolName").visible

 

[sedenter]

Dear MajP,

There is a high chance for me to have posted this not quite complete, thus the reason it might not have any sense.

I'm trying to explain:
- indeed I needed to use a variable to show and hide some subforms and I just didn't know how to do this.
- "ini" is the string used to replace the name of the main form called "Initialize"
- "strSfmNm" is the variable used to find the subform coresponding to the button I'm clicking
- it gives me no error

So, I think is better to send you the entire project as it is in this very moment, just in case you are curios how it's working (because it is working). And if you'll check it's code you'll find the logic of it.

Thanks anyway Majp for all your help and I hope you'll find the time to take a look on our work.
Personally I'm a bit proud as it seems to be developing quite interesting.

Thanks Andy,

it seems that I managed to understand how to post codes, here's an example:

Public Sub bt_Click()
Call init
Call strSfmName
If Forms(ini).Form(strSfmNm).Visible = False Then
Forms(ini).Form(strSfmNm).Visible = True
Else
Forms(ini).Form(strSfmNm).Visible = False
End If
End Sub

Wish all of you the best!

Eugen

 

[Andrzejek]

Better. Don't forget to format (indent) your code:

Public Sub bt_Click()

Call init
Call strSfmName

If Forms(ini).Form(strSfmNm).Visible = False Then
    Forms(ini).Form(strSfmNm).Visible = True
Else
    Forms(ini).Form(strSfmNm).Visible = False
End If

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

You were asking about making a single procedure that could generalize the code.

seems very tedious to write the same procedure for every click or MouseDown

What you show does not really do anything, especially not that. Your buttons need do a single task. If the matching subform is visible it hides it or if visible it shows it and hides all other ones. This is a perfect example to make a single procedure to do this. Tell the procedure to hide all subforms except show the one related to that button.

To do this and make it easy I add all of my subform controls into a module level collection.

Public MySubForms As New Collection

'---subforms position and dimentions---
Private Sub Form_Load()
    DoCmd.Maximize
    'Add to collection
    MySubForms.Add Me.purpoisesfm
    MySubForms.Add Me.newsfm
    MySubForms.Add Me.filesfm
    MySubForms.Add Me.openvsfm
    MySubForms.Add Me.printsfm
    MySubForms.Add Me.editsfm
    MySubForms.Add Me.optionsfm

    'Hide all subforms
    Call HideSubForms
End Sub

Then I need a procedure that I can call to hide all subforms except the one I specify

'General procedure to hide all subforms except the one specified
Public Sub HideSubForms(Optional DontHideSFM As String = "All")
  Dim sfm As Access.SubForm
  For Each sfm In MySubForms
       If sfm.Name = DontHideSFM Then
         'If already open toggle
         sfm.Visible = Not sfm.Visible
       Else
         sfm.Visible = False
       End If
  Next sfm
End Sub

Now there is one little glitch. The mouseover logic is a little different, to keep it from overwriting the click event. So I make a seperate but similar procedure for the MouseOver.

'General procedure to hide all subforms except the one specified
Public Sub HideSubFormsMouse(Optional DontHideSFM As String = "All")
  Dim sfm As Access.SubForm
  'only do something if other sub forms are visible
  For Each sfm In MySubForms
       If (sfm.Name <> DontHideSFM) And sfm.Visible Then
        sfm.Visible = False
        Me.Controls(DontHideSFM).Visible = True
      End If
  Next sfm
End Sub

I could have made this into a single procedure, but would be hard to follow.

My entire code is

Option Explicit
'Buil a collection to store references to your sub forms
Public MySubForms As New Collection
'---subforms position and dimentions---
Private Sub Form_Load()
    DoCmd.Maximize
    'Add to collection
    MySubForms.Add Me.purpoisesfm
    MySubForms.Add Me.newsfm
    MySubForms.Add Me.filesfm
    MySubForms.Add Me.openvsfm
    MySubForms.Add Me.printsfm
    MySubForms.Add Me.editsfm
    MySubForms.Add Me.optionsfm
    'Move subforms
    purpoisesfm.Move Left:=1380, Top:=315.25, Width:=1400, Height:=945.75
    newsfm.Move Left:=1380, Top:=615.25, Width:=1400, Height:=615.25
    filesfm.Move Left:=0, Top:=315.25, Width:=1400, Height:=2837.25
    openvsfm.Move Left:=0, Top:=315.25, Width:=11650, Height:=3650
    newvsfm.Move Left:=0, Top:=315.25, Width:=11650, Height:=3650
    printsfm.Move Left:=1380, Top:=1891.5, Width:=1500, Height:=1261
    editsfm.Move Left:=540, Top:=315.25, Width:=1300, Height:=1576.25
    optionsfm.Move Left:=945.75, Top:=315.25, Width:=1733.87, Height:=945.75
    'Hid all subforms
    Call HideSubForms
End Sub
'-click action-
Private Sub Filebt_Click()
  Call HideSubForms(Me.filesfm.Name)
End Sub
Private Sub Optionsbt_Click()
  Call HideSubForms(Me.optionsfm.Name)
End Sub
Private Sub Editbt_Click()
  Call HideSubForms(Me.editsfm.Name)
End Sub
'-hover action-
Private Sub Editbt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Call HideSubFormsMouse(Me.editsfm.Name)
End Sub
Private Sub Filebt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
   Call HideSubFormsMouse(Me.filesfm.Name)
End Sub
Private Sub Optionsbt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
   Call HideSubFormsMouse(Me.optionsfm.Name)
End Sub
'Mousedown outside
Private Sub Detail_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Button = acLeftButton Then
        Me.Filebt.SetFocus
       Call HideSubForms
    End If
End Sub
'General procedure to hide all subforms except the one specified
Public Sub HideSubForms(Optional DontHideSFM As String = "All")
  Dim sfm As Access.SubForm
  For Each sfm In MySubForms
       If sfm.Name = DontHideSFM Then
         'If already open toggle
         sfm.Visible = Not sfm.Visible
       Else
         sfm.Visible = False
       End If
  Next sfm
End Sub
'General procedure to hide all subforms except the one specified
Public Sub HideSubFormsMouse(Optional DontHideSFM As String = "All")
  Dim sfm As Access.SubForm
  'only do something if other sub forms are visible
  For Each sfm In MySubForms
       If (sfm.Name <> DontHideSFM) And sfm.Visible Then
        sfm.Visible = False
        Me.Controls(DontHideSFM).Visible = True
      End If
  Next sfm
End Sub
'---"X" button action---
Private Sub ovclosebt_Click()
    DoCmd.Close
End Sub

seems very tedious to write the same procedure for every click or MouseDown

for me to add 1 or hundred more buttons my code changes only by 2 lines for each button.

Private Sub Newbt_Click()
  Call HideSubForms(Me.Newsfm.Name)
End Sub
Private Sub Newbt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Call HideSubFormsMouse(Me.Newsfm.Name)
End Sub

compared to this

'========================
'==="Initialize" meniu===
'========================
'---click outside action---
Private Sub Detail_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Button = acLeftButton Then
        Form_Initialize.Filebt.SetFocus
        '-close "file" menu-
        Form_filefm.Visible = False
        '-close "new" menu-
        Form_newfm.Visible = False
        '-close "print" menu-
        Form_printfm.Visible = False
        '-close "purpoise" menu-
        Form_purpoisefm.Visible = False
        '-close "edit" menu-
        Form_editfm.Visible = False
        '-close "option" menu-
        Form_optionsfm.Visible = False
        '-"FormName" textbox clear-
        fmnmbx.Value = ""
    End If
End Sub


'---subforms position and dimentions---
Private Sub Form_Load()
    DoCmd.Maximize
    
    purpoisesfm.Visible = False
        purpoisesfm.Move Left:=1380, Top:=315.25, Width:=1400, Height:=945.75
    newsfm.Visible = False
        newsfm.Move Left:=1380, Top:=615.25, Width:=1400, Height:=615.25
    filesfm.Visible = False
        filesfm.Move Left:=0, Top:=315.25, Width:=1400, Height:=2837.25
    openvsfm.Visible = False
        openvsfm.Move Left:=0, Top:=315.25, Width:=11650, Height:=3650
    newvsfm.Visible = False
        newvsfm.Move Left:=0, Top:=315.25, Width:=11650, Height:=3650
    printsfm.Visible = False
        printsfm.Move Left:=1380, Top:=1891.5, Width:=1500, Height:=1261
    editsfm.Visible = False
        editsfm.Move Left:=540, Top:=315.25, Width:=1300, Height:=1576.25
    optionsfm.Visible = False
        optionsfm.Move Left:=945.75, Top:=315.25, Width:=1733.87, Height:=945.75
End Sub


'---"File" button action---
'-click action-



Private Sub filebt_Click()
  btnmbx = "File"
    If Form_filefm.Visible = False Then
        Form_filefm.Visible = True
        Form_Initialize.Filebt.SetFocus
        Form_editfm.Visible = False
        Form_purpoisefm.Visible = False
        Else: Form_filefm.Visible = False
    End If
    Debug.Print "file btn"
 End Sub
'-hover action-
Private Sub Filebt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    btnmbx = "File"
        Form_Initialize.Filebt.SetFocus
    If Form_editfm.Visible = True Or (Form_optionsfm.Visible = True) Then
        Form_editfm.Visible = False
        Form_filefm.Visible = True
        Form_editfm.Visible = False
        Form_editfm.Visible = False
        Form_purpoisefm.Visible = False
        fmnmbx.Value = "File"
    End If
    Debug.Print " file Mouse"
End Sub



'---"Edit" button action---
'-click action-
Private Sub editbt_Click()
    btnmbx = "Edit"
    If Form_editfm.Visible = False Then
        Form_editfm.Visible = True
        Form_Initialize.Editbt.SetFocus
        Form_optionsfm.Visible = False
        Form_filefm.Visible = False
        Form_newfm.Visible = False
        Form_printfm.Visible = False
        Form_purpoisefm.Visible = False
        Else: Form_editfm.Visible = False
    End If
    
    If Form_editfm.Visible = True Then
        fmnmbx.Value = "Edit"
        Else: fmnmbx.Value = ""
    End If
End Sub
'-hover action
Private Sub Editbt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    btnmbx = "Edit"
        Form_Initialize.Editbt.SetFocus
    If (Form_filefm.Visible = True) Or (Form_optionsfm.Visible = True) Then
        Form_filefm.Visible = False
        Form_editfm.Visible = True
        Form_optionsfm.Visible = False
        Form_newfm.Visible = False
        Form_printfm.Visible = False
        Form_purpoisefm.Visible = False
        fmnmbx.Value = "Edit"
    End If
End Sub

'---"Options" button action---
'-click action-
Private Sub optionsbt_Click()
    btnmbx = "Options"
    If Form_optionsfm.Visible = False Then
        Form_optionsfm.Visible = True
        Form_Initialize.Optionsbt.SetFocus
        Form_editfm.Visible = False
        Form_filefm.Visible = False
        Form_newfm.Visible = False
        Form_printfm.Visible = False
        Form_purpoisefm.Visible = False
        Else: Form_optionsfm.Visible = False
    End If
    
    If Form_optionsfm.Visible = True Then
        fmnmbx.Value = "Options"
        Else: fmnmbx.Value = ""
    End If
End Sub
'-hover action
Private Sub optionsbt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    btnmbx = "Options"
        Form_Initialize.Optionsbt.SetFocus
    If (Form_editfm.Visible = True) Or (Form_filefm.Visible = True) Then
        Form_editfm.Visible = False
        Form_filefm.Visible = False
        Form_optionsfm.Visible = True
        Form_filefm.Visible = False
        Form_newfm.Visible = False
        Form_printfm.Visible = False
        Form_purpoisefm.Visible = False
        fmnmbx.Value = "Options"
    End If
End Sub

'---"X" button action---
Private Sub ovclosebt_Click()
    DoCmd.Close
End Sub

Every procedure above basically has to change when you add a new button.

 

[sedenter]

Thank you MajP!

As soon as I'll have the time (I'm at work now and today is a very busy day ), I'm going to try your solution and let you know.

 

[MajP]

I forgot to mention when you add a new button and subform you also have to add the subform control to the collection
Public MySubForms As New Collection

'---subforms position and dimentions---
Private Sub Form_Load()
    DoCmd.Maximize
    'Add to collection
    MySubForms.Add Me.purpoisesfm
    MySubForms.Add Me.newsfm
    MySubForms.Add Me.filesfm
    MySubForms.Add Me.openvsfm
    MySubForms.Add Me.printsfm
    MySubForms.Add Me.editsfm
    MySubForms.Add Me.optionsfm
    [COLOR=#3465A4][b]mySubforms.add Me.HelpSfm[/b][/color]
    'Hide all subforms
    Call HideSubForms
End Sub

So I exaggerated it takes three lines of code not two to add a new button/menu.

 

[sedenter]

Thank you MajP!

For the moment it works. I'll see if I encounter some exceptions which I must rezolve, as now I only have a slightly vague idea of how this program will work and look.

I will bother you later to ask for some suggestions for sure .

So... Have a nice day and week end ahead!

Eugen

 

[MajP]

Are you going to repeat this menu structure on other forms. There are a couple of ways to do that so that you do not have to repeat the code. The best way would be to place all of these buttons and menu forms onto a subform. Then that subform could be placed onto any main form. If different forms use the same menu structure but do different things you would have the main form pass information to the menu subform when the main form opens.

 

[sedenter]

Thanks a lot,

As this is practically a new domain for me and I'm not the autodidact kind, it'll be a bit difficult to understand everything you say (as you found it hard to understand what I wanted to do from my first explanations - the kind of a dialog between an expert and a dummy), but I'm guessing that we did until now exactly as you described it in your last post. And we intend to continue the same way in order to optimize the code.