Reading and saving multivalue field in recordset 1

[BrianWen]

Hi!

In Access 2007, I'm creating a dynaset recordset, which among other fields, contain a multivalue field.

So I have this code:

Dim LocalFilter As DAO.Recordset
Set LocalFilter = CurrentDb.OpenRecordset("SELECT * FROM ProjectFilter WHERE user ='" & CheckUser() & "'", dbOpenDynaset)

If LocalFilter.EOF Then
LocalFilter.AddNew
Else
LokalFilter.MoveFirst
LokalFilter.Edit
End If

Now, I begin to update those fields in the recordset. Every field, but the multivalue field, updates correctly, when I run LocalFilter.Update.
However when I set the multivalue (it's type is Number) like this:

LokalFilter!Fieldname = 443

it gives me this error: "Method 'Collect' of object 'Recordset2' failed"

I wasn't expecting that to work though, as I believe one should use .Value. However:

LokalFilter!Fieldname.Value = 443

gives this error: "Object doesn't support this property or method".


So, how do I put this single value in that multivalue field? - And how can I put in several values to the field??

Please ask if you need more information...





P.S. I don't need recommendations not to use multivalue fields in Access. I know of the troubles it can give me...

 

[PWise]

notived a typo

LokalFilter.MoveFirst
LokalFilter.Edit
misspeled
sb
LocalFilter.MoveFirst
LocalFilter.Edit
did that help

 

[BrianWen]

Hi PWise!

Unfortunately no, the misspelling is only here on the site. I was just translating the variable name for it to make more sense and forgot some places, I see.
Shouldn't have started doing that at all...

 

[MajP]

I do not have access in front of me so I am doing this off the top of my head, but you can check the help if I made too many mistakes. But the general idea is correct.

public sub readFromMV
  dim rs as dao.recordset
  dim rsMV as dao.recordset

  set rs = currentdb.openrecordset("someTable")
  do while not rs.eof
    'the value property returns another recordset
    set rsMV = rs.fields("SomeMultiValField").value
    do while not rsMV.eof
      debug.print rsMV.fields(0)
      rsMV.moveNext
    loop
    rs.movenext
  loop
[code]

when writing you have to open both the rs and the rsMV for editing

[code]
public sub readFromMV
  dim rs as dao.recordset
  dim rsMV as dao.recordset

  set rs = currentdb.openrecordset("someTable")
  do while not rs.eof
    'the value property returns another recordset
    set rsMV = rs.fields("SomeMultiValField").value
    rs.edit
    do while not rsMV.eof
      rsMV.edit
      rsMV.fields(0) = someValue
      rsMV.update
      rsMV.moveNext
    loop
    rs.update
    rs.movenext
  loop
end sub

 

[BrianWen]

Thank you very much, MajP!

Your code worked just as intended, but even more important I learned how to do it. I wasn't aware, that .Value actually returned another recordset. But it makes sense!