Access form is connected to a subform just fine. I would like to connect the subform to other information in a different table. The connection is at the subform level not the form level.

I have a form and a subform that work great.  I would like a record on my subform to call another record that is connected by a linenumber id.  

Can I do something on the subform so my users will know there is data tied to the record?  There could be many records that have no information and I don't want to have  to have them click on it just to check.  

If it does have information on the other table I would like to click on a tab to show the information related to that record.
Who is Participating?
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.

Ryan ChongSoftware Team LeadCommented:
can you provide a sample to better illustrate your issue?
cres1121Author Commented:
Okay so you click on on opening form... click on okay then type in 17161  then ok, ok and okay.  I took out those fields to make the database smaller.  

Now on the subform double click on contract number which will take you to the detail.  Click on okay, ok, ok, ok, ok, ok, ok,ok till you get the form to respond... so this is where I would like to

From the tblservicelineitem.subform  

Show that line number 12665 has some data from the tblservicelinedetails and if I click on it I would like to open up the data related to this line.  

if there is no data in that table I would like it to open a blank form so we can update it but I would like it to indicate that there is data attached if there is.  So we are not just clicking for the sake of clicking.
Gustav BrockCIOCommented:
It looks like all you need is an outer join to the details table. See attached.


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
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

cres1121Author Commented:
I see you added a Details Exist.... That is perfect.... Where was the code added so I can see the magic underneath so I can get it to work on my database.  If I create a subform to the data how do I get it to open?
Gustav BrockCIOCommented:
It's the source query of the subform that I adjusted.

cres1121Author Commented:
This worked great... It is exactly what I wanted.  I changed my form to be a check box and it looks very cool!

Thank you!
Gustav BrockCIOCommented:
You are welcome!

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.