Solved The expression after update you entered as the event property setting produced the following error:

[SaranPriya-7954]

The following is my code and I am having problems in making my Menu expand from 9 buttons items to 12.

I am getting a "The expression after update you entered as the event property setting produced the following error:"

It's a issues that arises when I click on my menu buttons. To whom ever will assist thank you for your time.

Option Compare Database


Dim db As Database
Dim rs As Recordset

Private Function SetBt()

Dim Ax As Single
Ax = CSng(Right(Me.ActiveControl.Name, 2))

'If CurParam(1100 + Ax, True) = False Then Exit Function

'MsgBox Ax
'Dim i As Integer

For i = 1 To 12

Me("B" & i).BackColor = Box18.BackColor


Next
Me.ActiveControl.BackColor = Box1.BackColor
Me("P" & Ax).SetFocus

'lb0.Caption = Me("B" & Ax).Caption
'Show SubTab
Dim j As Integer

Dim PageNameDigitLen As Integer 'New line of code
PageNameDigitLen = Len("P" & Ax) 'New line of code
For j = 0 To SubTab.Pages.Count - 1
If Left(SubTab.Pages(j).Name, 2) = Me("P" & Ax).Name Then
SubTab.Pages(j).Visible = True
Else

SubTab.Pages(j).Visible = False

End If

Next j

End Function




End Function

 

[arnelgp]

you can debug your code?
add breakpoint to the function (press F9 inside the function).
run your form and click your button.
when vba put you in the code press F8 until
you find which line has error.

 

[SaranPriya-7954]

you can debug your code?
add breakpoint to the function (press F9 inside the function).
run your form and click your button.
when vba put you in the code press F8 until
you find which line has error.

I understand what you are asking for, however, when I attempt to do that as I go past my first breakpoint, it kicks me out of the function and displays the same error message.

The following is the line I am referring to:

Ax = CSng(Right(Me.ActiveControl.Name, 2))

 

[The_Doc_Man]

First, if you look up "Active Control" you get a page like this one:

Form.ActiveControl property (Access)

Office VBA reference topic

docs.microsoft.com

IF you read that, you would see that despite the claim that it works for a Form object, in fact their example refers to the SCREEN.ActiveControl - so my first advice is change Me.ActiveControl to Screen.ActiveControl and see if that works.

Second, I am not sure why you are trying to find the rightmost two characters of the screen name and then take a "Convert to Single" of it. But there are SO many potential pitfalls here that I can't begin to explain them all. But a SINGLE is almost CERTAINLY not the data type you want. Either LONG or INTEGER? Maybe (CLng or CInt). But SINGLE? Then there is the matter that I hope you have no controls with either a single digit at the end or three digits.

This LOOKS like you are trying to dynamically identify a control and do some computation to .SETFOCUS to that control (perhaps?) But if you got it as the active control it already HAS focus. So that is essentially a null operation. Since we can't see your form, we are missing half of the structure, and since you haven't told us - in common language - what is your end goal, we cannot correlate what you did with what you wanted. We don't KNOW what you wanted.

I'm not trying to bust your chops here, but we are working with very little understanding of your goal. So our answers will have to be limited to what little we can see. You might get more out of this experience by filling in a few of the blanks here.

 

[SaranPriya-7954]

The Screen.ActiveControl generates another error message: Method or data member not found

In order to give more context:

I am developing an application with dynamic submenu's which upon selection brings up various tabs with additional controls.

My dynamic menu works correctly when I set my "For statement from 1 to 9 and the value, Ax = CSng(Right(Me.ActiveControl.Name, 1)) however, when I increase the 1 to 2 in order to read both characters, I receive an error message.

I set the number as single as the data will never exceed an exorbitant number as I will only have a maximum of 15 menu items.

"This LOOKS like you are trying to dynamically identify a control and do some computation to .SETFOCUS to that control (perhaps?)"

Answer: You are correct

The form layout is as such:

