Open Subform based on Form with Combo Box

[Lisad]

I have a main form (FRMProspects) with a combo box (ComboCoName) that has a query as it's record source to sort the records displayed in it. It is bound by the CustID (autonumber), but displays the CompanyName.

What I want to do is open a sub form FRMNotes, based on the Company name that is selected in the ComboCoName. So for Company XY, I want to click on the Notes command button to open up all the notes for that company only (filtered). FRMNotes is based on a different table, but TBLProspects and TBLNotes are linked by CustID (one to many).

What code do I need for this? I am treading water with VBA!

 

[MStef]

Hello Lisad!

I suppose you need a MainForm only for filter.
If it's so, you don't need two forms.
Look at "DemoQryFormCA2000.mdb" I think it is what you need.

 

[Lisad]

Hello MStef!

I want another window to open when the cmdNotes button is clicked rather than the information being displayed on the same form (frmprospects). I did use a wizard to add the command button to the main form, but that didn't work as the FRMNotes, when opened up from the onclick, did not show the results for the Company I had selected in the main form - the form was blank. Good ol' Access! Seems the link between the data was not there.

Any other suggestions?

 

[reclusivemonkey]

I'm not quite sure where you have got to with this, or where the problem lies.

Is the combo box on your first form working? Can you select the correct filter and get the results in the form? If so, then all you need is a button which opens the new form with a field from this form. The first bit you won't need VBA for; the second you will (although you may be able to get away without using VBA; depends on how your form is set up). The code for opening a form is;

DoCmd.OpenForm [I]frmName[/I], [I]frmType[/I], [I]filtername[/I], [I]wherecondition[/I]

Whether you want to use a WHERE condition or a Filter is up to you. You say in your post that you want to open FRMNotes on Company Name, but I think you mean CustID; remember you have CustID as the bound column in your lookup.

You may even be able to do all this with Wizards, but its pretty simple to do it manually.

 

[Lisad]

All Done!

Thanks, i've sorted it.