What is the recordsource for your cardlist. That will tell us what is happening. Most likely there is something in the SQL / Query saying to exclude records that have null values

So, you've definitely got a complex recordsource. The problem is when it gets to the part I highlighted in bold below:

Details


SELECT CardT.CardNumber, CardSetNameT.CardSetName, CardSubSetNameT.SubSetName, SportsT.SportsName, PositionT.PositionName, CardT.CardYear, TeamT.TeamName, PlayerT.PlayerLastName, PlayerT.PlayerFirstName, CardT.CardID
FROM PlayerT
RIGHT JOIN (TeamT INNER JOIN (PositionT
     INNER JOIN (SportsT
          INNER JOIN (CardSubSetNameT
               INNER JOIN (CardSetNameT
                    INNER JOIN CardT ON CardSetNameT.CardSetNameID = CardT.CardNameSetID)
                    ON CardSubSetNameT.SubSetNameID = CardT.CardNameSubSetID)
               ON SportsT.SportsID = CardT.SportsID)
          ON PositionT.PositionID = CardT.CardID)
     ON TeamT.TeamID = CardT.TeamID)
ON PlayerT.PlayerID = CardT.PlayerID;|
Copy

That part of the relationship is telling Access to only pull up records with data in that field. It needs to be changed to pull up ALL records from CardT and only records from CardSubSetNameT that match. In that way, even if the field is blank, it will show the record.

Is that the part you boldened. I have to ask foe your help here for sure. I am not sure where or what to put.

I broke it all down and see you DELETED a whole line of code is that correct, if so I got it. Also I have the relationship on join 3.

Not seeing anything missing, but a little bug eyed this morning. All I did was an OCR screen capture from your image above and then space it out. If there is something missing let me know what/where.

The key is that join for the SubsetNameID might need changed from "INNER JOIN" to "RIGHT JOIN" So that it will pull all records from CardT and only matching records from CardSubSetNameT.

If the query/source is available in the query editor (QBE), If you can find the join line between CardT and CardSubSetNameT and ensure it has an arrow pointing toward the CardSubSetNameT that should address the issue.

you are correct. nothing is missing...its my eyes apparently...little too much anesthesia yesterday....I removed a line that i thought you had left out so i am rebuilding my card list form again. as far as joins I am not sure how to see the joins you are talking about. I know how to got o the ...ellipses and open the query I don know from there...I will try what you said when I get it rebuilt. I may follow up with you in a bit. Thank you very much.

Donald...Got everything back like it was. You say find the QBE/source. How do I find that? I did not create a query for this. If I go to relationships and pull in the Tables of CardT and CardSubSetNameT there are no relationships showing. I am a little lost about the query that you talk about. I thought I knew how to find possibly the query that may exist that Access created itself, but if it exist I dont know.

For the form's Record Source property, if you select the record source, click the 3 ellipses at the right. They should be there even if your Record Source is an SQL statement. That should take you to the Query Editor (QBE) unless the SQL is too complex, in which case it will take you to the SQL Editor and you won't be able to use the GUI that shows the lines.

here are my screen shots...and my results if I click the ellipses...

Ok not at my PC at moment so will take a look when I get home

So, to make sure I am looking at the right issue, you have a form "CardF" that has several fields. One of which is a combo box for SubSetName which holds the subsetID. If you don't have something in that field, then it doesn't show up in the "CardListF" form.

Your latest images appear to be "CardF". In order to tackle the issues you're having, we'll need to see the record source and relevant control sources for "CardListF" and/or wherever the SQL you shared above came from as that is likely where your solution will be.

Donald, Thank you for all your time. I am going to call it quits this is so messed up for some reason. I cant even get a card list made. I do thank you for your time already invested. I have my card table and player table set up good. But when I try and do other stuff that's where it gets off line somewhere. I am lost..lost

I understand if you need to step away from it. You've accomplished quite a bit. Your card list will pull data from multiple sources so it's not uncommon for one or more pieces of the puzzle not to drop right into place without some coaxing sometimes.

If you decide to look at it again later and want help, feel free to ask. I, or someone else if you prefer, will assuredly be happy explore it with you.

Found what you wanted. If I change the joins Option 1 does not solve the issue, if i choose option #2 I get the message Join Expression not supported, option # does not solve the issue either.

Donald It started working after I changed the joins back and forth a couple of times. I got the arrow pointing to the right like you described. So for now it appears good. Thank you so much for your patience.

Happy it's working for you :)

Not sure which shake of the magical 8 ball did the trick but, just in case, don't forget Richard's frequent reminders: Backup, Edit/Change, Save, Close, Reopen. Sometimes just saving, closing, and reopening helps to clear memory so it will pull a fresh copy of the source query.

Again, not sure if that would have reduced your frustration (or if that is in fact what you did), but even the toughest issues often get resolved by going back to basics.

Good work!

I am not sure. But I got hung up on the Card List and once the light bulb came on I got back to the original issue at hand. Then I started doing your suggestions and something went my way for a change. I cant say thank you enough to all of you that help.