Referring to a button on a subform from a seperate form

[stillnew2vb]

Greetings Gurus,

Could someone please tell me how I can refer to a button on a subform from another form?

Basically I have a mainform "form1" which has 3 subforms "form2","form3" and "form4". Each of those subforms have a button on them which call another form "form5". I need form5 to "recognise" which button was clicked so that it can perfrom a function based on which button called it

I have found plenty of ways to refer to a control but none referring to buttons. Can anyone help?

 

[boblarson]

Just use OpenArgs to do it.

In the event which opens the form, add the name of the form to the openargs:

Docmd.OpenForm "FormNameHere", acNormal, OpenArgs:= Me.Name

Then you can refer to Me.OpenArgs in the form 5 that was opened.

 

[stillnew2vb]

Hi Bob,

That works if I am referring to form2 itself of a record on it. I am trying to refer to the name of a button (if that's possible). I get a run-time error 438 "object doesn't support this property or method" and/or run-time error 2498 "wrong data type". I think this is because the button doesn't have a value.

My code

Private Sub Notify_Click()
    
Dim myName As String
myName = Me.Name
MsgBox "" & myName 'just testing I am on the right form

DoCmd.OpenForm "Notifier", acNormal, , , , acWindowNormal, OpenArgs:=Me.Notify ' Notify is the button's name

Any ideas or I am forced to used the form name (which will make things difficult as forms 3 & 4 both have 2 buttons.

 

[John Big Booty]

Your problem is that you are referring to the button, additionally I believe that Me.Name will return the form name rather than the button name. I suspect Bob's intention was that you pass the calling form's name to the form being opened rather than the button name, if you wish to pass the button name your code should probably look like;

Private Sub Notify_Click()
    
Dim myName As String
myName = Me.Name
MsgBox "" & myName 'just testing I am on the right form

DoCmd.OpenForm "Notifier", acNormal, , , , acWindowNormal, OpenArgs:=Me.Notify.[COLOR="Red"]Caption[/COLOR] ' Notify is the button's name

Note: the above will not work with a button that has an image rather than a caption.

Better still given that this code is specific to a particular button simpl use;

DoCmd.OpenForm "Notifier", acNormal, , , , acWindowNormal, OpenArgs:= "Notify"

 

[stillnew2vb]

Hi John

thanks for that it sort-of works except it only works for button 1 on form 2. If I click button 1 on form 3 I get the result of button 1 on form 2. Is there something wrong with my code?

Form 5 gets called like this

Private Sub PONotify_Click()
DoCmd.OpenForm "Notifier", acNormal, , , , acWindowNormal, OpenArgs:="PONotify"
End Sub

Private Sub PRNotify_Click()
DoCmd.OpenForm "Notifier", acNormal, , , , acWindowNormal, OpenArgs:="PRNotify"
End Sub

Private Sub QRNotify_Click()
DoCmd.OpenForm "Notifier", acNormal, , , , acWindowNormal, OpenArgs:="QRNotify"
End Sub

Each of the xxNotify are the buttons. Each of them launches form 5 quite happily.
Form5 then has a button which when clicked does this

Dim strProjectCode, strCompany, OpenArgs, strStatement, strChange As String

strProjectCode = [Forms]![OrdersEdit]!List4.Column(1)
strCompany = DLookup("CompanyName", "dbo_Company", "ID =" & Forms!OrdersEdit!Text10)

Select Case OpenArgs
Case PRNotify
     strStatement = "requires a sale price. Please see ..." ' name of another form
Case QRNotify
     strStatement = "requires a quote. Please see ..." ' name of another form
Case PONotify
     strStatement = "has accepted a quote. Please see ..." ' name of another form
End Select

strChange = strCompany & " " & strProjectCode & " " & strStatement
           
If Me.Pricing = -1 Then
DoCmd.SendObject acSendNoObject, , , "xx@xxx", , , strChange, , False ' relative dept. gets an advisory email depending on what action needs to be taken.

Is there something wrong with this?

Thanks for the help.

 

[John Big Booty]

You don't need to declare OpenArgs as a variable. I'm not sure if that is effecting the outcome of your code.

 

[stillnew2vb]

Hi again John,

I originally thought so too but if I omit it then the strStatement part of strChange is left out. Maybe it shouldn't be declared as a string?

 

[John Big Booty]

I'm just wondering if your code should look like this;

Dim strProjectCode, strCompany, OpenArgs, strStatement, strChange As String

strProjectCode = [Forms]![OrdersEdit]!List4.Column(1)
strCompany = DLookup("CompanyName", "dbo_Company", "ID =" & Forms!OrdersEdit!Text10)

Select Case OpenArgs
Case PRNotify
     strStatement = "requires a sale price. Please see ..." ' name of another form
Case QRNotify
     strStatement = "requires a quote. Please see ..." ' name of another form
Case PONotify
     strStatement = "has accepted a quote. Please see ..." ' name of another form
End Select

strChange = strCompany & " " & strProjectCode & " " & strStatement [B][COLOR="Red"]& " " & OpenArgs[/COLOR][/B]
           
If Me.Pricing = -1 Then
DoCmd.SendObject acSendNoObject, , , "xx@xxx", , , strChange, , False ' relative dept. gets an advisory email depending on what action needs to be taken.

 

[stillnew2vb]

Nope, that doesn't work either.

Interesting that the msgbox is blank when I do this.

DoCmd.OpenForm "Notifier", acNormal, , , , acWindowNormal, OpenArgs:="PONotify"
MsgBox "" & OpenArgs

. Should I declare "PONotify" statically?

 

[John Big Booty]

OpenArgs is part of the DoCmd.OpenForm command, so you will need to test it in the On Load event of the form that is being opened by your DoCmd.OpenForm command.

 

[stillnew2vb]

Now I'm very confused...

Dim strProjectCode, strCompany, strStatement, strChange As String
MsgBox "" & OpenArgs ' this returns PENotify which is correct
strProjectCode = [Forms]![OrdersEdit]!List4.Column(1)
strCompany = DLookup("CompanyName", "dbo_Company", "ID =" & Forms!OrdersEdit!Text10)
MsgBox "" & OpenArgs ' this returns PENotify which is correct

Select Case OpenArgs
Case PENotify
     MsgBox "" & OpenArgs ' this returns nothing
     strStatement = "has been edited. Please see Purchasing > Add/Edit Orders"

Is there such a thing as

Select Case OpenArgs as String

?

Any ideas?

 

[John Big Booty]

Try passing the OpenArgs to a string, Something along the lines of;

Dim strProjectCode, strCompany, strStatement, strChange, [COLOR="Red"]strPassedInfo[/COLOR] As String

strPassedInfo = OpenArgs

MsgBox "" & OpenArgs ' this returns PENotify which is correct
strProjectCode = [Forms]![OrdersEdit]!List4.Column(1)
strCompany = DLookup("CompanyName", "dbo_Company", "ID =" & Forms!OrdersEdit!Text10)
MsgBox "" & OpenArgs ' this returns PENotify which is correct

Select Case strPassedInfo 
Case PENotify
     MsgBox "" & OpenArgs ' this returns nothing
     strStatement = "has been edited. Please see Purchasing > Add/Edit Orders"

 

[stillnew2vb]

Finally got it working, I was think along the same lines John...

Dim strProjectCode, strCompany, strOpenArgs, strQuote, strChange As String

strOpenArgs = OpenArgs

Select Case strOpenArgs
Case "PENotify"
     strQuote = "has been edited. Please see Purchasing > Add/Edit Orders"
Case "PRNotify"
     strQuote = "requires a sale price. Please see Purchasing > Add/Edit Orders"
Case "QRNotify"
     strQuote = "requires a quote. Please see Purchasing > Add/Edit Orders"
Case "PONotify"
     strQuote = "has accepted a quote. Please see Purchasing > Add/Edit Orders"
Case "IRNotify"
     strQuote = "is ready to be invoiced. Please see Purchasing > Add/Edit Orders"
End Select

The problem now is how to "reset" the event because, if I click 2 different buttons in a row (after allowing Outlook's warnings) the same strQuote value comes up...

 

[stillnew2vb]

OK, that problem solved through a couple of requeries, not to work out why there is an equivalent of a tab space between strProjectCode & strQuote (it comes out as ProjectCode Quote)

 

[stillnew2vb]

Seems the record in the source table that strProjectCode comes from has got some "padding" after it, probably because the SQL table it is linked to has a field size of 40 chars.

There is probably a way to get Access to ignore the extra spaces but I'll leave that for another day.

 

[stillnew2vb]

Doh! - RTrim

 

[John Big Booty]

Glad you got it working