Need help with listboxes

[hbrems]

Dear all,

please see screenshot for table structure and a form view.

tblcases is a table with information about a case.
tblissues is a table with issues that can occur for each case.
tblissuelog is a table that links cases with issues.

A case can only have the same issue once, but one issue can be applicable to many cases.

One listbox will show issues that are applicable to the case, the other will show the issues that can still be selected. In other words, these two listboxes should be the exact opposite of eachother.

I figured that I can pull the issues that are applicable from tblissuelog.

The only problem that I have is that I have no idea how to show the issues that are still available. The caseid is of course a variable and changes as I go through the records.

Kind regards,
Hans B.

 

[wazz]

i did it like this...without taking too much time, i'm not sure if there's another way.

- first, in tblissuelog you need a primary key which should be both fields.

- query 1: to see which issues have been applied
- use tblissuelog
- first fld is fldissueid; second fld is fldcaseid (unchecked)
- criteria for fldcaseid is whichever fldcaseid you want (i guess it will be the caseid of the current record of the form).

- query 2: to see which issues have not been applied
- include query 1 and tblissuelog
- join the matching fields (fldissueid)
- edit the join to: include all flds from tblissuelog
- add fldissueid from tblissuelog
- criteria for fldissueid is: Not In (query1.fldissueid)

now, i just hope i typed that all properly or at least you get the idea. post back. also, if anyone has an easier way plz post.

====== new:

i was just fooling around with this on a form and it behaves a bit differently than queries alone. i had to make a third query, based on the second query to get around a grouping problem. i used the *third* query, which is 'Grouped By', on the second listbox.

 

[hbrems]

Hello Wazz,

thanks for the reply. I got around trying it today and I got the query's working. Unfortunately I was still not able to make it work on the forms.

I tried the following in vba:

lstissuesbound.RowSource = "SELECT * from qryselected1 WHERE tblissuelog.fldcaseid = " & Me.txtcaseid (these are the issues that have been selected aka. query1)
lstissues = ?? (this should be query2 based on the rowsource of lstissuesbound)

Kind regards,
Hans B.