Using a Where Filter to Open a form

[Sue Compelling]

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

 

[Daniel Pineault]

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]

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

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.

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

 

[Daniel Pineault]

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.

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

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.

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]

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

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.

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

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.



:

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