same event on lots of fields

[jreynold]

I have a continuous form [ProjectList] with 16 different fields on it.

I would like to be able to double click on any of the fields and open a tabbed single form to a particular tab. I have code to do that below.

What I am wondering is what is the best way to repeat the same event for lots of different controls? I can certainly make it work by just repeating the code for each control_event.

I have the majority of the code in another sub "OpenFormToPage()" that I call in my "AmountDue_DblClick" sub.


Here is the entirety (well relevent code) of my forms code. Note: this is only for double clicking on "AmountDue".

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Option Compare Database
Dim btPageToOpenTo As Byte
Dim stFormtoOpen As String

'-----------------------------------------------------------
Private Sub AmountDue_DblClick(Cancel As Integer)

stFormtoOpen = "Projects"
btPageToOpenTo = 4
OpenFormToPage

End Sub
'-----------------------------------------------------------
Private Sub OpenFormToPage()
On Error GoTo Err_OpenFormToPage
Dim stLinkCriteria As String
stLinkCriteria = "[ProjectName]=" & "'" & Me![ProjectName] & "'"
DoCmd.OpenForm stFormtoOpen, , , stLinkCriteria
Forms(stFormtoOpen).TabCtl0.Pages(btPageToOpenTo).SetFocus

Exit_OpenFormToPage:
Exit Sub

Err_OpenFormToPage:
MsgBox Err.Description
Resume Exit_OpenFormToPage

End Sub
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

[mhartman1]

Hello:

What you want to do is loop through the controls on your subform using the Controls Collection:

Search control collection in the help

Regards
Mark

 

[Remou]

Do you always wish to open the same form but to different pages?

 

[jreynold]

Mark - thanks, i'm going to look into that right now.

Remou - In my particular form I have 16 controls, 5 go to one page, 5 go to another page and 6 go to another page.

 

[Remou]

You could add the page number to the tag of the controls and use Screen.ActiveControl.Tag, if all events are to open the same form, you could then simply update the event line of the controls to reference the function:

=OpenPage()

If you need different forms, you could include the form name:

=OpenToPage("FormToOpenName")


Then

Function OpenToPage(FormToOpen)
'Must be a function
On Error GoTo Err_OpenFormToPage
    Dim stLinkCriteria As String
    stLinkCriteria = "[ProjectName]=" & "'" & Me![ProjectName] & "'"
    DoCmd.OpenForm stFormtoOpen, , , stLinkCriteria
    Forms(stFormtoOpen).TabCtl0.Pages(screen.activecontrol.tag).SetFocus

It is easy enough to update the event line in design view to set up the controls, so it would not be necessary to manually edit them.

Alternatively, you could simply use the same click event and select case with the activecontrol.name.

 

[TheAceMan1]

How are ya jreynold . . .

Make [blue]OpenToPage[/blue] a bit more of a common routine (you can get rid of the global variables):

[blue]Private Sub OpenFormToPage(frmName As String, TabPage As Integer)
   Dim Cri As String
   
   Cri = "[ProjectName] = '" & Me!ProjectName & "'"
   DoCmd.OpenForm frmName, , , Cri
   DoEvents [green]'allow a small amount of time for the form to open.[/green]
   Forms(frmName).TabCtl0.Pages(TabPage).SetFocus

End Sub[/blue]

In the event(s) of your choosing, a call to the routine would look like:

[blue]   Call OpenFormToPage("Projects", 4)[/blue]

[blue]Your Thought? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886