Reduce repetive code on multiple forms (1 Viewer)

TimTDP · Apr 4, 2019

On a form I have the following code

Code:

Private Sub ShowCustomerContactDetailsForm()

Forms![frmCustomer]![frmCustomerContacts].Form!lngCustomerContactId = Forms![frmCustomer]![frmCustomerContacts].Form!lstCustomerContacts.ItemData(0)
Forms![frmCustomer]![frmCustomerContacts].Form!lstCustomerContacts.Selected(0) = True
Forms![frmCustomer]![frmCustomerContacts].Form!frmCustomerContactDetails.Visible = True
Forms![frmCustomer]![frmCustomerContacts].Form!frmCustomerContactDetails.Requery

End Sub

I need to repeat the same code on another form, so I want to make the private sub routine public in a module
In the public version the following will change:
Forms![frmCustomer]![frmCustomerContacts]
Forms![frmCustomer]![frmCustomerContacts].Form!frmCustomerContactDetails
Name of list box. In above code it is "lstCustomerContacts"

How do I do this?

Thanks in advance

MajP · Apr 4, 2019

Code:

Private Sub ShowCustomerContactDetailsForm(frm as access.form)
  frm.lngCustomerContactId = frm.lstCustomerContacts.ItemData(0)
  frm.lstCustomerContacts.Selected(0) = True
  with frmCustomerContactDetails
    .Visible = True
    .Requery
  end with
End Sub

call ShowCustomerContactDetailsForm(Forms![frmCustomer]![frmCustomerContacts].Form)

TimTDP · Apr 4, 2019

Thanks very much

How do I pass the name of the list box "lstCustomerContacts" and field "lngCustomerContactId"

These will change for each form

Thanks

MajP · Apr 4, 2019

Can you describe the setup? From where are you calling this code? In your original example is the subform
Forms![frmCustomer]![frmCustomerContacts].Form
on the same form where the code is called?

Can you describe the different subforms on the different forms? I guess I am confused why these subforms on different forms have similar code but different objects.

One thing. If you are calling code from a form you can reference it directly. So if the frm is form customer then you can replace
Forms![frmCustomer]![frmCustomerContacts].Form
with
me.FrmCustomerContacts
(FYI you do not need [] if your names do not have spaces)
Also if you want to continue to refer to a subform on that form (and you are calling the code from the main form) then you can do something like

Code:

dim subFrm as access.form
set subFrm = me.frmCustomerContacts.Form
'now instead of repeating the long reference you can
subFrm.txtOne = "A'
subFrm.someProperty = somevalue

May not help what you are doing but can shorten code by setting a variable.

It really depends on where you are calling the code to give a good answer.

moke123 · Apr 5, 2019

In addition to MajP's advice, You can also pass a listbox object as well as a form object.

Code:

public sub SomeSub(lbx as listbox, frm as form)

lbx = lbx.ItemData(0)
...
frm.requery
....

from your form you would call it like

Code:

Call SomeSub(me.MyListBoxName, Me)

passing the object you don't need the form references.

Galaxiom · Apr 5, 2019

moke123 said:
passing the object you don't need the form references.

Yes. To address the form that holds the passed listbox, refer to it in the procedure as:

Code:

lbx.Parent

Code:
Code:

Call SomeSub(me.MyListBoxName, Me)

The unnecessary brackets around the parameter list cause it to be treated as a function. A procedure only needs the Call keyword when it is addressed as a function.

Instead you can run the sub using:

Code:

SomeSub Me.MyListBoxName, Me

MajP · Apr 5, 2019

The unnecessary brackets around the parameter list cause it to be treated as a function. A procedure only needs the Call keyword when it is addressed as a function

Sorry that is not correct statement, and does not make any sense. A subroutine can be called in two ways
call SomeSub(parameter1, parameter2)
or
SomSub parameter1, parameter2

a function can be called the same way if you do not do anything with the return
call Msgbox("some Message")
or
Msgbox "Some Message"

If returning a value from a function need the ()
rtn = Msgbox("some message",vbType)

Galaxiom · Apr 5, 2019

MajP said:
Sorry that is not correct statement, and does not make any sense. A subroutine can be called in two ways
call SomeSub(parameter1, parameter2)
or
SomSub parameter1, parameter2

a function can be called the same way if you do not do anything with the return
call Msgbox("some Message")
or
Msgbox "Some Message"

Yes, exactly as I said. By using the brackets around the parameter list you have effectively caused the sub to be treated as function.

The similarities run very deep. A function will also be treated exactly as a sub if used like a sub without the brackets or return value equation.

One might claim that Subs and Functions are virtually identical with two different ways to call them. However the use of functions (and only functions) directly in objects' Events reveals the syntax proscribed for a function.

