How can I do this with a CheckedListBox?

[PaulieORF]

Here's my situation. I have an Access Database that has 3 tables; Users, Interests, and Interests_Log. Each user int he Users table has a User_ID, and each interest in the Interests table has an Interest_ID, as well as an Interest_Name. The Interests_Log table consists of a composite key: User_ID and Interest_ID.

 

What I want to be able to do here is when someone is editing a user's interests, to be able to see a CheckedListBox that contians the interests from the Interests table. If the Interests_Log table contains any Interest_ID's for the selected User_ID, then those items should be checked when the form loads. If the user clicks on an Interest that was not previously checked, I'd like it to add a new row to the Interests_Log table that would consist of the current User_ID and the Interest_ID of the newly checked item(s).

 

I can get as far as setting the datasource, display member, and value member of the CheckedListBox. I just can't figure out a way to make the CheckedListBox do what I want it to. Could someone please help me with this? It would be greatly appreciated. Thanks!

[kejpa]

I would do subselect...

SELECT i.*, (Select il.User_ID from Interest_log il where il.Interest_ID=i.Interest_ID and il.User_ID=1) AS User_ID FROM Interests i

The records will show the User_ID if it's in the Interest_log table and you can set the checkboxes to true in those cases. If it's DBNull it's not a selected Interest

 

HTH

Kejpa

[PaulieORF]

Okay, that select statement does work and gets me the info I want. However, I cannot seem to get it to work properly in VS.NET. Keep in mind, I'm a beginner with VB.NET, and I'm using the OleDbDataAdapter wizard to work with my Access DB. When I put this statement into the wizard and run it, it works. But when I have the wizard finish up, it gives me an error saying that it was unable to generate the SELECT statement. What should I do to get around this? Thanks, and I really appreciate your help, a LOT.

[SonicBoomAu]

Can you put a copy of your code up here please

[PaulieORF]

I'm not really sure what code you want me to post up. It's not a problem with any code, it's just that the Data Adapter Configuration Wizard will not allow me to configure the Data Adapter. Under "Generated SELECT statement" on the final screen of the wizard, it says "There were errors configuring the data adapter." I can still click finish, but I cannot make a DataSet from it.

[PaulieORF]

Anybody else have any advice for me on this? Thanks.