CODEWITHSUNDEEP
vbams-accesscombobox
Thameem
Thameem

Reputation: 756

Binding multivalued column's data into combo box via vba ms access

I have a table as follows

enter image description here

Where the BranchIds column is a multivalued column referering the ids of the branches table. Some how i need to bind the id and its related value in a combo box residing in another access forms as follows enter image description here

The permission table contains the data of which user is allowed to access which branch. I am unable to bind the branches from the perimssion table to the combo box in my another form.

The code i am trying. Got from MSDN after a length search..

Sub BindBranches()
   Me.comboBranchIds.RowSourceType = "Value List"

   Dim db As Database
   Dim rs As Recordset
   Dim childRS As Recordset

   Set db = CurrentDb()

   ' Open a Recordset for the Tasks table.
   Set rs = db.OpenRecordset("SELECT BranchIds FROM Permissions WHERE UserId = " & Forms!NavigationForm!txtSession.Value)
   rs.MoveFirst

   Do Until rs.EOF
      ' Print the name of the task to the Immediate window.
      'Debug.Print rs!TaskName.Value

      ' Open a Recordset for the multivalued field.
      Set childRS = rs!BranchIds.Value

         ' Exit the loop if the multivalued field contains no records.
         Do Until childRS.EOF
             childRS.MoveFirst

             ' Loop through the records in the child recordset.
             Do Until childRS.EOF
                 ' Print the owner(s) of the task to the Immediate
                 ' window.
                 'Debug.Print Chr(0), childRS!Value.Value
                 Me.comboBranchIds.AddItem Item:=childRS!Value.Value
                 'Me.comboBranchIds.RowSource = "SELECT BranchName FROM Branches WHERE ID = " + childRS!Value.Value
                 childRS.MoveNext
             Loop
         Loop
      rs.MoveNext
   Loop
End Sub

Upvotes: 1

Views: 236

Answers (1)

Gustav
Gustav

Reputation: 55816

A MultiValue field has little to do with a value list.

Just use the RowSource property of the MultiValue field, like:

SELECT [TableName].[FieldName] FROM [TableName] ORDER BY [Id];

as the RowSource property for your combobox.

Having a filter:

SELECT [TableName].[FieldName] 
FROM [TableName] 
WHERE UserId = [Forms]![NavigationForm]![txtSession]
ORDER BY [Id];

or modify the SQL by code to, say:

SELECT [TableName].[FieldName] 
FROM [TableName] 
WHERE UserId = 466
ORDER BY [Id];

Applying a revised SQL will automatically requery the combobox.

Upvotes: 1

Related Questions