It is usually best sticking to treating Subs as Subs and Functions as Functions. Otherwise it can easily become very confusing when passing objects to a sub where the presence of brackets around the parameter causes it to be evaluated under some conditions as was discussed in this thread many years ago.

There was another discussion about the confusion on this thread.

And a summary on this one at the other site.

sonic8 · Apr 5, 2019

Galaxiom said:
... the presence of brackets around the parameter causes it to be evaluated under some conditions as was discussed in this thread many years ago.

Brackets around an argument cause it to be evaluated as an expression under all circumstances!
The confusion is mainly about when are the brackets around the single argument itself, when are they around the list of arguments, and when does this make a real difference in the result.

Galaxiom · Apr 5, 2019

sonic8 said:
Brackets around an argument cause it to be evaluated as an expression under all circumstances.

Not quite.

Brackets will not cause Forms!formname.controlname to be evaluated if the parameter is typed as an object or Variant.

Brackets will cause Forms!formname!controlname to be evaluated regardless of the parameter type and cause an error if an object is expected to be passed.

sonic8 · Apr 5, 2019

Galaxiom said:
Brackets will not cause Forms!formname.controlname to be evaluated if the parameter is typed as an object or Variant.

Brackets will cause Forms!formname!controlname to be evaluated regardless of the parameter type and cause an error if an object is expected to be passed.

Both, Forms!formname.controlname and Forms!formname!controlname are expressions on their own. - They will be always evaluated regardless of brackets.

The difference between them is...

The first expression returns an object of type Control (or a derived subtype) whose default method will only be invoked if required to match a certain (variable- or argument-) type.

The type of the second expression's return value is determined only at runtime and its evaluation may or may not include invoking the default method of whatever is returned by the original expression. - This final step may indeed be influenced by the brackets.

MajP · Apr 5, 2019

@ Glaxiom,
You are confusing what bracketing does with the Call keyword. If the call keyword is used there are rules for bracketing. That is different than using bracketing without the call keyword. You are confusing byval and byref as a result of bracketing, which is not a result of the call keyword. The call keyword is a leftover from early basic where every line started with a key word. Let, Call,... However, it does one thing if used and that is discard a return value

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist. If you use either Call syntax to call any intrinsic or user-defined function, the function's return value is discarded

To understand what bracketing does explains why a object is or is not evaluated. An object can only be passed by ref so it you tell it to pass by val then it will try to evaluate the form reference.

1) An argument list for a function call with an assignment to the returned value must be surrounded by parens: Result = MyFunc(MyArg)
2) An argument list for a subroutine call (or a function call with no assignment) that uses the Call keyword must be surrounded by parens: Call MySub(MyArg)
3) If 1 and 2 do not apply then the list must NOT be surrounded by parens.

And finally there is the byref rule: arguments are passed byref when possible but if there are “extra” parens around a variable then the variable is passed byval, not byref.

MySub(MyArg) is legal but MyOtherSub(MyArg1, MyArg2) is not. The first case appears to be a subroutine call with parens around the argument list, but that would violate rule 3. Then why is it legal? In fact it is a subroutine call with no parens around the arg list, but parens around the first argument! This passes the argument by value. The second case is a clear violation of rule 3.3, and there is no way to make it legal, so we give an error.

Suppose x and y are vars, f is a one-arg procedure and g is a two-arg procedure.

to pass x byref, y byref:
f x
call f(x)
z = f(x)
g x, y
call g(x, y)
z = g(x, y)

to pass x byval, y byref:
f(x)
call f((x))
z = f((x))
g (x), y
g ((x)), y
call g((x), y)
z = g((x), y)

The following are syntax errors:
call f x
z = f x
g(x, y)
call g x, y
z = g x, y

Uncle Gizmo · Apr 5, 2019

MajP said:
The call keyword is a leftover from early basic where every line started with a key word. Let, Call,..

I quite like calling my functions with Call .. it just looks tidier to me.

At one stage in my early access career I used to precede everything with "Let" where possible.

I was advised that the word Let may well be removed from VBA at some stage in the future and I was ill advised to continue using it. So I stopped using it, and then when I became more experienced I discovered that private properties actually have to have the "Let" keyword in them!

So I'm thinking the advice might not have been good advice, but just coercion to get me to tow the line! I'm actually against towing the line! I like to experiment and do things in as many different ways as possible.

I have used let occasionally recently, but I'm just not comfortable with it now.

Sent from my SM-G925F using Tapatalk

sonic8 · Apr 5, 2019

MajP said:
To understand what bracketing does explains why a object is or is not evaluated. An object can only be passed by ref so it you tell it to pass by val then it will try to evaluate the form reference.

