Iterate thorough listbox field with values using DAO

[calvinle]

Hi,

I have a table with multiple fields as Textbox, and one of the field is set as Listbox with values and multiple selection allowed.

Using DAO, I would like to iterate and pull all data from all fields, including that listbox.

sTable = "tblUser"
sProjId = "5"

  Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & sTable & " WHERE proj_id = " & sProjId & "", dbOpenSnapshot)
  
  If rs.RecordCount <> 0 Then
    For i = 1 To rs.Fields.Count - 1
      rs.MoveFirst
      Do Until rs.EOF
        sFieldName = Nz(rs.Fields(i).Name)
        If sFieldName = "mbr_selection" Then
        ' This is the listbox that I need to populate the value from the member 
          selection.
        End If
        sFieldInput = Nz(rs.Fields(i))
        End If
        rs.MoveNext
      Loop
  End If

The sFieldInput will jammed once it hit the field "mbr_selection" as it's a listbox field with values.

Thanks

 

[theDBguy]

Hi. Sounds like you're referring to a MVF. If so, when you get to that part in your code, you'll need to create a separate recordset for the values selected in the Listbox. I have some sample MVF code here that you might be able to adapt for your use.

 

[calvinle]

Will I have any issue if I decide to move this table to a SQL server in the future? I don’t believe SQL table has that MVF property?

Thanks

 

[pbaldy]

Yes, you'll have a problem. That's one of many reasons most of us avoid MVF.

 

[theDBguy]

Will I have any issue if I decide to move this table to a SQL server in the future? I don’t believe SQL table has that MVF property?

Thanks

Hi. As Paul said, you won't be able to migrate your table, as is, to SQL Server in the future, if it has a MVF. So, if SQL Server is in your immediate future, you might consider removing the MVFs and use child tables instead.

 

[calvinle]

Thanks! Going back to create a table with option field instead.

 

[theDBguy]

Thanks! Going back to create a table with option field instead.

It's probably for the best. Good luck!

 

[calvinle]

An OptionGroup stores only a single value. If you have multiple values, you actually need an additional table.

Also, not that I need to know but the whole process mystifies me. Why are you mushing all the values together? You are not using delimiters so there will be no way to pull them apart later unless they are all fixed in length to begin with.

I was just trying to see if it was possible to not create a separate table with option but use that feature instead. Because on the current form, its showing as listbox with option, so by creating a new table, i will need to create a subform as well. Was trying to find shortcut.

Thanks

 

[MajP]

I was just trying to see if it was possible to not create a separate table with option but use that feature instead. Because on the current form, its showing as listbox with option, so by creating a new table, i will need to create a subform as well. Was trying to find shortcut.

FYI, when you use a MVF it to creates a separate table to store individual values, except it does it behind the scenes not visible to the user. There are ways to avoid the standard subform using a multiselect listbox or faking the MVF listbox control. Unfortunately, this is no shortcut requiring quite a bit of code.