Filter a sub form

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

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.

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

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

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

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.

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

correction....


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

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

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

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

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

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