# Access 2007 - Open a form from a subform (filter records)



## niccyka (Dec 5, 2008)

I am still trying to get to grips with Access 2007 - there's a lot more than cosmetic changes!!Here's my conundrum....I have a form with a subform in it and that subform has a subform in it (nested subform).Main form Name: frmEngineerSubform 1 Name: joinTasksEmployeeSubform (joined to Main form by EmpID)Subform 2 Name: joinTaskServersSubform (joined to Subform 1 by TaskID)I would like to create a command button (or an event procedure) to open up another form called frmServer (which also has a subform called frmNote joined by ServerID).I would like frmServer to open and show the record (will be singular) for the selected server (and in turn display all the notes associated with it in the subform) I've tried a number of things and can't seem to get it right - have started climbing the walls! Any ideas? Will be much appreciated - have wasted almost a week on this!


----------



## OBP (Mar 8, 2005)

niccyka, welcome to the forum, it is a pity that you didn't come here a bit sooner to save your nerves.
Did you use the Toolbox Command Button Wizard to create your button to open the Server Form?
If you did, you probably didn't notice that it "Defaults" to "Show all Records in the new form".
Whereas the version that you want is to "Open the Form and find Specific Data to display", when you choose that option it will allow you to select the ServerID in both Forms to Filter the new form.


----------



## niccyka (Dec 5, 2008)

Hi there OBP,Thank you for your quick reply. Yes, I did select the 'open form to display selected records' - and looking at the displayed result - it does indeed look like the records are filtered (navigation bar at bottom shows 'filtered') - but in reality - all records are selected.I have read in several places on the internet (google) that many people are having a similar issue with Access 2007 - but I haven't yet for a solution for it!Do you have any alternative ideas as to how I might get around this? I have tried creating a query based on the selected criteria - but this doesn't seem to work - maybe it's the way I am creating it though.Any help would be appreciated.CheersNiccyka


----------



## OBP (Mar 8, 2005)

niccyka, there are a couple of ways around it, can you post the VBA that was generated for the Command button, it may be possible to modify that to get it working?
The other method is to use a Query with a Criteria Row that matches your Forms Current record ServerID number. Which of the Subforms has the ServerId on it, the first or the Second? If it is the first then in the query type this in to the ServerId Column's first Criteria Row

forms![mainformname]![Subformname]![ServerID]

Where mainformname is the name of the main form and Subformname is the name of the first subform.
I am not sure if you can extend that principle to include the Sub Sub Form by using

forms![mainformname]![Subformname]![SubSubformname]![ServerID]

But if you can't we can cheat a bit by putting a "dummy" ServerID field on the 1st Subform and populate it with the ServerID with some very simple VBA.

If that also doesn't work we may be able to set the frmServer Filter directly using VBA.


----------



## niccyka (Dec 5, 2008)

Hello again!I think I have just got myself in a right ol state now!Being Access 2007, I dont seem to be able to save the embedded macro as code.The embedded macro reads as follows:Action: OpenFormArguments: frmServer, Form, , "[ServerID]=" & "'" & [ServerID] & "'", , Normal Form Name: frmServer View: Form Filter: Where Condition: "[ServerID]=" & "'" & [ServerID] & "'" Data Mode: Window Mode: NormalI have tried (reasonably successfully) passing the ServerID from subform2 to frmServer  but dont seem to be able to move on from there.My thoughts were if I could pass the ServerID through, I could set the form control source to a query based on that value  but that doesnt seem to work either!Looking forward to your replyNiccy


----------



## OBP (Mar 8, 2005)

niccy, can you convert the database to Access 2000- 2003, zip it and post it on here as an attachment?
It deosn't need any real data in it, just a couple of dummy records so that I can get the Forms working.


----------



## niccyka (Dec 5, 2008)

Hello OBP, don't seem to be able to do anything from the work PC - having trouble uploading a reply even! will have to try from home.


----------

