pass strings to a function

[Souris]

I need pass a couple of string to my fucntion.
Are there any variable type to hold a couple of string?
Is varaible length array my best choice or any better one to hold unknown
number of elements of string to pass my function?


Your information is great appreicated,

 

[Baz]

Other options are:

- Pass in a delimited string and use the Split function to extract it into
an array
- Use a collection

 

[Souris]

Thanks for the message,
It seems that I have to remove after I add the items in to my collection,
because it needs to use New to create new collection which locate memory.

According to the example for VBA help file, should I create a new class for
every collection or I just can pass collection for the function?

Thanks again,

 

[Baz]

I'm sorry, I'm not sure I understand your question. Whatever technique you
use to structure your data is going to use memory, that's what memory is
for. Exactly when you destroy the collection to release the memory is
entirely dependent upon the design of your application, but the same is true
of an array or anything else.

You don't need any classes to create a collection of strings. All you need,
as a minimum, is something like this:

Dim col As New Collection

col.Add "A String"
col.Add "Another String"
ShowCollection col

Function ShowCollection(col As Collection)

MsgBox col.Item(1)
MsgBox col.Item(2)

End Function

 

[Souris]

I have following code to create my collection, but I got compile error that
augument required.

The function does not need augument. I am not sure which augument VB is
looking for.

Your information is great appreciated,

Dim MyValuesClause As New Collection

MyValuesClause = GetMyValuesClause

Private Function GetMyValuesClause() As Collection

Dim MyClause As New Collection
Dim i As Integer

