Using a Where Filter to Open a form

Discussion in 'Microsoft Access Form Coding' started by Sue Compelling, Oct 24, 2009.

  1. Hi ALL

    I've used a macro to open my form "FrmSiteSubform" in dialogue mode using a
    where statement of ... [tblsites].[siteID]=[Forms]![frmcontacts]![combosite]
    ....

    It works well but I now want to copy and paste a field from my Contacts form
    to my SiteSubform - once it opens - so need to convert the above macro into
    VBA ...

    I've tried using answers in the forum though keep coming unstuck ... HELP

    TIA
    --
    Sue Compelling
     
    Sue Compelling, Oct 24, 2009
    #1
    1. Advertisements

  2. So you can use the DoCmd.OpenForm to launch your form and the 4th input
    variable is a WHERE argument. So you would do something like:

    If siteID is a number:
    DoCmd.OpenForm "FrmSiteSubform",acNormal, ,"[siteID]=" &
    [Forms]![frmcontacts]![combosite], ,acDialog

    If siteID is a string:
    DoCmd.OpenForm "FrmSiteSubform",acNormal, ,"[siteID]='" &
    [Forms]![frmcontacts]![combosite] & "'", ,acDialog
    --
    Hope this helps,

    Daniel Pineault
    http://www.cardaconsultants.com/
    For Access Tips and Examples: http://www.devhut.net
    Please rate this post using the vote buttons if it was helpful.



    "Sue Compelling" wrote:

    > Hi ALL
    >
    > I've used a macro to open my form "FrmSiteSubform" in dialogue mode using a
    > where statement of ... [tblsites].[siteID]=[Forms]![frmcontacts]![combosite]
    > ...
    >
    > It works well but I now want to copy and paste a field from my Contacts form
    > to my SiteSubform - once it opens - so need to convert the above macro into
    > VBA ...
    >
    > I've tried using answers in the forum though keep coming unstuck ... HELP
    >
    > TIA
    > --
    > Sue Compelling
     
    Daniel Pineault, Oct 24, 2009
    #2
    1. Advertisements

  3. Thanks so much Daniel ... simple when you know how - if you have a minute ...

    is there a way that I can now tell access to go to the subform within this
    subform and set focus on the ContactFK of a new record?

    I'm trying setfocus, goto, me. all sorts of the only combinations I know ...


    The subform is called: FrmSiteSubform (which we opened)
    The subsubform is called: FrmSiteRosterOneSubform
    The control of the new record is: ContactFK
    --
    Sue Compelling


    "Daniel Pineault" wrote:

    > So you can use the DoCmd.OpenForm to launch your form and the 4th input
    > variable is a WHERE argument. So you would do something like:
    >
    > If siteID is a number:
    > DoCmd.OpenForm "FrmSiteSubform",acNormal, ,"[siteID]=" &
    > [Forms]![frmcontacts]![combosite], ,acDialog
    >
    > If siteID is a string:
    > DoCmd.OpenForm "FrmSiteSubform",acNormal, ,"[siteID]='" &
    > [Forms]![frmcontacts]![combosite] & "'", ,acDialog
    > --
    > Hope this helps,
    >
    > Daniel Pineault
    > http://www.cardaconsultants.com/
    > For Access Tips and Examples: http://www.devhut.net
    > Please rate this post using the vote buttons if it was helpful.
    >
    >
    >
    > "Sue Compelling" wrote:
    >
    > > Hi ALL
    > >
    > > I've used a macro to open my form "FrmSiteSubform" in dialogue mode using a
    > > where statement of ... [tblsites].[siteID]=[Forms]![frmcontacts]![combosite]
    > > ...
    > >
    > > It works well but I now want to copy and paste a field from my Contacts form
    > > to my SiteSubform - once it opens - so need to convert the above macro into
    > > VBA ...
    > >
    > > I've tried using answers in the forum though keep coming unstuck ... HELP
    > >
    > > TIA
    > > --
    > > Sue Compelling
     
    Sue Compelling, Oct 24, 2009
    #3
  4. The basic format for referencing a form explicitly is:

    Forms![FormName].Form.ControlName

    When referencing a subform, it is:

    Forms![FormName]![SubFormName].Form.ControlName

    With multiple subforms, it is:

    Forms![FormName]![SubFormName]![SubFormName]!...![SubFormName].Form.ControlName

    So in your case you'd do something like:

    Forms![FrmSiteSubform]![FrmSiteRosterOneSubform].Form.ContactFK.SetFocus
    --
    Hope this helps,

    Daniel Pineault
    http://www.cardaconsultants.com/
    For Access Tips and Examples: http://www.devhut.net
    Please rate this post using the vote buttons if it was helpful.



    "Sue Compelling" wrote:

    > Thanks so much Daniel ... simple when you know how - if you have a minute ...
    >
    > is there a way that I can now tell access to go to the subform within this
    > subform and set focus on the ContactFK of a new record?
    >
    > I'm trying setfocus, goto, me. all sorts of the only combinations I know ...
    >
    >
    > The subform is called: FrmSiteSubform (which we opened)
    > The subsubform is called: FrmSiteRosterOneSubform
    > The control of the new record is: ContactFK
    > --
    > Sue Compelling
    >
    >
    > "Daniel Pineault" wrote:
    >
    > > So you can use the DoCmd.OpenForm to launch your form and the 4th input
    > > variable is a WHERE argument. So you would do something like:
    > >
    > > If siteID is a number:
    > > DoCmd.OpenForm "FrmSiteSubform",acNormal, ,"[siteID]=" &
    > > [Forms]![frmcontacts]![combosite], ,acDialog
    > >
    > > If siteID is a string:
    > > DoCmd.OpenForm "FrmSiteSubform",acNormal, ,"[siteID]='" &
    > > [Forms]![frmcontacts]![combosite] & "'", ,acDialog
    > > --
    > > Hope this helps,
    > >
    > > Daniel Pineault
    > > http://www.cardaconsultants.com/
    > > For Access Tips and Examples: http://www.devhut.net
    > > Please rate this post using the vote buttons if it was helpful.
    > >
    > >
    > >
    > > "Sue Compelling" wrote:
    > >
    > > > Hi ALL
    > > >
    > > > I've used a macro to open my form "FrmSiteSubform" in dialogue mode using a
    > > > where statement of ... [tblsites].[siteID]=[Forms]![frmcontacts]![combosite]
    > > > ...
    > > >
    > > > It works well but I now want to copy and paste a field from my Contacts form
    > > > to my SiteSubform - once it opens - so need to convert the above macro into
    > > > VBA ...
    > > >
    > > > I've tried using answers in the forum though keep coming unstuck ... HELP
    > > >
    > > > TIA
    > > > --
    > > > Sue Compelling
     
    Daniel Pineault, Oct 25, 2009
    #4
  5. Hi Daniel

    Thanks - but odd - the form upens - though doesn't go to the control, then
    when I close the form I have an error message - saying the DB couldn't find
    the FrmSiteSubform ... referred to in the VBA (when infact it's just opened
    it!!!



    Private Sub CmdSchedules_Click()
    DoCmd.OpenForm "FrmSiteSubform", , , "[siteID]=" &
    [Forms]![frmcontacts]![ComboSite], , acDialog
    Forms![FrmSiteSubform]![FrmSiteRosterOneSubform].Form.ContactFK.SetFocus
    Exit_CmdSchedules_Click:
    Exit Sub

    --
    Sue Compelling


    "Daniel Pineault" wrote:

    > The basic format for referencing a form explicitly is:
    >
    > Forms![FormName].Form.ControlName
    >
    > When referencing a subform, it is:
    >
    > Forms![FormName]![SubFormName].Form.ControlName
    >
    > With multiple subforms, it is:
    >
    > Forms![FormName]![SubFormName]![SubFormName]!...![SubFormName].Form.ControlName
    >
    > So in your case you'd do something like:
    >
    > Forms![FrmSiteSubform]![FrmSiteRosterOneSubform].Form.ContactFK.SetFocus
    > --
    > Hope this helps,
    >
    > Daniel Pineault
    > http://www.cardaconsultants.com/
    > For Access Tips and Examples: http://www.devhut.net
    > Please rate this post using the vote buttons if it was helpful.
    >
    >
    >
    > "Sue Compelling" wrote:
    >
    > > Thanks so much Daniel ... simple when you know how - if you have a minute ...
    > >
    > > is there a way that I can now tell access to go to the subform within this
    > > subform and set focus on the ContactFK of a new record?
    > >
    > > I'm trying setfocus, goto, me. all sorts of the only combinations I know ...
    > >
    > >
    > > The subform is called: FrmSiteSubform (which we opened)
    > > The subsubform is called: FrmSiteRosterOneSubform
    > > The control of the new record is: ContactFK
    > > --
    > > Sue Compelling
    > >
    > >
    > > "Daniel Pineault" wrote:
    > >
    > > > So you can use the DoCmd.OpenForm to launch your form and the 4th input
    > > > variable is a WHERE argument. So you would do something like:
    > > >
    > > > If siteID is a number:
    > > > DoCmd.OpenForm "FrmSiteSubform",acNormal, ,"[siteID]=" &
    > > > [Forms]![frmcontacts]![combosite], ,acDialog
    > > >
    > > > If siteID is a string:
    > > > DoCmd.OpenForm "FrmSiteSubform",acNormal, ,"[siteID]='" &
    > > > [Forms]![frmcontacts]![combosite] & "'", ,acDialog
    > > > --
    > > > Hope this helps,
    > > >
    > > > Daniel Pineault
    > > > http://www.cardaconsultants.com/
    > > > For Access Tips and Examples: http://www.devhut.net
    > > > Please rate this post using the vote buttons if it was helpful.
    > > >
    > > >
    > > >
    > > > "Sue Compelling" wrote:
    > > >
    > > > > Hi ALL
    > > > >
    > > > > I've used a macro to open my form "FrmSiteSubform" in dialogue mode using a
    > > > > where statement of ... [tblsites].[siteID]=[Forms]![frmcontacts]![combosite]
    > > > > ...
    > > > >
    > > > > It works well but I now want to copy and paste a field from my Contacts form
    > > > > to my SiteSubform - once it opens - so need to convert the above macro into
    > > > > VBA ...
    > > > >
    > > > > I've tried using answers in the forum though keep coming unstuck ... HELP
    > > > >
    > > > > TIA
    > > > > --
    > > > > Sue Compelling
     
    Sue Compelling, Oct 25, 2009
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Douglas J. Steele

    Re: Removing Filter Criteria from the Form's Filter Property

    Douglas J. Steele, Mar 12, 2004, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    448
    Guest
    Mar 15, 2004
  2. May via AccessMonster.com

    Open form with filter will not open to filtered recordset

    May via AccessMonster.com, Aug 3, 2006, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    378
    Rick Brandt
    Aug 3, 2006
  3. Guest

    Filter Filter Filter

    Guest, Nov 27, 2006, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    458
    Guest
    Nov 28, 2006
  4. Russ via AccessMonster.com

    Trying to use the filter on the form to filter report

    Russ via AccessMonster.com, Dec 12, 2006, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    157
    Rick Brandt
    Dec 12, 2006
  5. Rich_in_NZ

    Open form and filter form & subform using combo-boxes

    Rich_in_NZ, Dec 26, 2008, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    333
    John Smith
    Dec 30, 2008
Loading...

Share This Page