Navigation subforms and the use of a mix of both Unbound subform and bound subform on the same tab

Help I am new to the use of Navigation subforms (tabs)  I need to display a unbound form used for data-entry and next to that form display a bound form that will be bound to a global variable set by the first tab's forms.  I tried using a master form containing both unbound and bound fields, however, I have no way to connect the data.  The global variable will hold the CityRecID (PK) for most records.

I am looking for sources to help with this issue, Didn't have much luck with googling the question.  What is the best approach to use the Navigation forms and still able to use both types of subforms?  Form on right displays blank even though it has 1 record in query results.

SELECT IIf(IsNull([StateCode]),[CityName] & ', ' & [CountryCode],[Cityname] & ', ' & [StateCode] & ', ' & [CountryCode]) AS Header, UCase(IIf(IsNull([StateCode]),[CityName] & '' & [CountryCode],[Cityname] & '' & [StateCode] & '' & [CountryCode])) AS [Unique Identifier], tblcity.TaxInclusive, tblcity.Top5000, tblTC.CollectedbyName, qryConfidenceLevel_Determination.ConLevels AS ConfidenceLevel, qryNotes_ExternalOnly.Comments, tblcity.CityRecID
FROM ((tblState RIGHT JOIN (tblCountry RIGHT JOIN (tblcity LEFT JOIN tblTC ON (tblcity.CityRecID = tblTC.CityRecID) AND (tblcity.CityRecID = tblTC.CityRecID)) ON tblCountry.CountryRecID = tblcity.CountryRecID) ON tblState.StateRecID = tblcity.StateRecID) LEFT JOIN qryNotes_ExternalOnly ON tblcity.CityRecID = qryNotes_ExternalOnly.CityRecID) LEFT JOIN qryConfidenceLevel_Determination ON tblcity.CityRecID = qryConfidenceLevel_Determination.CityRecId
WHERE (((tblcity.CityRecID)=1604));

Open in new window

Please look at attachments.


Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I would think you could do the bound form normally with the Master Linked Field = "Global Variable" and Child Linked Field = CityRecID.  BTW if your control has the same name as a data field this can cause Access to get confused about which one to use so make sure your controls have unique names.

If that isn't working, an option is to use Visual Basic to change the Recordsource or Filter of the bound subform at the point when you set the Global Variable.  You may need to do the same thing at certain events, such as on open, on current, and when you add something in the unbound form.

Another thing to note is when you add something using the unbound form it won't show up in the bound form until the bound form has been refreshed.
Karen SchaeferBI ANALYSTAuthor Commented:
ok I decide to make both bound, however form1 is in add mode and I need to update form 2 upon save of new record.

Since there isn't a Recid until new record is create I am unable to us MasterChild Links - How do I update form 2 to reflect the new record enter, yes I believe it is redundant, but got to give the customer what they want.

I even tried to reset the sourceobject without success.
Forms![frmTC_Detail_Main]![frmLocationDetail_RightSide].SourceObject = "frmLocationDetail_RightSide"

What is supposed to show up on Form 2, while entering an Add on form 1?  How can there be child data for something that isn't entered yet?

So, I'm making a guess here.  I think what you want to do is change the recordsource for form2 so that it displays all the records, then whenever something changes on form1 you need to create a Filter string and apply it to Form2.filter and set form2.filteron =true

Building the filter in VBA is fairly straightforward, and you'll need to recreate the filter in the afterupdate of every control on Form1 and probably Oncurrent of the form.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the input, however, this project was cancelled I no longer need assistance with this issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.