Iterate thorough listbox field with values using DAO (1 Viewer)

calvinle · Oct 14, 2019

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.

Code:

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 · Oct 14, 2019

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 · Oct 14, 2019

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 · Oct 14, 2019

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

theDBguy · Oct 14, 2019

calvinle said:
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 · Oct 14, 2019

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

theDBguy · Oct 14, 2019

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

It's probably for the best. Good luck!

Pat Hartman · Oct 15, 2019

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.

calvinle · Oct 15, 2019

Pat Hartman said:
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 · Oct 15, 2019

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.

Iterate thorough listbox field with values using DAO (1 Viewer)

calvinle

Registered User.

theDBguy

I’m here to help

calvinle

Registered User.

pbaldy

Wino Moderator

theDBguy

I’m here to help

calvinle

Registered User.

theDBguy

I’m here to help

Pat Hartman

Super Moderator

calvinle

Registered User.

MajP

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread