Add To Multiple Columns

[Bopo]

Hey Guys

Well I'm really frustrated with this, despite having tried numerous different methods, I still can't simply add data to specific columns within my combo box. I'm looping through a recordset to do this, my code is awful, simply because I couldn't figure how to write those two queries as one, therefore I'm having to do the same operation twice.

Basically I would like rs![ResourceCode] to fill column (0) and rs![Description to fill column (1).

Help appreciated.

Private Sub cboresourcetype_AfterUpdate()
Dim sqlQuery(1) As String, countRecords As Integer, llistIndex As Long, rs As Object

sqlQuery(0) = "SELECT Description, ResourceCode FROM TblResource WHERE ResourceType = '" & cboresourcetype & "' AND New = 'Yes'"
sqlQuery(1) = "SELECT R.Description, R.ResourceCode FROM TblResource AS R LEFT JOIN TblRsrcEmp AS RSE ON R.ResourceCode = RSE.ResourceCode WHERE R.ResourceType = '" & cboresourcetype & "' AND RSE.Returned = 'Yes'"

Set rs = CurrentDb.OpenRecordset(sqlQuery(0))

'testing if there's a record in there

If rs.EOF = False Then
With Me.cboresources
    Do
  '' doesn't work .Column(0).AddItem = rs![ResourceCode]
  .AddItem rs![ResourceCode]
    .AddItem rs![Description]
    
    rs.MoveNext
    Loop Until rs.EOF
    End With
    
    rs.Close
    Set rs = Nothing
End If


Set rs = CurrentDb.OpenRecordset(sqlQuery(1))

If rs.EOF = False Then
With Me.cboresources
    Do
    .AddItem rs![ResourceCode]
    .AddItem rs![Description]
    rs.MoveNext
    Loop Until rs.EOF
    End With
    
    rs.Close
    Set rs = Nothing
End If
End Sub

 

[DCrake]

When using AddItem the rowsource type must be value list. The technique for adding multiple columns are as follows


.AddItem rs![ResourceCode] & ";" & rs![Description]
& ";" & Rs![Another field) etc

David

 

[jdraw]

Hey Guys

Well I'm really frustrated with this, despite having tried numerous different methods, I still can't simply add data to specific columns within my combo box. I'm looping through a recordset to do this, my code is awful, simply because I couldn't figure how to write those two queries as one, therefore I'm having to do the same operation twice.

Basically I would like rs![ResourceCode] to fill column (0) and rs![Description to fill column (1).

Help appreciated.

Private Sub cboresourcetype_AfterUpdate()
Dim sqlQuery(1) As String, countRecords As Integer, llistIndex As Long, rs As Object

sqlQuery(0) = "SELECT Description, ResourceCode FROM TblResource WHERE ResourceType = '" & cboresourcetype & "' AND New = 'Yes'"
sqlQuery(1) = "SELECT R.Description, R.ResourceCode FROM TblResource AS R LEFT JOIN TblRsrcEmp AS RSE ON R.ResourceCode = RSE.ResourceCode WHERE R.ResourceType = '" & cboresourcetype & "' AND RSE.Returned = 'Yes'"

Set rs = CurrentDb.OpenRecordset(sqlQuery(0))

'testing if there's a record in there

If rs.EOF = False Then
With Me.cboresources
    Do
  '' doesn't work .Column(0).AddItem = rs![ResourceCode]
  .AddItem rs![ResourceCode]
    .AddItem rs![Description]
    
    rs.MoveNext
    Loop Until rs.EOF
    End With
    
    rs.Close
    Set rs = Nothing
End If


Set rs = CurrentDb.OpenRecordset(sqlQuery(1))

If rs.EOF = False Then
With Me.cboresources
    Do
    .AddItem rs![ResourceCode]
    .AddItem rs![Description]
    rs.MoveNext
    Loop Until rs.EOF
    End With
    
    rs.Close
    Set rs = Nothing
End If
End Sub

There is a video tutorial at this site that should help.

http://www.datapigtechnologies.com/flashfiles/combobox1.html

Note: When building the query, select both fields you want in the combobox.

 

[smig]

you have few mistakes here:
you should have both queries open as recordsets, and you should go like this:
add an item to the combo, move to the first item on both recordsets, and copy the data from them to the combo, and update.
add another item. move another step on both recordsets, copy and update.....

your .additem is wrong.
both columns should be added to the same item, seperated by comma.

but, it will be much easier to add the items from both columns into a long string seperated by commas, and attach this string to the combo at the end.