A dynamic menu of buttons custom made on the left hand side, which activates various tabs when clicked upon which displays various objects.

If you require more info just let me know...much appreciated.

 

[SaranPriya-7954]

Thank you for your assistance:

I figured it out, another way to skin the cat:

The following code worked:

Dim Ax As Single
On Error Resume Next
Ax = CSng(Right(Me.ActiveControl.Name, 2))
If Err Then
Ax = CSng(Right(Me.ActiveControl.Name, 1))
End If
On Error GoTo 0

 

[The_Doc_Man]

Yep... after that fact, given your explanation of what worked, taking the RIGHT(x,2) function of a control - if that control had only a single trailing digit like CONTROLBUTTON9 as a name - would lead you to taking the CSng() of a letter and number combination - N9 in my example. Since the letter would have come first, CSng() would balk. While I might not have done it the way you did, it is perfectly legal to do that and it should be stable - until you reach 3-digit numbers.

 

[SaranPriya-7954]

Yep... after that fact, given your explanation of what worked, taking the RIGHT(x,2) function of a control - if that control had only a single trailing digit like CONTROLBUTTON9 as a name - would lead you to taking the CSng() of a letter and number combination - N9 in my example. Since the letter would have come first, CSng() would balk. While I might not have done it the way you did, it is perfectly legal to do that and it should be stable - until you reach 3-digit numbers.

If you ever have time, I would love to know how you would have done it

 

[The_Doc_Man]

I'm still not clear on the intent of the substitution. It seems like a rather torturous method to do something based on something else - by forcing name-part matches. Are you using TAG properties for anything else in your forms? Because you can tag "stuff" in the .TAG property if you want. OR you can build tables that say "If you click X you open A, B, and C" - but I don't see from your description what it is that you are REALLY trying to accomplish so I can't answer your question properly. This is the ONLY statement that gives me any information on purpose:

"I am developing an application with dynamic submenu's which upon selection brings up various tabs with additional controls."

IF you have a main form with tab controls, you can name the tab controls and when you click them, ALL of the controls on the tab will come up at once with a single click - on the tab itself. You can also create a button that could have VBA code to activate the tab control and let that one tab control "drag" everything into view. That is what they are for.

However, I absolutely would never use CSng for this kind of back-door selection. CInt, maybe. CLng, maybe. CSng (or CDbl), never. While it might behave itself, you are using a scientific number for what SHOULD be a counting number. The fact that it seems to work (at the moment) is good. Pragmatically, your code will work. But I'm just enough - barely enough - of a purist to be bothered by use of what appears to be the wrong data type. Don't take it as a slam. Count it as a mild disagreement on stylistic preferences.

 

[SaranPriya-7954]

I'm still not clear on the intent of the substitution. It seems like a rather torturous method to do something based on something else - by forcing name-part matches. Are you using TAG properties for anything else in your forms? Because you can tag "stuff" in the .TAG property if you want. OR you can build tables that say "If you click X you open A, B, and C" - but I don't see from your description what it is that you are REALLY trying to accomplish so I can't answer your question properly. This is the ONLY statement that gives me any information on purpose:

"I am developing an application with dynamic submenu's which upon selection brings up various tabs with additional controls."

IF you have a main form with tab controls, you can name the tab controls and when you click them, ALL of the controls on the tab will come up at once with a single click - on the tab itself. You can also create a button that could have VBA code to activate the tab control and let that one tab control "drag" everything into view. That is what they are for.

However, I absolutely would never use CSng for this kind of back-door selection. CInt, maybe. CLng, maybe. CSng (or CDbl), never. While it might behave itself, you are using a scientific number for what SHOULD be a counting number. The fact that it seems to work (at the moment) is good. Pragmatically, your code will work. But I'm just enough - barely enough - of a purist to be bothered by use of what appears to be the wrong data type. Don't take it as a slam. Count it as a mild disagreement on stylistic preferences.

lol..I am not offended...