determine which commandbarbutton is pressed

[gloudo]

I have a bit of code I can't get to work.

I have 2 buttons on a custom menu(KRS2005)
Button1 "Archive" and Button2 "Inspection".

I found this piece of code on the net but I can't get it to work.

Private Sub Form_Open(Cancel As Integer)
Dim cmdBCtl As CommandBarControl
Set cmdBCtl = Application.commandbars.ActionControl

    ' Compare the tag to determine which button was clicked.
    With cmdBCtl
    Select Case .Tag
    Case "Archive"
            MsgBox "Archief"

    Case "Inspection"
            MsgBox "Keuringen"
    End Select
    End With
End Sub

End Sub

what am I doing wrong?

 

[HarleyQuinn]

What part of it doesn't work? Does it error etc.?

Out of interest, on the Form_Open event, would any of the buttons be clicked?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gloudo]

It gives: Runtime error 91, Object variable not set or with block variable not set.

What I want is: When pressing the button "Archive" or "Inspection" in Menu (KRS2005) a popup form (Frm_PW) opens where the user must submit a password before continueing to the form "Archive" or "Inspections".

 

[HarleyQuinn]

OK, what I think you will need to do is put the code you have in a sub named WhatButton().

When you create the commandbarbuttons you will need to set the .OnAction property to the name of your sub (without the parenthesis) e.g.

With buttonTwo
    .Tag = "Archive"
    .OnAction = "WhatButton"
End With

Then the sub will be called each time a button is pressed.

The built in help is pretty good for this, if you put your cursor on the .ActionControl property in your code and press F1 it will give a more detailed version of the example I've given you.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gloudo]

I changed the code into the following:

Private Sub Cmd_OK_Click()
Dim Msg, Style, Title, Response, MyString
Msg = "Dit formulier is alleen met een Password te openen!" + Chr(10) + "Wilt u doorgaan?"   ' Definieert bericht.
Style = vbRetryCancel + vbCritical + vbDefaultButton2    ' Definieert knoppen.
Title = "Formulier openen Onderbroken"    ' Definieert titel.
Dim ArchiefBtn, KeuringenBtn As Object
Dim StDocName As String

    Set ArchiefBtn = commandbars("KRS2005").Controls("Onderhoud").Controls("Archief Test")
    With ArchiefBtn
        .Tag = "Archief_Test"
    End With
    
    Set KeuringenBtn = commandbars("KRS2005").Controls("Onderhoud").Controls("Keuringen Test")
    With KeuringenBtn
        .Tag = "Keuringen_Test"
    End With


If Me.PWtxt.Value = DLookup("Connect", "Tbl_Connect") Then
    
    Dim cmdBCtl As CommandBarControl
    Set cmdBCtl = commandbars.ActionControl

    ' Compare the tag to determine which button was clicked.
    With cmdBCtl
    Select Case .Tag
        Case "ArchiefBtn"
                StDocName = "Frm_Tabel_Archief"
                 DoCmd.Close acForm, "Frm_PW", acSaveNo
                 DoCmd.OpenForm StDocName
    
        Case "KeuringenBtn"
                StDocName = "Frm_Tabel_Keuringen"
                 DoCmd.Close acForm, "Frm_PW", acSaveNo
                 DoCmd.OpenForm StDocName
    
        End Select
    End With
    
Else
    ' Geeft bericht weer.
            Response = MsgBox(Msg, Style, Title)
        If Response = vbRetry Then    ' Gebruiker koos Retry.
            Me.PWtxt.Value = ""
            DoCmd.GoToControl "PWtxt"
         
        Else    ' Gebruiker koos Nee.
            DoCmd.Close acForm, "Frm_PW", acSaveNo
            DoCmd.CancelEvent
        End If
End If

End Sub

It still won't work. it hangs on (Select Case .Tag)

 

[HarleyQuinn]

It won't pick up the button's as you've assigned different tags to them than you're searching for.

