Update a list box

[bwyrwitzke]

I was hoping Nero's response to the DLOOKUP question would help solve my problem...

BACKGROUND: The Form (frmSearch) has two sections. One side consists of 7 check boxes and the other side is a listbox. Each time a check box is checked, a requery occurs, updating the records that match the checks. The other side of the form is a list box that is based on the query created with the check boxes.

GOAL: Update the list box everytime a box is checked, without prompting the user to click a command button

WHAT'S REALLY HAPPENING: The list box never changes. It always shows the entire list. To ensure that the query is updating, after every event, a dialog box opens with the same list box, and it updates as expected.

QUESTION: How do I update the list box without prompting the user or opening a new form? I've tried me.listbox.requery just about everywhere. Is there another command to refresh? Thanks!

Bruce

 

[IfButOnly]

Bruce, what you are doing sounds correct - it is odd that your dialog box is working fine and your form is not.

a few things to check. Is the refresh code being executed when you click a check box - verify with a msgbox.

Make sure that the onclick event on the check box, or option box, links to your procedure. Might be different, if you have changed the name of the check boxes and forgot to update the procedure - I suggest this only because you obviously created a new reference to the checkboxes in your dialog box, and it worked.

The list box is not in a subform is it?

When you update the rowsource, trap your SQL statement and check it in a query.

 

[bwyrwitzke]

I'll give it a try.

No, the list box is not on a subform. Thanks.

Bruce

 

[bwyrwitzke]

Also,
The dialog box only refreshes when I close it and reopen.

 

[IfButOnly]

Are you using 7 check boxes or an option box?
Is the refresh being done on the click event?
Have you verified that you are getting to refresh code on the form?

let me know and we will see if we can narrow it down.

regards... Peter

 

[bwyrwitzke]

Peter,
The answer to the first question is 7 Check boxes. The user needs to be able to check all, some or none of the options. I've included a screenshot to help...

I'm still new to Access and VB so, I'm not sure how to do the trap you were asking about. Any clarification would be helpful. Thanks.

Bruce

 

[IfButOnly]

Bruce, the problem with your approach is that if the user is making four selections from your listbox, it will be refreshed each time they click one of the checkboxes - I'm sure you are aware of that though.

I assume that as each checkbox is clicked, a function is executed which checks which checkboxes are clicked and builds an SQL statement. There is probably then a statement like

Me!ListBoxName.Rowsource = strSQLstring

Add a large text box to your form called viewSQL. After the above statement in your procedure, insert

Me!viewSQL = strSQLstring (or whatever the string is called)

run the form. Each time you click a check box, viewSQL should be updated - if not, we know that you are not getting to the code.

If it is being updated, then copy the contents of viewSQl to the clipboard, close or minimize the form, open a new queries in design view, don't add any tables and go to the SQL view. Paste your code from the click board and press the run button.

If there are any problems with your code, it will show up here and be easier to fix.

 

[bwyrwitzke]

Peter,
It worked like a charm. I had originally put the statement in the wrong spot and it was updating before the query updated. I took your advice and put in a cmd button to update the list after the user makes all their selections - things move a bit faster that was! Thanks again for your help and patience.

Bruce