MyClause.Add Item:=cmbEmployeeName.Text
MyClause.Add Item:=cldActivity.Value
MyClause.Add Item:=cmbActivityCode.Text
MyClause.Add Item:=textComments.Text
MyClause.Add Item:=chkTransfer.Value
'MyClause.Add Item:="Approved_By"
MyClause.Add Item:=DLookup("[EmployeeID]",
"SP_GET_CURRENT_EMPLOYEE_BY_WINDOWS_ID", "[Windows_AD_ID] = " & """" &
fOSUserName() & """")

GetMyValuesClause = MyClause

Call CleanMyCollection(MyCaluse)

End Function

 

[Albert D. Kallal]

A few things:

You are using .value and.text intermixed in your code (at least try and be
consistent in your coding practices, and even if the code is wrong, I would
much prefer that you did one thing always the same way).

In access the VBA code, you will very rarely use .text. The text is only a
valid property of control when the control has focus, and thus is of little
use in most of your daily code that you'll use.

You often see some code in this newsgroups where people are trying to read
or modify the value of a control and you'll see zillions and zillions of set
focus commands because you can not read or modify the value of a controlled
when you use the .text property *unless* it has the focus.

You would thus have to go for each time you use .text:

docmd.SetFocus cmbEmployeeName
MyClause.Add Item:=cmbEmployeeName.Text

In a nutshell using the.text property of a control is extremely messy, and
you don't need to use it on all. I suspect there is some confusing here
because in VB6 all developers use the .text property for all of their code
(that is all they have, and it's not dependent on the control having focused
to use it in visual basic)

In MS access we must use.value. So often the reason for this confusion is
many developers coming from a visual basic background instinctively and
naturally been using the text property of a control for many years.

In a nutshell using.text is extremely messy, and you don't need to use it
all. (use the .value property).

I fact in most cases you don't even have to use the .value property and can
just use the name of the control.

eg

msgbox "last name contorl is = " & LastName

Ironically one exception or one area that deserves an extra bit of caution
is when you're adding controls to a collection. You most surely want to be
very careful and distinguished between the.value property, and that of
actually adding the actual control to the collection.

The following data control can referenced on a form and display the value

msgbox "LastName value is = " & me.LastName

if you go:

MyColleciton.Add me.LastName

You'll actually not add the value of lastname to the collection, but you'll
actually add the actual control on the form or reference to the control on
the formed to your collection. That is certainly a considerable difference
in the outcome. In fact after you add that control to the collection then
you could actually use the.value property , the .height property etc of that
control from the collection. So while in using the message box command or
assigning the value of a control into a variable, for the most part you can
actually does dump the use of the .value property, however when using the
collection it is significantly important to always specify that you want the
value of the control copied into the collection, and not the actual control
added to the collection.

I shall also assume that this code you're writing is in the form's code
module, since if you put the code in a standard code module you're not be
able to distinguish what form and what controls are actually referencing.
(in fact the code will not compiled if those controls don't actually desist
on the form).


You code will thus look like:

Private Function GetMyValuesClause() As Collection

Set GetMyValuesClause = New Collection

GetMyValuesClause.Add cmbEmployeeName.Value
GetMyValuesClause.Add cldActivity.Value
GetMyValuesClause.Add cmbActivityCode.Value
GetMyValuesClause.Add textComments.Value
GetMyValuesClause.Add chkTransfer.Value
GetMyValuesClause.Add Item:=DLookup("[EmployeeID]", _
"SP_GET_CURRENT_EMPLOYEE_BY_WINDOWS_ID", _
"[Windows_AD_ID] = " & """" & fOSUserName() & """")


Call CleanMyCollection(GetMyValuesClause)

End Function

 

[Albert D. Kallal]

I should point out that since collecions are READ only, and your code of:

Call CleanMyCollection(GetMyValuesClause)

suggests that you want to do additional processing on that collection.

The above being the case, then you have to return a "copy" of the
collecion.

Thus, you last line of code would be:

set GetMyValuesClause = CleanMyCollection(GetMyValuesClause)

Funciton CleanMyColleciton will look like:

Function CleanMyColleciton(c As Collection) As Collection

Dim strbuf As String
Dim i As String

Set CleanMyColleciton = New Collection

' process and clean passed colleciton
For i = 1 To c.Count
strbuf = Nz(c(i), "")
strbuf = Replace(strbuf, ",", "") ' remove all ,
strbuf = Replace(strbuf, ".", "") ' remove all .
strbuf = Replace(strbuf, "!", "") ' remove all !
strbuf = Replace(strbuf, """", "") ' remove all "
strbuf = Replace(strbuf, " ", "") ' remove all double space (" ")

If strbuf <> "" Then
' only add data to collecion if resulting string has data
CleanMyColleciton.Add strbuf
End If
Next i

End Function

 

[Souris]

Thanks millions for helping and Merry Christmas,

 

[Souris]

Thanks again,

I use your code which works on cmbEmployeeName.
I comment cldActivity out since it is ActiveX Control.
I got "object required" again on the second line cmbActivityCode

Thanks again,

A few things:

You are using .value and.text intermixed in your code (at least try and be
consistent in your coding practices, and even if the code is wrong, I would
much prefer that you did one thing always the same way).

In access the VBA code, you will very rarely use .text. The text is only a
valid property of control when the control has focus, and thus is of little
use in most of your daily code that you'll use.

You often see some code in this newsgroups where people are trying to read
or modify the value of a control and you'll see zillions and zillions of set
focus commands because you can not read or modify the value of a controlled
when you use the .text property *unless* it has the focus.

You would thus have to go for each time you use .text:

docmd.SetFocus cmbEmployeeName
MyClause.Add Item:=cmbEmployeeName.Text

In a nutshell using the.text property of a control is extremely messy, and
you don't need to use it on all. I suspect there is some confusing here
because in VB6 all developers use the .text property for all of their code
(that is all they have, and it's not dependent on the control having focused
to use it in visual basic)

In MS access we must use.value. So often the reason for this confusion is
many developers coming from a visual basic background instinctively and
naturally been using the text property of a control for many years.

In a nutshell using.text is extremely messy, and you don't need to use it
all. (use the .value property).

I fact in most cases you don't even have to use the .value property and can
just use the name of the control.

eg

msgbox "last name contorl is = " & LastName

Ironically one exception or one area that deserves an extra bit of caution
is when you're adding controls to a collection. You most surely want to be
very careful and distinguished between the.value property, and that of
actually adding the actual control to the collection.

The following data control can referenced on a form and display the value

msgbox "LastName value is = " & me.LastName

if you go:

MyColleciton.Add me.LastName

You'll actually not add the value of lastname to the collection, but you'll
actually add the actual control on the form or reference to the control on
the formed to your collection. That is certainly a considerable difference
in the outcome. In fact after you add that control to the collection then
you could actually use the.value property , the .height property etc of that
control from the collection. So while in using the message box command or
assigning the value of a control into a variable, for the most part you can
actually does dump the use of the .value property, however when using the
collection it is significantly important to always specify that you want the
value of the control copied into the collection, and not the actual control
added to the collection.

I shall also assume that this code you're writing is in the form's code
module, since if you put the code in a standard code module you're not be
able to distinguish what form and what controls are actually referencing.
(in fact the code will not compiled if those controls don't actually desist
on the form).


You code will thus look like:

Private Function GetMyValuesClause() As Collection

Set GetMyValuesClause = New Collection

GetMyValuesClause.Add cmbEmployeeName.Value
GetMyValuesClause.Add cldActivity.Value
GetMyValuesClause.Add cmbActivityCode.Value
GetMyValuesClause.Add textComments.Value
GetMyValuesClause.Add chkTransfer.Value
GetMyValuesClause.Add Item:=DLookup("[EmployeeID]", _
"SP_GET_CURRENT_EMPLOYEE_BY_WINDOWS_ID", _
"[Windows_AD_ID] = " & """" & fOSUserName() & """")


Call CleanMyCollection(GetMyValuesClause)

End Function

 

[Souris]

Thanks again,

it works,
I correct control name and the code works,

Thanks millions,

 

[Souris]

My collection add works fine now.

I got ByRef Augument type mismatch when it execute
callCleanCollection(GetMyValuesClause)

It looks like I do not have right time to clean the collection.
shoule I clean them after I use them or I have use the diferent name on
calling function?

Your help is great apppreciated,

 

[Albert D. Kallal]

My collection add works fine now.

I got ByRef Augument type mismatch when it execute
callCleanCollection(GetMyValuesClause)

It looks like I do not have right time to clean the collection.
shoule I clean them after I use them or I have use the diferent name on
calling function?

You need to go:

set GetMyValuesClause = CleanMyCollection(GetMyValuesClause)

 

[Souris]

Thanks for the information,

You need to go:

set GetMyValuesClause = CleanMyCollection(GetMyValuesClause)