Multiple instances of a form

[MarkK]

An Access.Form object exposes a Filter property that you can use like a WHERE clause. In that case the syntax is like ...

Me.Filter = "Fieldname = " & fieldValue
Me.FilterOn = True

I don't use Form.Filter much, so maybe you don't need to explicitly set the FilterOn after setting the text. Not sure.

An sort of unrelated observation I have of code you posted is that it runs a process in a different object using data and controls in that object. Typically you want to make each object responsible for itself, responsible for it's own functionality and it's own data.

Think about this. Put a Public Sub on the parent form, and teach consumers of that parent to call that Sub to change out Subform2. Imagine this code on the parent ...

Public Sub ChangeOutSubform2(sfmName as String)
[COLOR="Green"]'  loads or unloads a form from the SF2Cont subform control
'  and sets or clears the filter on Me as the case may be
[/COLOR]   Me.SF2Cont.SourceObject = sfmName
   If sfmName <> "" then 
       Me.Filter = "FID = " & Me.SF1Cont.FID
       Me.FilterOn = True
   Else
       Me.FilterOn = False
   End If
End Sub

... and now the child form button click consumes the new Public method exposed by the parent ...

Private Sub Tab2ButtonLabel_Click()
   Me.Parent.ChangeOutSubform2 Me.Tab2FormName
End Sub

... or consumers can just unload the subform on the parent ...

Private Sub UnloadButton_Click()
   Me.Parent.ChangeOutSubform2 ""
End Sub

... so if work has to happen in the Parent, teach the parent to do it for itself, then any child can make it happen with one line of code. Simpler. More stable. More scalable. Each object fends for itself.

 

[ma t]

Thank you very much... i will try to digest and implement. VBA is still very mysterious... but I'm trying, and I really want to learn.

 

[MarkK]

Well, you're asking interesting questions, and you seem ambitious. Makes it fun to chip in. Cheers,

 

[ma t]

Thanks, my brother is a db guy but he refuses to do anything ms... so when i ask him questions he politely declines. I am trying to "graduate" from macros and wizzards to vba and im really trying to understand the language. is there a recommended reading you can point folks like me to that explain the basics? I am sure I'm not alone! Again, thank you so much for your help!

 

[ma t]

Hello Lagbolt-
So, trying to implement your code here... the two sfconts that contain the FID's are the header with the client's info "Nav_HeaderSFCont" and the subform that loads into SF2Cont. I tried various modifications of the line with the me.filter = FID and i keep getting the error that the method or data member not found. I changed your code to reflect that the FID is not in the SF1Cont but in the SF2Cont. (SF1Cont contains the subform with the list of navigation "buttons".) Maybe I did something wrong... I also tried adding "me.parent.nav_headersfcont.fid" but that didn't work either. I have also pasted the code for the Tab2ButtonLabel, is there something missing?

Public Sub ChangeOutSubform2(sfmName As String)
'  loads or unloads a form from the SF2Cont subform control
'  and sets or clears the filter on Me as the case may be
   Me.SF2Cont.SourceObject = sfmName
   If sfmName <> "" Then
       Me.Filter = "FID = " & Me.Nav_HeaderSFCont.FID
       Me.FilterOn = True
   Else
       Me.FilterOn = False
   End If
End Sub

Thank you! I feel like I am on the verge of getting this to work... but not without a ton of help!

 

[ma t]

Still experimenting- feel like a blind man bumbling around in the dark...

I have place this code on my "parent" form, "MainNavigationForm". I placed a textbox control in the form header named "TempFID" that = the FID I am trying to filter on.

Public Sub ChangeOutSubform2(sfmName As String)
'  loads or unloads a form from the SF2Cont subform control
'  and sets or clears the filter on Me as the case may be
   Me.SF2Cont.SourceObject = sfmName
   If sfmName <> "" Then
       Me.Filter = "[TempFID] = " & Me.SF2Cont.[FID]
       Me.FilterOn = True
   Else
       Me.FilterOn = False
   End If
End Sub

I have this code pasted into an onclick event of the subform "NavTabForm2" which is loaded into the SF1Cont sfcontrol.

Option Compare Database
Private Sub Tab2ButtonLabel_Click()
   Me.Parent.ChangeOutSubform2 Me.Tab2FormName
End Sub

I'm no longer getting the error I mentioned above, but this one- Run-time error 438, Object doesn't support this property or method. when I click debug, i see this: (the 2nd line has a yellow highlight)

Option Compare Database
Private Sub Tab2ButtonLabel_Click()
   Me.Parent.ChangeOutSubform2 Me.Tab2FormName
End Sub

I'm sure its something simple, all help is greatly appreciated!

 

[ma t]

Here is a picture of my form layout... maybe it will help?

 

[MarkK]

Hey there, that's pretty ambitious what you're trying to do there as a "beginner," but here's the problem I'm having. When you write ...

the two sfconts that contain the FID's are the header with the client's info "Nav_HeaderSFCont" and the subform that loads into SF2Cont.

... I don't really get it. You know what all that means, but making it make sense to me is going take too much work.
What you can do if you want is strip out any private data, and post your database. Compact and repair it first, and .zip it up, and I'll have a look. It's easier to do, and then you can look at what I did.
Cheers,

 

[ma t]

ok will do... it will take me just a bit.

 

[ma t]

Ok- here is the file. In a nutshell, this is what I hope to do.
I have people with a unique id "PID" the people are in families, with an "FID" I have some forms that need to link on PID such as person info, address, etc. I have other forms that need to link on FID such as the example "AgencyFID" as each form is loaded, I would like to be able to "tell" the SF2Cont whether to link PID or FID. I just added a field "buttonclass" to the table named "NavigationTabButtons" where I define the tabs, button names and form names. I am wondering if the buttonclass (right now its either 1 for PID or 2 for FID can be referenced in the code? This is the table that goes into the queries that is the record source for the "left nav" subforms, which the code in the onclick for the SF1Cont refers to. I hope that is clearer than mud.
Thank you again for taking interest.

 

[MarkK]

Took a look at this and I think you've got way too many subforms. And your main form isn't even bound to any data so all your subforms are going to need to talk to each other, not the parent, and it totally violates all the "keep it simple" rules. I think I get that you want to write this super awesome system, but you're one guy pounding out code, and where you're going will provide so much awesome flexibility and sophistication, that you'll never actually get it reliable. I wouldn't even try to make that approach reliable.

A rule of thumb I've heard before, "good software takes five years to write." I don't know what deal you're working under, like if you have a contract to deliver this system, or if you're an employee somewhere, but I would back way up and concentrate on getting your data, tables, and very basic user interface working so you can start recording data, and just trouble shooting your table structure. I wouldn't do fancy navigation stuff until I could assure my users there's some reliable functional thing to navigate to. See what I'm getting at?

Your users have jobs to do. What are those jobs? That's your bread and butter, so first I would make places where users can go and do those jobs, and then worry about navigation, which is just how they get from job to job.

Anyway, that's my 2c. I was hoping I could just tweak it for you and you could go, "oh, yeah, cool, I see," but you've bit off more then I thought, and it doesn't look very chewable. Sorry to say so. Feel free to get other opinions though. This is just my opinion.

Happy new year,

 

[ma t]

Thanks for your input, I truly appreciate it. Actually this is a redesign I am attempting of an app that has been in use for about 2 years. The current system utilizes many tabbed forms with many subforms. We get the "cannot open any more databases" error and I was trying to overcome that by utilizing an interface that would only open 1 form at a time and give the user all the choices in one navigation too. Yes the data collection is complex, I get that. Thanks so much for taking a look, happy New Year!