unmatched query trouble

[Homegrownandy]

I'm trying to create a query that I can use in a combo box. I want the combo box to only show "card name" where the selected person doesn't already have one. This here is giving no results. but I cant figure out why that is. any advice appreciated.

[andy49]

Is combo20 a list of names?

[Homegrownandy]

it is yes. their ID will be the saved field. I want it to show only cards they don't have already. the ones they do already have I can have an edit button for. But I don't want duplicates to be possible.

I know I can do this using the key of the two tables involved but I don't think this should be too much trouble to just show the cards they don't have.

[andy49]

What happens when you replace = forms etc by a number.

[Homegrownandy]

a couple of things. I have tried the "is Null" on the work_cards field as this is where I believe it should be. still no joy. The reference to the combo box is correct. If I remove the is null it works like a normal select query and will show what they DO have.

In this case the results will show a number in that field

[andy49]

Work_cards.cardID cannot be null. It's a primary key.

[Homegrownandy]

Yes that's what I mean. I didn't explain very well. I've moved the is null onto the work_cards field on then work card JB table. same results.

[andy49]

Your join is wrong as only primary key values are allowed with copies from other table. Try right join.

[Homegrownandy]

still no joy mate. I would have thought that the table [work_cardsT] needs to show all because that is the list of cards. I have tied all sorts.

[Homegrownandy]

I think the fact its a junction table doesn't help. Ill look into it a bit more with junction tables.

[andy49]

I'll look if you've got a copy on here?

[Homegrownandy]

sure one sec, thanks.

[Homegrownandy]

Database1 - Copy.zip

I've deleted any sensitive data or edited it if needed.

the main menu has a button called view work cards. open that.

at the top right is a combo box and a button. when the button is pressed it should open the form for adding new cards for the selected person.

I was working on the row source on that last form. (expiry dates add/edit). That is there the screenshot earlier came from. Thanks for your time also.

[andy49]

Two queries. I called the first QRYHASONE (it selects who has a card)

SELECT Work_Card_JB.WORK_CARDS, Work_Card_JB.Contact_ID
FROM Work_CardsT INNER JOIN Work_Card_JB ON Work_CardsT.Card_ID = Work_Card_JB.Work_Cards;

Then a list of who hasn't from here

SELECT ContactT.Contact_ID, QRYHASONE.WORK_CARDS
FROM ContactT LEFT JOIN QRYHASONE ON ContactT.Contact_ID = QRYHASONE.Contact_ID
WHERE (((QRYHASONE.WORK_CARDS) Is Null));

Hope this is it?

[Homegrownandy]

I'm sorry for the confusion. Ill have to explain better in future. I want to see in that combo box all the cards they do not have already.

so the person ID is a constant (defined by the combo box) then if that person has a cscs card.. the user is unable to select cscs card. They should go to the existing record and edit instead.

its getting there though. I see what you did with the query there. I may be able to work with this to see what I want.