Lock specific records at runtime in a form

[WCStarks]

I am sorry to be so dense. But do you have time to give me an example of how to use the variable in this instance?

[pbaldy]

Quick and dirty, but declare it at the top of the module so it's available anywhere in the form, set it in the load event and:

        If Me.[ID] = 34 And lngID <> 34 Then    'SCATeam Ledger
            Me.CtrLedger.SourceObject = "Team Trans Subform"
            Debug.Print "setting for 34"
        ElseIf lngID = 34 Then    'Member Ledger
            Me.CtrLedger.SourceObject = "Team Mem_Ledgers Subform"
            Debug.Print "setting for all others"
        Else
            'not needed, here for the debug
            Debug.Print "not doing a thing"
        End If
        lngID = Me.ID

[pbaldy]

Second test might need to be

ElseIf Me.[ID] <> 34 And lngID = 34 Then

[WCStarks]

Paul, the code, as written in Post 17 does not work. As modified in post 18, also does not work. In either case only the default SCATeam Ledger displays. However, the code as show below, does work. FYI: Until last week, I only had one Ledger form and it was and still is imbedded in the Ledgers Tab Container. It is now used as the SCATeam Ledger form. I suppose I can un-embed it from the contrainer, now that I am dynamically loading the two forms. Can you confirm that?

        If Me.[ID] = 34 And LngID <> 34 Then 'SCATeam Ledger form
             Me.CtrLedger.SourceObject = "Team Trans Subform"
             Debug.Print "Setting for SCATeam Ledger form"
        Else
            'LngID = 34 Then 'Members Ledger form
             Me.CtrLedger.SourceObject = "Team Mem_Ledgers Subform"
             Debug.Print "Setting for Member Ledger form"
        'Else
            'Not needed.  Here for the Debug
            'Debug.Print "Not doing a thing"
        End If
        LngID = Me.[ID]

Also, here is the Form Load property code placed at the top of the module. I wasn't sure if I should make it private or public, so I made it public. Perhaps you could clarify this point for me.

Public Sub Form_Load()
    Dim LngID As Long
    LngID = Me.[ID]
End Sub

[WCStarks]

Redundant, deleted

[June7]

Shouldn't hurt but there is no need for it to be Public.

But if you want LngID to be available to multiple procedures it must be declared in the module header. So I don't see how your code can be fully functional.

Do you have Option Explicit in the module header? It should be in every module. You can force this as a default in new modules with VBA Editor options setting:

Tools > Options > Editor > require variable declaration

[WCStarks]

Thanks for the clarification. I didn't know if a private variable was visible to the whole module.

About Option Specific - Do I understand correctly that if Option Specific is not set, variables can be created on the fly, without first declaring them and they will still work? I guess that is why, when in the above code, earlier this morning, I misspelled LngID and it did not cause an error. The code didn't work correctly, until I corrected it, but Access didn't seem to care. I had wondered why it hadn't caused an error.

[orange]

PMFJI

Always use Option Explicit ---it will identify spelling errors and more, and save you from yourself and poorly executed good intentions.

Good luck and Happy New Year.

[pbaldy]

Agree and have a little info here:

http://www.baldyweb.com/OptionExplicit.htm

[WCStarks]

Well, I do not see the Tools > Options to select. How do I get there?

[June7]

On the VBA Editor window.

[pbaldy]

And by the way, the code I posted was working in the sample db, with the variable declared at the top of the module, not within a sub. That said, I was just judging by the debug results, I didn't bother trying to find which subform was changing.

[WCStarks]

I would like to take this opportunity to thank you, Paul, June7, and Orange for your help over the last many months. It is greatly appreciated. You have made my project much better than it would have been otherwise. I have learned a lot, yet still, much more to learn. And My I wish you all a Happy New Year.

[pbaldy]

Glad to help and happy new year!

[WCStarks]

That is interesting. The declaration was in the On Load property, at the top of the module. I had it marked Public at the time. When I ran the debug, I saw it kept skipping the Member register. I checked the code over several times, but couldn't get it to work. At least it does seem to work properly with the simpler one "Else".

Oh, I wonder if I had the LngID misspelled at the time. It may have been. I don't remember when I caught it. So that may have been the problem. It is funny how one sometimes just cannot see a misspelling.

I now have the "Option Distinct" set.