Filter datasheet of split form with listbox

[DavidM]

Table tblBodypartExercises with fields // IdBodypart (PK) // IdExercise (PK) is the junction table between tblExercises with fields IdExercise (PK) // Exercise (txt) and tblBodyparts with fields IdBodypart (PK) // Bodypart (txt). Form frmExercises is a split form with a datasheet on top listing the exercises and a list box on bottom listing the bodyparts. What I want to do is select multiple bodyparts of the list box to limit (filter) the exercises of the database. How?! I can't figure it out. Please help! Thanks! Please see attached database.

[June7]

Will need VBA code. Review http://allenbrowne.com/ser-50.html

[DavidM]

Thanks, June 7, but my Access skills are not sufficient to figure out how to make the reference you cite work. I've been trying for weeks now. Now that I've attached the database, please take a look and show me what to do. Thanks!

[June7]

The example code is to open a report with a dataset filtered to the items selected in listbox.
1. on the form with listbox, create a button named cmdPreview
2. select [Event Procedure] in the button Click event property
3. click the ellipses (...) for the Click event
4. copy/paste Allen's code into the VBA procedure (all the lines between Private Sub and End Sub)
5. change the line in the procedure that sets the value of strDoc variable to your report name

This code can be adapted to set the form filter property. Instead of the last lines that refer to report (delete them), set form Filter and FilterOn properties:

Me.FilterOn = False
Me.Filter = strWhere
Me.FilterOn = True

[DavidM]

Couldn't wait to get home and try it today after work. I did try. It didn't work. I substituted stuff and tried stuff, but I couldn't get it to work. Darn@&%$*(

[June7]

What happens - error message, wrong results, nothing?

Maybe because of split form. Don't like, never use. Will have to test. Do you want to provide db? Follow instructions at bottom of my post.

[DavidM]

What happens - error message, wrong results, nothing?

Maybe because of split form. Don't like, never use. Will have to test. Do you want to provide db? Follow instructions at bottom of my post.

I tried it on my "production" db, not the model. I was that sure your fix would work. Then I had to shut down for the night. Today and tomorrow are 12 hour work days, so I don't expect to have the chance to bang away at this again until possibly the weekend or even some time later next week sadly. When I do, I'll use the model, not my "production," so I get it in your hands. Thanks very much. I'm eager to get this working!!

[June7]

This worked:

1. move the listbox into form header section, name it lbxParts

2. create a button called btnSearch

3. paste code into the button Click event VBA procedure, changed lines:
strDoc = "frmExercises"
...
With Me.lbxParts
...
strWhere = "[IDBodyPart] IN (" & Left$(strWhere, lngLen) & ")"
...
strDescrip = "Exercises: " & Left$(strDescrip, lngLen)

4. form RecordSource:
SELECT tblBodypartExercises.IdBodypart, tblBodypartExercises.IdExercise, tblExercises.Exercise FROM tblExercises RIGHT JOIN tblBodypartExercises ON tblExercises.IdExercise=tblBodypartExercises.IdExe rcise;

5. bind textbox to Exercise field, Locked Yes

6. listbox properties
RowSource: tblBodyparts
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

7. I set the datasheet to bottom of form

8. if you want code to deselect all listbox items, review http://allenbrowne.com/func-12.html

9. fields did not show in form part so I deleted the textboxes and created new ones, now they show in both sections, if you don't want both this could just be a regular form in Continuous view

[DavidM]

Getting close!!

I implemented everything you detailed (THANKS)... Clicking the button gives the following error:

cmdPreview_Click
Error 2467 - The expression you entered refers to an object that is closed or doesn't exist.
[OK]

I think maybe some report remnants remain in the code perhaps?!? I peck around to delete report stuff that I'm guessing might be the problem, but I just break it!!

[June7]

What 'report remnants'?

You must have missed something.

I would have to analyze code or db again.

[DavidM]

What 'report remnants'?

You must have missed something.

I would have to analyze code or db again.

I did exactly EVERYTHING you have been so gracious to tell me to do AGAIN and AGAIN... I looked up Error 2467 but can't make heads or tails of that. Please take a look at "BodypartExercises_08-21-13.accdb" with my best efforts therein. THANKS!!

[June7]

Yes, need to change the code that deals with report (as noted in post 4 but not repeated in post 8).

Also, if you want to allow selection of multiple items in listbox (the whole reason for using this code), need to change MultiSelect property. Then hold down shift or ctrl key when selecting items.

[DavidM]

Yes, need to change the code that deals with report (as noted in post 4 but not repeated in post 8).

Also, if you want to allow selection of multiple items in listbox (the whole reason for using this code), need to change MultiSelect property. Then hold down shift or ctrl key when selecting items.

I think I'm on the right track now!! At least I don't get the error, and the filter works! I just have to keep at it!! Please see attached.

[June7]

So issue is resolved?