An object variable can very well be passed ByVal. However, this will only replace the original variable pointing to the actual object with a copy of that variable pointing, once again, to the very same object. - An object is an reference type and will continue to be even if you pass the variable representing the object ByVal.
You can, however, set the object variable inside a ByVal procedure to a another object without affecting the original object variable outside the procedure.

Galaxiom · Apr 6, 2019

MajP said:
And finally there is the byref rule: arguments are passed byref when possible but if there are “extra” parens around a variable then the variable is passed byval, not byref.

All sounds like a hangover from the days when variables were typed by naming patterns and parameters were not declared as ByRef of ByVal.

Better to be explicit about it when declaring the parameters. The sub of funtion "knows" how it intends to handle the arguments. Leaving it to the call to define ByVal of ByRef sounds like preparation for making problems.

Can't really see the point of Call-ing a sub just so that extra brackets need to be typed as well.

The_Doc_Man · Apr 6, 2019

The ONLY time I have ever seen a case where the CALL was required syntax was on a mainframe version of BASIC (OpenVMS version) where a routine entry point was a valid object type. There, you HAD to use a CALL for that one case. I don't recall any syntax for Office VBA that supports such a declaration and no other case that I recall would require use of CALL as opposed to simply using the name of the subroutine as the first thing on the line.

MajP · Apr 7, 2019

An object variable can very well be passed ByVal. However, this will only replace the original variable pointing to the actual object with a copy of that variable pointing, once again, to the very same object. - An object is an reference type and will continue to be even if you pass the variable representing the object ByVal.
You can, however, set the object variable inside a ByVal procedure to a another object without affecting the original object variable outside the procedure.

You are missing my point. Of course you can pass an object, and receive it byval and make a copy of it. You cannot pass it by val through bracketing. Simple test to understand what goes on

Code:

Private Sub cmdObj_Click()
 PassForm Me  'Passed byref
 PassForm (Me) 'passed byval
End Sub

Code:

Public Sub PassForm(frm As Object)
  'Frm is default by ref
  MsgBox TypeName(frm)
End Sub

The first case it simply passes the form obect and returns the form class. In the second case it tries to resolve it to pass byval, but since it can't it, returns the default property which is the controls collection of the form. This is very different than

Code:

Public Sub PassFormByVal(ByVal frm As Object)
  MsgBox TypeName(frm)
End Sub

In the above case it is sent byref and recieved byval. The msgbox is a reference (copy) to the Form's class not the controls collection.

This final step may indeed be influenced by the brackets.

Yes, that is exactly what is happening, and nothing to do with the Call keyword.

sonic8 · Apr 7, 2019

MajP said:
You are missing my point. Of course you can pass an object, and receive it byval and make a copy of it. You cannot pass it by val through bracketing.

I'm not entirely sure what your point is, actually.

Bracketing an argument is not really meant to transform the argument into an ByVal argument. That is merely a side effect of the brackets indicating an expression that should be evaluated. - That's what I intended to point out in the first place.

The expression is evaluated, its return value is stored in an implicitly allocated buffer, and that buffer is passed into the invoked procedure instead of the original variable. - So, the effect is the same as when declaring the argument ByVal, however, the internal mechanisms are different.

Objects themselves cannot be evaluated as an expression. The next best thing is to return their default property on evaluation. If the object has no default property an error will occur.

MajP · Apr 7, 2019

Bracketing an argument is not really meant to transform the argument into an ByVal argument. That is merely a side effect of the brackets indicating an expression that should be evaluated. - That's what I intended to point out in the first place.

Maybe we are saying the same thing. All I know if you put brackets around a value type it gets passed by val. You put brackets around a ref type and it tries to evaluate it first. Only if done without a Call or if returning the value. So there is a big difference between

SomeProc (x),

'gets passed by val
Call SomeProc ((x),

) 'gets passed by val

and
SomeProc x, y 'by ref
Call SomeProc (x,Y) ' normal notation by ref

Code:

Public Sub TestIt()
  Dim x As Integer
  Dim y As Integer
  x = 1
  y = 2
  SomeProc (x), (y)
  Debug.Print x & " " & y
  SomeProc x, y
  Debug.Print x & " " & y
End Sub

Public Sub SomeProc(x As Integer, y As Integer)
  x = x + 10
  y = y + 20
  x = x + y
End Sub

SomeProc (X),(Y) ' Pass by val
Call SomeProc ((x),(Y)) ' pass by val
Call SomeProc (x,y) ' Normal notation not an extra brackets by ref

Reduce repetive code on multiple forms (1 Viewer)

Registered User.

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

Registered User.

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

AWF VIP

Super Moderator

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

Super Moderator

AWF VIP

Super Moderator

AWF VIP

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

Nifty Access Guy

AWF VIP

Super Moderator

Immoderate Moderator

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

AWF VIP

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

Similar threads

Users who are viewing this thread