Passing a field name as a parameter

[John F]

I have a form with a number of command buttons that perform calculations. The
calculations are the same with the difference being which field the
calculation is based on. I would like to call a subroutine using the name of
the button as a parameter so that I can have one routine and call it.

How do you pass a field name as a parameter to a called subroutine so that
it can use the correct field in the calculation?

 

[Marshall Barton]

I have a form with a number of command buttons that perform calculations. The
calculations are the same with the difference being which field the
calculation is based on. I would like to call a subroutine using the name of
the button as a parameter so that I can have one routine and call it.

How do you pass a field name as a parameter to a called subroutine so that
it can use the correct field in the calculation?

Not sure how the button name can "use the correct field in
the calculation", but your subroutine can reference the
button control by using Me.ActiveControl. The name of the
button would just be Me.ActiveControl.Name.

If you relly need the button's Click event to pass the
buttons name, then you can use:

Private mybutton_Click()
mysub "mybutton"
End Sub

If you have no other use for the button's Click event
procedure, then make your subroutine a function and call the
function in the button's OnClick property using
=yoursub("mybutton")

 

[John F]

Sorry not the name of the button but the name of some fields my goof. I guess
that I have buttons on the brain.

Perhaps this will illustrate:

Way it is now.

Private Sub IM1_Click()

Select Case [LVL]
Case 1 To 4
If ([M1] < 0) Then
[M1] = [M1] + 75
If ([PRA1] = 0) Then
[PRA1] = 236
[PRP1] = [PRC1] / [PRA1]
End If
End If
Additional Case statements

Private Sub IM2_Click()

Select Case [LVL]
Case 1 To 4
If ([M2] < 0) Then
[M2] = [M2] + 75
If ([PRA2] = 0) Then
[PRA2] = 236
[PRP2] = [PRC2] / [PRA2]
End If
End If
Additional Case statements

What I would like.

Private Sub IM1_Click()

Call FieldCalculate ( [M1], [PRA1], [PRP1], [PRC1] )
Need to be able to pass the name of the fields,
not the contents of the fields.
End Sub

Private Sub IM2_Click()

Call FieldCalculate ( [M2], [PRA2], [PRP2], [PRC2] )
Need to be able to pass the name of the fields,
not the contents of the fields.
End Sub

Private Sub FieldCalculate ( Var1, Var2, Var3, Var4 )

Select Case [LVL]
Case 1 To 4
If (Var1 < 0) Then
Var1 = Var1 + 75
If (Var2 = 0) Then
Var2 = 236
Var3 = Var4 / Var2
End If
End If
Additional Case statements

 

[Clifford Bass]

Hi John,

Have you tried your example? You can pass the fields themselves, just
as you show. I would modify the FieldCalculate parameter list to reflect the
types of the controls:

Private Sub FieldCalculate(Var1 As TextBox, Var2 As ComboBox, Var3 As
CheckBox, Var4 As Label)

Clifford Bass

 

[John F]

Doing my example seems to do nothing. The reference is updating fields in the
table based on contents in other fields.

Button 1 should make this calculation Field1 = Field1 + 75
Button 2 should make this calculation Field2 = Field2 + 75
Button 3 should make this calculation Field3 = Field3 + 75

But there are other fields involved in the decision to add 75 or some other
amount to the origional contents of the field.

Based on value of field "LVL" clicking button one does:
Select Case [LVL]
Case 1 To 4
If ([M1] < 0) Then "Field1" < 0
[M1] = [M1] + 75 "Field1" = "Field1" + 75
If ([PRA1] = 0) Then "Field2" = 0
[PRA1] = 236 "Field2" = 236
[PRP1] = [PRC1] / [PRA1] "Field3" = "Field4" / "Field2"
End If
End If

This is just the first Case of 8. They all deal with the same 4 fields but
in diferent ways, and this was button 1.
Button 2 does the exact same calculations but with 4 different fields.

 

[Marshall Barton]

Sorry not the name of the button but the name of some fields my goof. I guess
that I have buttons on the brain.

Perhaps this will illustrate:

Way it is now.

Private Sub IM1_Click()

Select Case [LVL]
Case 1 To 4
If ([M1] < 0) Then
[M1] = [M1] + 75
If ([PRA1] = 0) Then
[PRA1] = 236
[PRP1] = [PRC1] / [PRA1]
End If
End If
Additional Case statements

Private Sub IM2_Click()