Set ArchiefBtn = commandbars("KRS2005").Controls("Onderhoud").Controls("Archief Test")
    With ArchiefBtn
        '.Tag = "[red]Archief_Test[/red]" 'your tag
         .Tag = "[red]ArchiefBtn[/red]" 'try with this tag
    End With
    
    Set KeuringenBtn = commandbars("KRS2005").Controls("Onderhoud").Controls("Keuringen Test")
    With KeuringenBtn
        .Tag = "Keuringen_Test"
    End With


If Me.PWtxt.Value = DLookup("Connect", "Tbl_Connect") Then
    
    Dim cmdBCtl As CommandBarControl
    Set cmdBCtl = commandbars.ActionControl

    ' Compare the tag to determine which button was clicked.
    With cmdBCtl
    Select Case .Tag
        Case "[red]ArchiefBtn[/red]"
                StDocName = "Frm_Tabel_Archief"
                 DoCmd.Close acForm, "Frm_PW", acSaveNo
                 DoCmd.OpenForm StDocName
    
        Case "KeuringenBtn"
                StDocName = "Frm_Tabel_Keuringen"
                 DoCmd.Close acForm, "Frm_PW", acSaveNo
                 DoCmd.OpenForm StDocName
    
        End Select
    End With

You'll have to do this for the Inspection button as well.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gloudo]

No, still the same error.

 

[HarleyQuinn]

Did you try the .OnAction method as I pointed you to earlier?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jebry]

Hi!

If you are using the DoCmd.OpenForm you can pass the information using the OpenArgs:

DoCmd.OpenForm "YourForm", , , , , , "YourButtonName"

Then in the Open event of the form use:

ButtonName = Me.OpenArgs

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[gloudo]

Hi HarleyQuinn,

Yes I tried it but before I insert a password the form goes on to "Frm_Tabel_Archief

 

[HarleyQuinn]

Hi gloudo,

Could you show us the code you tried so that we might be able to help fix it for you?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gloudo]

This is what I have on clicking OK after inserting the password:

Private Sub Cmd_OK_Click()
Dim Msg, Style, Title, Response, MyString
Msg = "Dit formulier is alleen met een Password te openen!" + Chr(10) + "Wilt u doorgaan?"   ' Definieert bericht.
Style = vbRetryCancel + vbCritical + vbDefaultButton2    ' Definieert knoppen.
Title = "Formulier openen Onderbroken"    ' Definieert titel.
Dim ArchiefBtn, KeuringenBtn As Object
Dim StDocName As String

    
If Me.PWtxt.Value = DLookup("Connect", "Tbl_Connect") Then
    ' Compare the tag to determine which button was clicked.

      Set ArchiefBtn = commandbars("KRS2005").Controls("Onderhoud").Controls("Archief Test")
    With ArchiefBtn
        .Tag = "ArchiefBtn"
        .OnAction = "WhichButton"
    End With
    
    Set KeuringenBtn = commandbars("KRS2005").Controls("Onderhoud").Controls("Keuringen Test")
    With KeuringenBtn
        .Tag = "KeuringenBtn"
        .OnAction = "WhichButton"
    End With

Else
    ' Geeft bericht weer.
            Response = MsgBox(Msg, Style, Title)
        If Response = vbRetry Then    ' Gebruiker koos Retry.
            Me.PWtxt.Value = ""
            DoCmd.GoToControl "PWtxt"
         
        Else    ' Gebruiker koos Nee.
            DoCmd.Close acForm, "Frm_PW", acSaveNo
            DoCmd.CancelEvent
        End If
End If

End Sub

Sub whichButton()

    Select Case commandbars.ActionControl.Tag
        Case "ArchiefBtn"
            DoCmd.Close acForm, "Frm_PW", acSaveNo
            DoCmd.OpenForm "Frm_Tabel_Archief"
        Case "KeuringenBtn"
            DoCmd.Close acForm, "Frm_PW", acSaveNo
            DoCmd.OpenForm "Frm_Tabel_Keuringen"
        
    End Select
End Sub

but still Runtime error 91, Object variable not set or with block variable not set.