asked on

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.

Thanks.

K
datasample2.bmp
datasample3.bmp

JimFive

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 Schaefer

ASKER

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"

ASKER CERTIFIED SOLUTION

JimFive

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

Thanks for the input, however, this project was cancelled I no longer need assistance with this issue.