Using a Where Filter to Open a form

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

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
 
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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top