Filter a sub form

correlate
correlate used Ask the Experts™
on
Dear Experts

I am trying to apply a filter to a sub form from a list box.

The List box "PeopleList" has amongst other data a first column of [UniqueID] (which is hidden from view).

The Sub Form "BioViewer" has 4 fields in it including  [UniqueID] and all data comes from the same query as the list box.

What I am after doing is this ... when the user clicks on a person in the "PeopleList" list box, the Sub Form "Bioviewer" should filter or navigate straight to that record.

Can anybody help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
1st, in the after update event of your list box (PeopleList) have:

MsgBox PeopleList.column(0)
MsgBox Vartype(PeopleList.column(0))

1st one to make sure you are getting the correct ID and the second on to find its variable type. This just a test before we move on.

brb
Mike EghtebasDatabase and Application Developer

Commented:
re:> filter or navigate straight to that record.

Which you want filter or navigate?

Mike
This is a fairly comon task.  
1. Create form with your list box source will be "PeopleList"
2. Create query "SelectedUser" with all the fields from "BioViewer" and [UniqueID] criteria = Form.Listbox.Value
3. Create subform with field source of "SelectedUser" and relevant fields pulling data you wish to display
4. On the list box event 'After Update' perform a refresh/redraw on the subform.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Hi eghtebas,

Ran the test - the [UniqueID] is coming up correct and the MsgBox Vartype(PeopleList.column(0)) comes up with "8".

as far as Filter or navigate, i really dont mind, whatever is quicker to retunr results / is deemed better practice
Mike EghtebasDatabase and Application Developer

Commented:
8 means it delivers it as string whereas we need it as long (assuming it is long in your table) so we will have it as:

MsgBox Vartype(Clng(PeopleList.column(0)))

down the line.

brb
Mike EghtebasDatabase and Application Developer

Commented:
sorry for the delay...

In the afterupdate event ofthe list box have:


Me.sfSubformName.Form.Filter = Clng(PeopleList.column(0))
Me.sfSubformName.Form.FilterOn = True
Mike EghtebasDatabase and Application Developer

Commented:
When you open your main form in design view and click on the subform once, the property sheet shows the subform name (replace sfSubformName I have used here with whatever you have). Remember to click only once on the subform to see its container name.
Mike EghtebasDatabase and Application Developer

Commented:
This will give you by filter. After the filter you may wish to remove filter. To do so, add a button "Reset" and in its on click event add:

Me.sfSubformName.Form.FilterOn = False
Mike EghtebasDatabase and Application Developer

Commented:
correction....


Me.sfSubformName.Form.Filter = "UniqueID=" & Clng(PeopleList.column(0))
Me.sfSubformName.Form.FilterOn = True

Author

Commented:
Hi,

Tried this, unfortunately I keep getting a run time error '13': type mismatch, I've tried playing around with it a bit doing things such as putting in the full field name (is with the query name in it etc) but alas no joy.  The only other thing that may make a difference is that the UniqueID's can contain spaces - not sure if that makes any difference
Mike EghtebasDatabase and Application Developer

Commented:
I have tested the solution before posting it. If you could isolate the portion we are working with with min. data, upload it for me to take a look at it.

Is UniqueID field Long Integer or it is text?

Mike
Database and Application Developer
Commented:
If it is text then use:

Me.sfSubformName.Form.Filter = "UniqueID='" & PeopleList.column(0) & "'"
Me.sfSubformName.Form.FilterOn = True




Author

Commented:
The unique ID is text which is formed from this ... The original data comes from a linked excel spreadsheet and is formed by joining up 4 fields:  name, location, company, job title.

Technically there is the possibility of this not being unique, but with the sample data I'm using it is (I've run duplicate checks based on this).  To ensure the unique ID remains unique users will be putting the raw excel data through an access validation tool prior to submitting data.

Sorry I should have mentioned this in the original post.

Unfortunately I won't be able to upload a dataset until tomorrow when I am back in the office, but will do so first thing

Author

Commented:
Just got your post - will give that a go first thing - thanks for your help on this

Author

Commented:
Thank you so much (finally also managed to login from home so was able to test it) - works a treat

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial