multi-select search

[potts]

Does anyone know the code for opening a form based on the values returned from a multi-select list box on the current form?

 

[Roni Sutton]

You could do this a couple of ways. The best way I have found is to create a table with the form name in one field and the menu item number in another field. Then your code sets a variable to your form name based on what the value of the list box is.

Dim formname as string

formname = dlookup("[formname]","tblFormNames","[formnumber] = " & forms!frmMenu!listbox)

docmd.openform formname


This code should go in the On Change property of the list box on the form.

Make sure your menu item number box in the table is set as a number or you have to play around with quotes which is not fun in the Dlookup statment.

 

[Tim K.]

Or you mean to open a specific form to show only record(s) selected in the list box?

If so, check this How to Use a Multi-Select List Box to Filter a Form (Q135546)

 

[potts]

not gettin it

Right I got the filter to work, but a parameter box now opens requesting the ID field value of the list box query before it will open the desired form. I've got this system to work properly on another form, but it just won't work here and I can't see that I'm doing anything differently.

Any help would be greatly appreciated as I've really hit the wall!!

 

[Tim K.]

Check if the field is the same in both forms, Text or Number? Or post the code of both.

 

[potts]

I've tried these things, but no success. Any other suggestions?

Thanks

 

[potts]

form type

The problem seems to be related to the fact that the form I want to open is a tabulated form. The other forms (that I have got to work) have been columnar.

does this make a difference? And if so is there any way to get it to work on a tabulated form?

 

[Tim K.]

I don't think the diff-viewed form will cause the problem. What method do you use since there are 2 in the linked KB activle? Post the code on the command button for opening the new form.

Do you use OpenForm with filer option or change the based query SQL of the to-be-open form?

 

[potts]

I ended up using code from an example in the microsoft access handbook. I have posted it on the forms forum under "list box search forms". I'd appreciate it if you'd have a look at it.

Thanks