Linking Subforms

[painterz]

Hello All,

I've attached a screen shot of my forms. I currently have my family subform (upper right hand corner) linked to my main form on AddressID. To show all of the families associated with an address. There are two families.

I'd like to link my people subform (big subform) linked to my family subform on PeopleID. The recordsource for both subforms contain PeopleID but not the main form. The reason for doing this to select which family I want to view.

It seems I can only do a Master/Child link when a subform is within another form. How do I get around this? I thought I could unbind my people subform but the phone and notes subforms are also bound on PeopleID.

Any ideas?

Thanks for pondering my problem :0

 

[pbaldy]

One way would be to change the record source of the big subform in the current event of the family subform. Pretend code:

FormReference.RecordSource = "SELECT Blah FROM TableName WHERE PeopleID = " & Me.PeopleID

 

[painterz]

Very cool! I assume I would also do the same for the Phone and Notes subforms too? I'll give it a try.

 

[pbaldy]

Sure, it should work for anything you want to be driven by the record selected in the family subform.

 

[painterz]

Yep, I've confused myself.

Pretend code: FormReference.RecordSource = "SELECT Blah FROM TableName WHERE PeopleID = " & Me.PeopleID

My code: [Forms]![Neighborhood Input Form]![Neighbors].RecordSource="SELECT * FROM qryNeighbors WHERE PeopleID = " & [Me].[PeopleID]

I placed this in the OnCurrent event of the Family Subform. The two = signs are throwing me off and I'm getting an error. It doesn't like my subform reference.

 

[pbaldy]

Try

Forms![Neighborhood Input Form]![Neighbors].Form.RecordSource="SELECT * FROM qryNeighbors WHERE PeopleID = " & [Me].[PeopleID]

Make sure you're using the name of the subform control, if different from the name of the subform.

 

[painterz]

I'm still getting the same error message: …cant find Forms!Neighborhood Input Form!Neighbors. Neighbors is the name of my Neighbors Subform. I'm currently reading an article about synchronizing two subforms https://www.fmsinc.com/microsoftaccess/Forms/Synchronize/LinkedSubforms.asp. Fingers crossed!

 

[painterz]

Nope, that's not going to help. I've got to show the Address so my main form can't be unbound.

 

[painterz]

I found a thread dated 7/30/10 where someone posted a similar question vbaInet suggested using a text box on the main form and having it be the the link to the two subforms. That worked except for on my last record--not sure what's different about it.

 

[jdraw]

You may get ideas from these videos at Datapig.
http://www.datapigtechnologies.com/flashfiles/subforms1.html

http://www.datapigtechnologies.com/flashfiles/subform2.html

 

[pbaldy]

Can you attach your db here?

 

[painterz]

It worked--now it doesn't. There doesn't seem to be a crying emoji.

I changed something somewhere and now my people subform will no longer show data.

I've checked the recordsource.
I've checked the Master/child links.

Data entry -- yes
Allow additions -- yes
Allow deletions -- yes
Allow edits -- yes
Allow filters -- yes (not sure if I need this)
Enabled -- yes
Locked -- no

What happened???

 

[painterz]

It will take a while to sanitize my db and post. I may have to though.

For some reason when I enter new data, the FK are not being recorded in the underlying queries. I thought this would take an hour to solve and I've been at it for 7 hours.

 

[pbaldy]

Data entry set to yes will prevent existing records from showing.

 

[painterz]

Ahhhhh….thank you!

 

[pbaldy]

No problemo!

 

[painterz]

Is there an easy way for me to copy my forms and queries from one database to another? I have a scaled down table in the other database.

 

[pbaldy]

In the destination db, you can use Import on the External Data tab to import objects.

 

[painterz]

I've sanitized my db but something isn't working correctly. The record indicators advance but the same person will be there, then finally the next will show. I thought it was in a query but can't find where the problem is.

My form shows the PK and FKs as I've been trying to troubleshoot why I'm not writing to the underlying query.

Neighborhood Input Form will be the first to show up.

 

[pbaldy]

What are the steps to reproduce the issue? I don't see the code we were discussion earlier.