How assign multi-valued value to a variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using 2007

How do you get the value of a multi-valued field on a form into a variable?
Something like this does not work.

strCompanys = me.Companys.value

Why not? I can see that the textbox contains a value of WPTS, JTS. But
trying to get the value this way produces a type mismatch error.
 
The multi-valued field (MVF) contains a *recordset* of values.

You will therefore need to set a Recordset variable to the field, and loop
through the recordset to manipulate the values.

Assuming you have a DAO object of type Field, test it to see if it is an
object. It will be if it is an MVF. You can then code like this:

Dim rsMVF As DAO.Recordset
If VarType(fld.Value) = vbObject Then
Set rsMVF = fld.Value
Do While Not rsMVF.EOF
Debug.Print rsMVF![Value].Value
rsMVF.MoveNext
Loop
End If

There's a more complete example in the ELookup() function here:
http://allenbrowne.com/ser-42.html
 
Thank you Allen for responding.

OK, I have a field on a form called txtCompanys. It is a Multi-Valued
field. When I put a debug on the line below, it immediately moves to the
else statment

If VarType(txtCompanys.Value) = vbObject Then
....
Else

which tells me that the code thinks this is not a Multi-Valued field, but I
know it is. What am I doing wrong?
 
Thank you Allen for responding.

OK, I have a field on a form called txtCompanys. It is a Multi-Valued
field. When I put a debug on the line below, it immediately moves to the
else statment

If VarType(txtCompanys.Value) = vbObject Then
...
Else

which tells me that the code thinks this is not a Multi-Valued field, but I
know it is. What am I doing wrong?
Click to expand...

try:
if VarType(txtCompanys)=vbObject Then
 
I don't understand that, reply, Woody.

Variant with brackets? Function name? Or array perhaps?

I don't believe you got that answer from Access. Time for me to move on to
answering someone else's question.
 
Are you in a bad mood? You said to me:
Ask Access what's there:
Debug.Print TypeName(Me.txtCompanys.Value)
Click to expand...

When I put that code in a button and ran it, it printed the following
following in my immediate window

Variant()

I don't see what the dificulty is in understanding that, but if you feel you
need to move on, alright.
 
Woody, I owe you an apology. It is indeed telling you that it is evaluating
the object's value as a variant array.

Try looping through the array like this:

Dim i As Long
If IsArray(Me.Companys.Value) Then
For i = LBound(Me.Companys.Value) To UBound(Me.Companys.Value)
Debug.Print Me.Companys.Value(i)
Next
End If

Where the field is null, it will not be an array; hence the need to test
with IsArray().

Where values are chosen, this should yield the results.

If the field has a default value, anything can happen.
 
Well, I'm not sure what to say. All I know is I put a button on my report
dialog and put this code in it.

Debug.Print TypeName(Me.txtCompanys.Value)

and my Immediate window says Variant()

Actually your earlier comment was quite helpfull to me. It put me onto
solving my original problem which was how to make use of a Multi-valued field
on a report dialog to create a query for a report with "Or" conditions. The
code follows:

Dim Variant1 As Variant
Dim countOfElements As Long
Dim whereString As String
Dim iCounter As Integer
Dim strWhere As String

Variant1 = Me.txtCompanys.Value
countOfElements = UBound(Variant1) - LBound(Variant1)

'Debug.Print Variant1

strWhere = ""
'Build strWhere based on what's in the array (variant)
For iCounter = 0 To countOfElements
'Debug.Print resultArray(iCounter)
strWhere = strWhere + " OR CompanyID= '" & Variant1(iCounter) &
"'"
Next iCounter

'Cut out the OR (OR space) from the first part of the string.
strWhere = Mid([strWhere], 4)

Me.Visible = False
DoCmd.OpenReport "rpt003_Companys", acPreview, ,
WhereCondition:=strWhere
DoCmd.RunCommand acCmdZoom75
 
Ouch!

Hope he does get the apology, and that this helps.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Pieter Wijnen"
 
Back
Top