Select Case [LVL]
Case 1 To 4
If ([M2] < 0) Then
[M2] = [M2] + 75
If ([PRA2] = 0) Then
[PRA2] = 236
[PRP2] = [PRC2] / [PRA2]
End If
End If
Additional Case statements

What I would like.

Private Sub IM1_Click()

Call FieldCalculate ( [M1], [PRA1], [PRP1], [PRC1] )
Need to be able to pass the name of the fields,
not the contents of the fields.
End Sub

Private Sub IM2_Click()

Call FieldCalculate ( [M2], [PRA2], [PRP2], [PRC2] )
Need to be able to pass the name of the fields,
not the contents of the fields.
End Sub

Private Sub FieldCalculate ( Var1, Var2, Var3, Var4 )

Select Case [LVL]
Case 1 To 4
If (Var1 < 0) Then
Var1 = Var1 + 75
If (Var2 = 0) Then
Var2 = 236
Var3 = Var4 / Var2
End If
End If
Additional Case statements

I think this is what you are looking for.

Private Sub IM1_Click()
FieldCalculate "M1", "PRA1", "PRP1", "PRC1"
End Sub

Private Sub FieldCalculate ( Var1, Var2, Var3, Var4 )

Select Case [LVL]
Case 1 To 4
If Me(Var1) < 0 Then
Me(Var1) = Me(Var1) + 75
If Me(Var2) = 0 Then
Me(Var2) = 236
Me(Var3) = Me(Var4) / Me(Var2)
End If
End If
. . .

 

[Clifford Bass]

Hi John,

This works, based on your original example and what I said about
passing the fields themselves. Note that it is good form when passing to a
subroutine to pass all the variable involved. It is also good form to use
meaningful names. So I include the LVL field and changed the names.

Private Sub Command30_Click()

FieldCalculate LVL, M1, PRA1, PRC1, PRP1

End Sub

Public Sub FieldCalculate(tboxLVL As TextBox, _
tboxM As TextBox, tboxPRA As TextBox, _
tboxPRC As TextBox, tboxPRP As TextBox)

Select Case [tboxLVL]
Case 1 To 4
If (tboxM < 0) Then
tboxM = tboxM + 75
If (tboxPRA = 0) Then
tboxPRA = 236
tboxPRP = tboxPRC / tboxPRA
End If
End If
End Select

End Sub

Clifford Bass

 

[John F]

Yes, thank you.

But this brings up another question.
I tried almost the exact thing using exactly the same subroutine call but
without using Me(Var1) in the called subroutine the but useing just Var1 and
it failed miserably. It gave me nothing but errors and grief.

How do you know when to use Me()?
Is there a book that can explain these subtleties?

The only help I have is the built in help file. Pardon my expression but
that file blows a howling wind followed by a huge smoke screen.
I look at it as help without the main subject the help itself.
Not to mention finding anything is a useless attempt unless you know exactly
what you are looking for, and phrase the query with exacting precision.
This to me makes the help file about as helpful as a one ton boulder (more
hindrance than help).

 

[John F]

Yes, thank you.

But this brings up another question.
I tried almost the exact thing using exactly the same subroutine call but
without using Me(Var1) in the called subroutine the but useing just Var1 and
it failed miserably. It gave me nothing but errors and grief.

How do you know when to use Me()?
Is there a book that can explain these subtleties?

The only help I have is the built in help file. Pardon my expression but
that file blows a howling wind followed by a huge smoke screen.
I look at it as help without the main subject the help itself.
Not to mention finding anything is a useless attempt unless you know exactly
what you are looking for, and phrase the query with exacting precision.
This to me makes the help file about as helpful as a one ton boulder (more
hindrance than help).

 

[John F]

Which would make all of the fields involvd:
longLVL standard format
longM1 standard format
longPRA1 standard format
longPRC1 standard format
dblPRP1 format percentage

I am aware of the meaningful nameing convention and I use it grudgingly in
the final product. This however is a proof of concept and didn't try for good
form but rather proofing the functionality which means at the end I use the
heck out of the search and replace functionality.

 

[Clifford Bass]

Hi John,

I also sometimes use poor names when trying something out. But not to
any great extent.

There are some other ways to do the thing. The one you yourself
suggested, as modified by me. Marshall's. Or even a hybrid. It mostly
depends on how you want to specify the varying fields. There is the
specification within the call to the function or the specification, for the
most part, within the function. Here is an example of the latter that could
be used if your field sets are sequentially numbered (as your original code
implies):

