Calling a Public Function

[tobesurveyor via AccessMonster.com]

Good evening all,

I have the following function and sub but keep getting an error message.
Basically I have several subs all going to at one point call this public
function to get a value. I can not for the life of me figure out how to pass
the variable from the public function back into the sub. Here is the code:

Public Function HilightColor(strln As String)
ColorNameID = DLookup("[ChoiceMenuColorID]", "tblLocalSettings", "
[LocalSettingsID] = 1")
strln = DLookup("[HiliteColor]", "tblChoiceMenuColor", "
[ChoiceMenuColorID] = " & ColorNameID)

HilightColor = strln
End Function

Private Sub Launch01()
Me.bxLaunch01.BackStyle = 1
Me.bxLaunch01.BackColor = HilightColor
End Sub

Any help figuring out the error would be greatly appreciated. I know it's
simple but I am new to VBA coding using functions.
Thanks,
Chris

 

[Douglas J. Steele]

You've declared strIn as a parameter to the function. That means that you
must pass it a string when you're calling it.

However, you don't use the value of strIn anywhere in the function, which
makes me think your function should be:

Public Function HilightColor() As Long
Dim ColorNameID As Long
Dim strIn As Long

ColorNameID = DLookup("[ChoiceMenuColorID]", "tblLocalSettings",
"[LocalSettingsID] = 1")
strln = DLookup("[HiliteColor]", "tblChoiceMenuColor",
"[ChoiceMenuColorID] = " & ColorNameID)

HilightColor = strln

End Function

This assumes that [ChoiceMenuColorID] and [HiliteColor] are both numeric
fields.

 

[Baz]

Good evening all,

I have the following function and sub but keep getting an error message.
Basically I have several subs all going to at one point call this public
function to get a value. I can not for the life of me figure out how to pass
the variable from the public function back into the sub. Here is the code:

Public Function HilightColor(strln As String)
ColorNameID = DLookup("[ChoiceMenuColorID]", "tblLocalSettings", "
[LocalSettingsID] = 1")
strln = DLookup("[HiliteColor]", "tblChoiceMenuColor", "
[ChoiceMenuColorID] = " & ColorNameID)

HilightColor = strln
End Function

Private Sub Launch01()
Me.bxLaunch01.BackStyle = 1
Me.bxLaunch01.BackColor = HilightColor
End Sub

Any help figuring out the error would be greatly appreciated. I know it's
simple but I am new to VBA coding using functions.
Thanks,
Chris

Public Function HilightColor() As String

Dim strln As String

ColorNameID = DLookup("[ChoiceMenuColorID]", "tblLocalSettings", "
[LocalSettingsID] = 1")
strln = DLookup("[HiliteColor]", "tblChoiceMenuColor", "
[ChoiceMenuColorID] = " & ColorNameID)

HilightColor = strln
End Function

 

[Graham Mandeno]

Hi Chris

There are a few problems here.

1. BackColor is a numeric property, not a string.

2. You have declared your function with a non-optional argument:
Public Function HilightColor(strln As String)
but you are not passing an argument value when you call it:
Me.bxLaunch01.BackColor = HilightColor

Is your [HiliteColor] field a number or text? It should be a number. Then
you can say:

Public Function HilightColor() As Long
Dim ColorNameID as Long 'always declare ALL variables!
ColorNameID = DLookup("[ChoiceMenuColorID]", "tblLocalSettings", _
"[LocalSettingsID] = 1")
HilightColor = DLookup("[HiliteColor]", "tblChoiceMenuColor", _
"[ChoiceMenuColorID] = " & ColorNameID)
End Function

Even better, create a query, qryHilightColor, with the following SQL text:

Select [HiliteColor] from tblChoiceMenuColor inner join
tblLocalSettings on (tblChoiceMenuColor.ChoiceMenuColorID=
tblLocalSettings.ChoiceMenuColorID) where LocalSettingsID = 1

Then you can dispense with the HilightColor function altogether:

Me.bxLaunch01.BackColor = DLookup("HiliteColor", "qryHilightColor")