Displaying records based on list boxes

[Grumbledook]

I have training table which displays training lectures attended by students i.e. each subject has a check box associated with it.

Problem:

I want to be able to select a checkbox field from a lisbox set to list each checkbox field and display corresponding records selected in another list box:

so...if Jones and Smith attended the burglary lecture, I want to be able to select Jones and Smith from one list box and Burglary lecture from another list and display them on a data access page, where I can then update the check box to reflect their attendance.

table name is 'Training'
Field name is 'Surnames'
Checkbox names = 24 unique check boxes.

I can get both list boxes to list the names and the check box fields but try as I might I cannot get them to combine to display the correct records. All I ever get back is a blank data access page.

If you need any more explanation please let me know.

G.

 

[PHV]

to display the correct records
What are for you correct records ?
Can you please post more info on the tables structure, what you have so far, some data examples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Grumbledook]

PHV - the training table consists of the students personal details plus a check box for each of the nominated training lectures.

When I say Correct records I mean that when a number of names are selected in one list box and a field checkbox selected in the second list box where the checkbox is 'false', the resultant data access page displays only those records whose names have been selected.

I have tried using Forms![Datapages]![Listnames]under the names field in a query along with Forms![Datapages]![Lectures] to produce the desired result to no effect - the subsequent SQL being:

Select Training.Rank, Training.Collar, Training.Names
From Training
Where (((Training.Names = Forms![Datapages]![Listnames]) and
(Forms![Datapages]![Lectures] = "False"))

This still produces a datapage with no records displayed.

G.

 

[PHV]

Is the Listnames listbox MultiSelect ?
If yes, you have to take a look at the ItemsSelected collection.
I quite don't understand this part of your where clause:
and (Forms![Datapages]![Lectures] = "False")
Is the Lectures control holding the table field name ?
Is this SQL created dynamically ? (I hope so)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Grumbledook]

Is the Listnames listbox MultiSelect ?
Yes.

Is the Lectures control holding the table field name ?
Yes - you select one field from a field list in the Lectures list box

Is this SQL created dynamically ? (I hope so)
Yes, it is created through design query. That is what it looks like in sql view. However, for some reason, if I try to copy&paste it to vba I get case expected errors.


If yes, you have to take a look at the ItemsSelected collection -I will have a look in the help section.

To tell you the truth, I am completely stumped on this one and it is probably something so simple I just cannot see it.

Thnaks for your interest by the way

G.

 

[PHV]

I presume the SQL is created in the Click event procedure of a CommandButton:
strSQL = "SELECT Rank,Collar,Names FROM Training WHERE Names IN ("
strNames = ""
With Listnames
For i = 0 To .ItemsSelected.Count - 1
strNames = ",'" & .ItemData(.ItemsSelected(i)) & "'"
Next i
End With
strSQL = strSQL & Mid(strNames, 2) & ") AND NOT [" & Lectures & "];"
I assumed the fields in Lectures are Boolean.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244