Private Sub IM1_Click()
FieldCalculate 1
End Sub

Private Sub FieldCalculate (intSet As Integer)

Select Case [longLVL]
Case 1 To 4
If Controls("longM" & intSet) < 0 Then
Controls("longM" & intSet) = Controls("longM" & intSet) + 75
If Controls("longPRA" & intSet) = 0 Then
Controls("longPRA" & intSet) = 236
Controls("dblPRP" & intSet) = Controls("longPRC" &
intSet) / _
Controls("longPRA" & intSet)
End If
End If
End Select

End Sub

You will notice that I am using the Controls collection of the form.
When Marshall used Me("xxxx"), there was an implied .Controls there because
the Controls collection is the default property of a form. Me of course
refers to the object the code is in. In this case a form. In my code the
Me. is implied. There is also an Item property of the Controls collection,
which again is the default property of the Controls collection. To access an
item in a collection, you can specify either an index number or the actual
name. Also, for a TextBox, there is a default property, which is Value. So
in fully qualified/explicit form it might look like:

Me.Controls.Item("longM1").Value

Most (all?) of this is in the help. However, as you stated, finding it
is problematic. I will say that after the fiasco with Access 2000's and I
think 2002's help, they improved with 2003 and again with 2007. After
initial training, a lot of this I have picked up over time by chance
encounter when trying to do something. So, if working with a form, I may
have been looking at the help for the form and noticed that the Controls
object was the default object. Just don't ask me to where find that
particular piece of information . And of course becoming involved in the
Access discussion groups has expanded my knowledge. I am sure there are
books and web sites that give the details in different formats and ways.

Hope this is helpful to you,

Clifford Bass

 

[Marshall Barton]

That syntax is inherent to the Access/VBA object model,
which is detailed in the VBA Help file. All the good stuff
is in the VBA Help file, while the Access Help file is
mostly about the point and click user interface.

You can address a member of a collection by using:
collection!membername
or
strvar = "membername"
collection(strvar)
or, in your case
Me(strvar)
The latter syntax takes advantage of the default collection
and property. In your case, the full reference would be:
Me.Controls(strvar)
or even more fully:
Me.Controls.Item(strvar)
but the Item method is almost never needed.

Your attempt with just the variable tried to set the
variable to the value of the expression. To set the value
of the object named in the variable requires that you go
through the Controls collection using the syntax of one of
the above examples.
--
Marsh
MVP [MS Access]

But this brings up another question.
I tried almost the exact thing using exactly the same subroutine call but
without using Me(Var1) in the called subroutine the but useing just Var1 and
it failed miserably. It gave me nothing but errors and grief.

How do you know when to use Me()?
Is there a book that can explain these subtleties?

The only help I have is the built in help file. Pardon my expression but
that file blows a howling wind followed by a huge smoke screen.
I look at it as help without the main subject the help itself.
Not to mention finding anything is a useless attempt unless you know exactly
what you are looking for, and phrase the query with exacting precision.
This to me makes the help file about as helpful as a one ton boulder (more
hindrance than help).

I think this is what you are looking for.

Private Sub IM1_Click()
FieldCalculate "M1", "PRA1", "PRP1", "PRC1"
End Sub

Private Sub FieldCalculate ( Var1, Var2, Var3, Var4 )

Select Case [LVL]
Case 1 To 4
If Me(Var1) < 0 Then
Me(Var1) = Me(Var1) + 75
If Me(Var2) = 0 Then
Me(Var2) = 236
Me(Var3) = Me(Var4) / Me(Var2)
End If
End If
. . .

 

[John F]

Just one final comment or question.

When it comes to the layout of the help file, how do you go about finding
something pertinent to the problem when you don't really know what you are
looking for? Oh you may have an good idea about what you need but like I said
until you stumble onto the exact phrasing you simply look and look and never
find. There has to be a better and easier way, somewhere, somehow. I say this
because I have spent several hours sometimes days looking and then finally
find it.

Once you do finally find what you are looking for the answer is very
helpful, but it’s the finding it that is a royal pain!

 

[John F]

That is a rather interesting way of doing business, one that I probably would
never have thought of let alone found.

To restate what I have already said, how do you find something that you
don't even know about? A saying goes "One does not know what they don't
know." especially when the finding process is extremely precise.

Hi John,

