Reference a field in a query using VBA- 3 days later... please, please help me!

[ma t]

Hello, I have tried to figure this out and I am just not getting it...:banghead:
I am trying to create an "onclick" event for a control in a "navigation menu" style subform that will change the source object in another subform control on the same main form. (similar to the new "navigation form" that Access provides)
I have a table TabNavigation which has fields "TabNumber" (number) "ButtonNumber" (number) "ButtonLabel" (text) and "FormName" (text). This goes to a query named "Nav_Tab1Q" which selects records that "TabNumber" = 1. This query is the record source for a subform named "Tab1SF" with the control "ButtonLabel".
Basically what I would like to happen is this, when the user clicks a row in the Tab1SF subform, say the button label of "person information", which in my table = TabNumber 1 and ButtonNumber 1, the form named = to the value in the FormName field will load into the subform control named SF2Cont. Example of form name would be "Nav_PersonInfoSF"

This is my attempt, I am a VBA novice or less than a novice....

Private Sub Tab1ButtonLabel_Click()
If [ButtonNumber] = Queries.Nav_Tab1Q!ButtonNumber Then
Forms!NavigationForm!SF2Cont.SourceObject = Nav_Tab1Q!FormName

End If



End Sub

 

[JHB]

Hmm - I do not know the new navigation form, so ...
But why are you not naming the form like the button label?

Private Sub Tab1ButtonLabel_Click()
Forms!NavigationForm!SF2Cont.SourceObject = Me.Tab1ButtonLabel.Caption
End Sub

 

[ma t]

Thank you JHB, but this doesn't seem to work. The labels have spaces between the words, i.e., "Person Info" and my forms do not, i.e., "Nav_PersonSF". I am not sure if that is the reason, but the code you gave me produces errors. Any other ideas?

 

[ma t]

Quick update- this did work after I removed the ".caption" from the end of the line of code. I will play with it a bit to see if I can still get it to work with my form name field instead of the caption but this is a great start. Thank you!!

 

[ma t]

Thank you Thank you! I got it to work beautifully. I changed the name of the field in my record source that contains my form names to "Tab1FormName" and then changed the code to this:

Private Sub Tab1ButtonLabel_Click()
Forms!NavigationForm!SF2Cont.SourceObject = Me.Tab1FormName
End Sub

Next time I will ask for help sooner... could have saved 4 days of work... but I usually try to do my "due diligence" first.

 

[ma t]

Hello- I am hoping someone out there can help me with another issue related to the first... I have all of the subforms loading properly based on the above code, however, when I try to enter data into any of the subforms my cursor starts flashing from an arrow to an hourglass, the subform seems to update with every key stroke, (what I am typing is replaced by the next key stroke) and I get the error "Object Invalid or no longer Set". Can anyone help me solve this?