Is there a cleaner way to repeat this code? (1 Viewer)

Bloodlvst · Dec 4, 2018

I have a split form where if the user double clicks on any field in a particular record row, it opens a form which lets them edit the record.

The below is my VBA. Is there any way to just write the code once and have it be shared by multiple control_DblClick events?

Code:

Private Sub FirstName_DblClick(Cancel As Integer)
    DoCmd.OpenForm "CustomerDetail", acNormal, , "[CustomerID]=" & Me.CustomerID, , acDialog
End Sub
Private Sub LastName_DblClick(Cancel As Integer)
    DoCmd.OpenForm "CustomerDetail", acNormal, , "[CustomerID]=" & Me.CustomerID, , acDialog
End Sub
Private Sub Email_DblClick(Cancel As Integer)
    DoCmd.OpenForm "CustomerDetail", acNormal, , "[CustomerID]=" & Me.CustomerID, , acDialog
End Sub
Private Sub PhoneNumber_DblClick(Cancel As Integer)
    DoCmd.OpenForm "CustomerDetail", acNormal, , "[CustomerID]=" & Me.CustomerID, , acDialog
End Sub
Private Sub CustomerType_DblClick(Cancel As Integer)
    DoCmd.OpenForm "CustomerDetail", acNormal, , "[CustomerID]=" & Me.CustomerID, , acDialog
End Sub

Eljefegeneo · Dec 4, 2018

Just put it in a Sub and then call it. I usually do this as the first event for the form so I know where to look for subs that I can call.

Sub OpenMyForm()
DoCmd.OpenForm "CustomerDetail", acNormal, , "[CustomerID]=" & Me.CustomerID, , acDialog
End Sub

Then call it on any of the events

OpenMyForm

Bloodlvst · Dec 4, 2018

Not quite what I'm looking for since I'm still repeating code, but definitely much more friendly to read, so thanks!

Galaxiom · Dec 4, 2018

Convert the sub to a function.

Code:

Function OpenMyForm(ByVal Customer As Long )
DoCmd.OpenForm "CustomerDetail", acNormal, , "[CustomerID]=" Customer, , acDialog
End Function

Call the function directly from the DoubleClick Event Property of each control.

Code:

= OpenMyForm([CustomerID])

Select all the control where you want it to run and type the call. It will apply to all of the controls.

mresann · Dec 4, 2018

Note: The original answer is "no." Each control must be programmed with code to call the function, or process the statement, on its own DoubleClick event. As other posters have mentioned, the code itself can be simplified and accessed through its own subroutine, but each control has to be assigned as their own instance.

theDBguy · Dec 4, 2018

Hi,

Pardon me for jumping in but are we talking about a single view form or a continuous one or a datasheet? Just curious...

MajP · Dec 4, 2018

The original answer is "no."

No, the original answer is yes as Galaxiom has demonstrated. You can write a single function that serves as an event handler for multiple events. You do not have to write individual event procedures, in fact you only have to write a single function which serves as the event handler.

Micron · Dec 4, 2018

Consider "grabbing" all necessary controls that allow the appropriate event, such as double click, in form design view. Enter =FormOpen("frmMyform") in the event property, where frmMyForm is the name of the form you want to open. In a standard module,

Code:

Function(frmName As String)
 DoCmd.OpenForm frmName
End Function

All 5 of your controls will call this one function and open the specified form.

Bloodlvst · Dec 4, 2018

theDBguy said:
Hi,

Pardon me for jumping in but are we talking about a single view form or a continuous one or a datasheet? Just curious...

It is split form (so form and datasheet), if that helps you.

Everyone has been helpful, and it looks like no matter what I still need to call the double-click event for every desired control. At least I know now

Micron · Dec 4, 2018

I guess it depends on how one interprets that statement but I venture to say "at least not the way you originally wrote it". Calling one function seems to fulfill the original request.

Is there a cleaner way to repeat this code? (1 Viewer)

Bloodlvst

Registered User.

Eljefegeneo

Still trying to learn

Bloodlvst

Registered User.

Galaxiom

Super Moderator

mresann

Registered User.

theDBguy

I’m here to help

MajP

You've got your good things, and you've got mine.

Micron

AWF VIP

Bloodlvst

Registered User.

Micron

AWF VIP

Similar threads

Users who are viewing this thread