I also sometimes use poor names when trying something out. But not to
any great extent.

There are some other ways to do the thing. The one you yourself
suggested, as modified by me. Marshall's. Or even a hybrid. It mostly
depends on how you want to specify the varying fields. There is the
specification within the call to the function or the specification, for the
most part, within the function. Here is an example of the latter that could
be used if your field sets are sequentially numbered (as your original code
implies):

Private Sub IM1_Click()
FieldCalculate 1
End Sub

Private Sub FieldCalculate (intSet As Integer)

Select Case [longLVL]
Case 1 To 4
If Controls("longM" & intSet) < 0 Then
Controls("longM" & intSet) = Controls("longM" & intSet) + 75
If Controls("longPRA" & intSet) = 0 Then
Controls("longPRA" & intSet) = 236
Controls("dblPRP" & intSet) = Controls("longPRC" &
intSet) / _
Controls("longPRA" & intSet)
End If
End If
End Select

End Sub

You will notice that I am using the Controls collection of the form.
When Marshall used Me("xxxx"), there was an implied .Controls there because
the Controls collection is the default property of a form. Me of course
refers to the object the code is in. In this case a form. In my code the
Me. is implied. There is also an Item property of the Controls collection,
which again is the default property of the Controls collection. To access an
item in a collection, you can specify either an index number or the actual
name. Also, for a TextBox, there is a default property, which is Value. So
in fully qualified/explicit form it might look like:

Me.Controls.Item("longM1").Value

Most (all?) of this is in the help. However, as you stated, finding it
is problematic. I will say that after the fiasco with Access 2000's and I
think 2002's help, they improved with 2003 and again with 2007. After
initial training, a lot of this I have picked up over time by chance
encounter when trying to do something. So, if working with a form, I may
have been looking at the help for the form and noticed that the Controls
object was the default object. Just don't ask me to where find that
particular piece of information . And of course becoming involved in the
Access discussion groups has expanded my knowledge. I am sure there are
books and web sites that give the details in different formats and ways.

Hope this is helpful to you,

Clifford Bass

Which would make all of the fields involvd:
longLVL standard format
longM1 standard format
longPRA1 standard format
longPRC1 standard format
dblPRP1 format percentage

I am aware of the meaningful nameing convention and I use it grudgingly in
the final product. This however is a proof of concept and didn't try for good
form but rather proofing the functionality which means at the end I use the
heck out of the search and replace functionality.

 

[Clifford Bass]

Hi John,

I often start by by looking at the list of methods and properties of
the type of object that I am trying to work with. So, if a form, in code I
may write a test subroutine and the type in "Me.". It will then give the
list of things you can do. If any look likely I would choose it, then click
the mouse inside of the method/property word, then press F1 to get help on
it. If I do not find it there, I might try "DoCmd." to see if there is
something more general. Or "Access." or "Application." or "DoCmd.RunCommand
" or "VBA.". In the table on contents it is often helpful to go to "Visual
Basic for Applications Language Reference" and then "Visual Basic Language
Reference". The items listed there will give you a number of other lists of
actions, properties and whatnot. Of course, the online help is not the only
source of information. Sometimes it helps to do a general Internet search
for the plain speach text of what I am trying to do. Someone else may have
documented it and once I find what someone else has done, say the function,
property, method, whatnot that they have done/used, then I can go back to the
help for specifics. Another useful thing is pressing F2 while in the VBA
editor. You can then search for a likely word. Or just browse through a
comprehensive list of Access objects and their members. Again, if you find
something that might apply you can click on it and then on the question mark
icon. It will take you to help on that item.

Hope that helps, at least somewhat,

Clifford Bass

 

[Marshall Barton]

Just one final comment or question.

When it comes to the layout of the help file, how do you go about finding
something pertinent to the problem when you don't really know what you are
looking for? Oh you may have an good idea about what you need but like I said
until you stumble onto the exact phrasing you simply look and look and never
find. There has to be a better and easier way, somewhere, somehow. I say this
because I have spent several hours sometimes days looking and then finally
find it.

Once you do finally find what you are looking for the answer is very
helpful, but it’s the finding it that is a royal pain!

The same issues occur with a book. You guess at what to
search for in the index, scan the table of contents, and
just read everything that looks interesting/related. The
Help files actually have an advantage over books because the
searching is very fast and you can hit F1 and/or use
intellisense. There is a lot to learn and it is not an
instantaneous process.