Blank Form with Two Subforms

Hi experts. I have a form, frmMortgage. On that for there are two subforms, sfmMortgage and sfmMortgageOther. There may be returned records in one or both of these subforms.

I'm running into an issue where if one of the subforms returns no value, both subforms disappear. Anyone know of a way to get the second subform to display if the first returns nothing?

In the attached screen shot, I tricked the system by adding a blank record in the top section. Once I did that, both sections showed up. This doesn't seem like the most logical solution.

Thank you!!
mortgage-info.jpg
acramer_dominiumAsked:
Who is Participating?

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

x
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.

John TsioumprisSoftware & Systems EngineerCommented:
Do the 2 subforms have fields in common ....i think something in the linking is wrong...
PatHartmanCommented:
Are you using a query that includes a join to both tables?  Since the two subform tables are not related to each other, they should never occur in the same query.
acramer_dominiumAuthor Commented:
PatHartmon - They each have their own record source. However the tables in that record source are identical. The only difference is the criteria in the mortgage type field.

Each of the subforms are linked to the main form with field 'txtid' our unique identifier.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

acramer_dominiumAuthor Commented:
John - The two forms have identical fields. The only difference is the criteria in the query.
PatHartmanCommented:
However the tables in that record source are identical.
Did you include BOTH tables in the queries for EACH subform?  Please post the two queries.
acramer_dominiumAuthor Commented:
PatHartman -

Query 1
SELECT tblMortgage.txtID, tblMortgage.*, tblMortgagePayments.*, tblMortgage.RecordID, tblMortgagePayments.MortgageID
FROM tblMortgage INNER JOIN tblMortgagePayments ON tblMortgage.MortgageID = tblMortgagePayments.MortgageID
WHERE (((tblMortgage.txtID)=proptxtid()) AND ((tblMortgage.RecordID)=[Forms]![frmMortgage].[sfmMortgageInfo].[Form].[RecordNo]) AND ((tblMortgagePayments.MortgageID)=[Forms]![frmMortgage].[sfmMortgageInfo].[Form].[txtMortID]));


Query 2
SELECT tblMortgage.txtID, tblMortgage.*, tblMortgagePayments.*, tblMortgage.RecordID, tblMortgagePayments.MortgageID
FROM tblMortgage INNER JOIN tblMortgagePayments ON tblMortgage.MortgageID = tblMortgagePayments.MortgageID
WHERE (((tblMortgage.txtID)=proptxtid()) AND ((tblMortgage.RecordID)=[Forms]![frmMortgage].[sfmMortgageInfoOther].[Form].[RecordNo]) AND ((tblMortgagePayments.MortgageID)=[Forms]![frmMortgage].[sfmMortgageInfoOther].[Form].[txtMortID]));
PatHartmanCommented:
Why are the criteria in these queries pointing to the subforms (AND ((tblMortgagePayments.MortgageID)=[Forms]![frmMortgage].[sfmMortgageInfo].[Form].[txtMortID) and (AND ((tblMortgagePayments.MortgageID)=[Forms]![frmMortgage].[sfmMortgageInfoOther].[Form].[txtMortID])?  

Is txtMortID a search field?

If you include the parent table (and I'm not sure that you should be), then you must use a Left join because an inner join will not return any records if there is no match in tblMortgagePayments.
acramer_dominiumAuthor Commented:
PatHarman. Sorry for the delayed response. These Access databases are a small fraction of my duties and I haven't been able to get back to this.

The queries are pointing to those forms to reference the correct mortgage ID. It's a unique ID for that mortgage for that properties record. I just tried changing them to a left join so all records pull in from mortgage and only corresponding records from mortgage payments and it's still blank.
PatHartmanCommented:
Sorry.  It simply does not make sense that the criteria is pointing to the form that is displaying the recordset unless you have unbound  "filtering" fields and it doesn't look like that based on the picture you posted.  I don't see anything in the queries that separates proprietary debt from other debt.

Is proptxtid() a function?  What does it do?

Can you post a stripped down version of the database?
acramer_dominiumAuthor Commented:
PatHartman. proptxtID() is a public variable to grab the unique ID of the 'property' in our case.

I don't think my 'copy' worked. below is the second query

SELECT tblMortgage.*, tblLender.*, tblMortgagePayments.*, tblProperty.txtTotalUnits, IIf(tblMortgage.HSDebt="Hard",tblMortgage.CurrentBal,"0") AS Hard, tblProperty.NoMortgage, tblProperty.txtID, tblMortgage.txtLoanType
FROM ((tblLender RIGHT JOIN tblMortgage ON tblLender.LenderID = tblMortgage.LenderID) LEFT JOIN tblMortgagePayments ON tblMortgage.MortgageID = tblMortgagePayments.MortgageID) LEFT JOIN tblProperty ON tblMortgage.txtID = tblProperty.txtID
WHERE (((tblMortgage.txtLoanType) Like "other*") AND ((tblMortgage.txtID)=proptxtid()));
acramer_dominiumAuthor Commented:
I figured it out. You got me thinking about the record source of each of my queries. My main form (that holds the two subforms) had the same record source as the first subform, therefore if there were no results on the first subform, there weren't any results on the main form as well.

I took that record source out of the main form and just listed the main table I needed.

It's working!!!! Thank you!

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
acramer_dominiumAuthor Commented:
With PatHartman's questions I was able to find the solution on my own.
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 Office

From novice to tech pro — start learning today.