Max limit to ListBox queries???

[J-F]

I'm trying to do a duty roster for 70 workers. They are identified by a three figure number. Their numbers are enterred in various TexBoxes to show their tasks for the day.

In order to check that I haven't missed anyone out, I have the complete list of available workers in a listBox.

I'm trying to get the listBox to only show the workers not tasked. I have built up a query as a RowSource which works fine but I find that when I have more than 64 workers enterred, it produces the following error 'runtime error 2176 : The setting for this property is too long'.

I've tried it manually in a query and there doesn't seem to be a limit for "SELECT ID, worker FROM Workers where WorkerNumber<>textbox1 Or WorkerNumber<>textbox2 OR WorkerNumber<>textbox3 etc etc etc. up to ListBox100

How can I get around this?

 

[raskew]

Food for thought.

How about adding a Selected Yes/No field to your workers table, with default = No.

Create a function which, when called from the AfterUpdate event of a text box, creates a recordset of the workers with the WorkerID just entered, and update Selected to Yes.

Then, in the row source of your list box use a query which selects only those workers where Selected = No. In the AfterUpdate function, requery the list box after update to Selected has been made.

 

[J-F]

Thanks for your reply. I got around it by opening the recordset with the sql string, looping through the result and adding it to the list box row source. Row source type was set to 'List Value' and 'Field count' set to What ever.

Works like a dream and hope this helps someone else.

Thanks again.

 

[jwindon]

J-F & Raskew:

I hope you don't mind, but I have been watching this thread and both of your approaches are intriquing.

J-F:

I have done something similar using an unmatched query to populate a combo. I have also used Raskew's approach.

I am wondering if you could both offer the pros and cons of these approaches.

 

[J-F]

I'm new to VBA for Access, although I have been writing Database driven websites using ASP for some time, so my knowledge of VBA/Access is relatively small.

The reason I went down a different line to my original was the 64 condition query maximum which listboxes are obviously limited to. Had this not occured, I would have carried on with making the row source the query as it saves a lot of extra programming because of the fact that you don't have to open a recordset.

I didn't go the way Raskew suggested because I dind't understand it (because of my lack of knowledge in VBA).

I would like to say that this forum is a fantastic source of information and I want to thank all who have helped me in the last 2 weeks. My project is nearing an end, thanks to you all.

J-F