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.

cres1121
cres1121 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
can you provide a sample to better illustrate your issue?

Author

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.
Database4.accdb
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
It looks like all you need is an outer join to the details table. See attached.

/gustav
Database4.accdb
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It's the source query of the subform that I adjusted.

/gustav

Author

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!
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial