Solved Check if entry in connection table exist and output bool (1 Viewer)

Saphirah

Active member
Local time
Today, 18:59
Joined
Apr 5, 2020
Messages
161
Hey everyone,

in my current project i have a list of Car Types one should be able to assign for each customer.
List.PNG


for this system i have 3 tables.
tbl_CarType(CarID, TypeName)
tbl_Customer(CustomerID)
tbl_Customer_CarType(ConnectionID, CustomerID_F, CarID_F)

You can see tbl_Customer_CarType is a connection table which stores the relationships between Customers and CarTypes.
So whenever the user selects a car type in the list a new record is created in tbl_Customer_CarType, storing the relationship between the CarType and the customer.
When the user unselects the CarType, the corresponding record is deleted again.

What i want now is to generate a list like you can see in the screenshot, where the user is able to select and unselect all car types.
For this i need to show all car types at every time and indicate (using the checkbox) if a relation between the Customer and the CarType already exist.
I can then run my vba code using the mouse button down event on the checkbox.
But whenever i try to create a query a bunch of car types are missing.

This is my current query
Code:
SELECT tbl_CarType.CarID, tbl_CarType.TypeName, tbl_Customer_CarType.CustomerID_F, Not [CustomerID_F] Is Null AS Selected
FROM tbl_CarType LEFT JOIN tbl_Customer_CarTypeON tbl_CarType.CarID= tbl_Customer_CarType.CarID_F
WHERE (((tbl_Customer_CarType.CustomerID_F)=[Forms]![frm_Customer]![CustomerID] Or (tbl_Customer_CarType.CustomerID_F) Is Null));

Any help would be highly appreciated. Thank you very much for your time
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:59
Joined
May 7, 2009
Messages
14,626
you can just "keep" those Unticked cars on tbl_customer_cartype table.
note that i added index (unique) on customerid + carid, on the above table.
 

Attachments

  • car_type.accdb
    640 KB · Views: 59

Saphirah

Active member
Local time
Today, 18:59
Joined
Apr 5, 2020
Messages
161
Thank you very much. This solves the problem. While this approach does produce a lot more data it is a good workaround :)
 

Saphirah

Active member
Local time
Today, 18:59
Joined
Apr 5, 2020
Messages
161
you can just "keep" those Unticked cars on tbl_customer_cartype table.
note that i added index (unique) on customerid + carid, on the above table.
Does creating a unique index on 2 fields only block the creation of the new record if BOTH fields match?

So to be more clear, does the following work?:
CustomerID: 1, CarID: 5
CustomerID: 1, CarID: 6
CustomerID: 1, CarID: 7

and the following does not?:
CustomerID: 1, CarID: 5
CustomerID: 1, CarID: 5
CustomerID: 1, CarID: 5

Because if that is how it behaves i learned something new :D
 

Users who are viewing this thread

Top Bottom