PC Review


Reply
Thread Tools Rate Thread

Using a Where Filter to Open a form

 
 
Sue Compelling
Guest
Posts: n/a
 
      24th Oct 2009
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
 
Reply With Quote
 
 
 
 
Daniel Pineault
Guest
Posts: n/a
 
      24th Oct 2009
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

 
Reply With Quote
 
 
 
 
Sue Compelling
Guest
Posts: n/a
 
      24th Oct 2009
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

 
Reply With Quote
 
Daniel Pineault
Guest
Posts: n/a
 
      25th Oct 2009
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

 
Reply With Quote
 
Sue Compelling
Guest
Posts: n/a
 
      25th Oct 2009
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter a Report Using a Form's Filter amr Microsoft Access Reports 3 11th Jan 2010 12:58 AM
Open form and filter form & subform using combo-boxes Rich_in_NZ Microsoft Access Form Coding 2 30th Dec 2008 06:39 PM
Using Macro to Open Filter by form, clear grid, then run filter Victoria@DIG Microsoft Access Macros 0 20th Apr 2008 12:46 AM
Why does unlinked sub form lose filter when main form filter is turned off?? jgeniti Microsoft Access Forms 4 29th Jan 2007 05:13 PM
Filter Filter Filter =?Utf-8?B?YW1zdXJpYQ==?= Microsoft Access Form Coding 5 28th Nov 2006 11:37 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:55 AM.