How to add more than 1 row in a combo box in VB?

[Ling]

I've a problem here:
I am using Microsoft Access and linked it to SQL server.
My code is as below:

note: cmbCustomerDetails is a combo box & type is a value
list

'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim lngMove As Long

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
rst.CursorType = adOpenStatic

'Open the connection
Set cnn = CurrentProject.Connection

'Set up the Command objects's Connection, SQL and
Parameter types
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT tblDo.DoNumber AS [DO No],
tblDo.DoYear AS [Year], tblDo.DoDate FROM tblDo WHERE
tblDo.DoDate Between" & "#" & [Forms]![frmDeliveryOrder
(pop)]![txtstartYr] & "#" & "And" & "#" & [Forms]!
[frmDeliveryOrder (pop)]![txtendYr] & "#"

End With

Set rst = cmd.Execute
Me.cmbCustomerDetails.AddItem "DO No"
Do Until rst.EOF
Me.cmbCustomerDetails.AddItem Item:=(rst![DO No])

rst.MoveNext
Loop

cnn.Close
Set cnn = Nothing
Set cmd = Nothing

Currently, cmbCustomerDetails could not show rst![DO No].
I would like it to show Year, DoDate as well but how do I
do so?

Any ideas?

Thanx

Ling

 

[Newbie]

Here is how I do it . ..

Once the recordset has been created add the following code:

If rst.RecordCount > 0 Then
Me.cmbCustomerDetails.Value = ""
rst.MoveFirst
Do Until rst.EOF
strItem = rst.Fields("DO No")
Me.cmbCustomerDetails.AddItem item:=strItem
rst.MoveNext
Loop

HTH
Ali

 

[Guest]

Thanx.
But what about adding more than 1 column into the combo
box?

My query fetches the year n date as well..

Any ideas? Thanx

Ling

-----Original Message-----
Here is how I do it . ..

Once the recordset has been created add the following code:

If rst.RecordCount > 0 Then
Me.cmbCustomerDetails.Value = ""
rst.MoveFirst
Do Until rst.EOF
strItem = rst.Fields("DO No")
Me.cmbCustomerDetails.AddItem item:=strItem
rst.MoveNext
Loop

HTH
Ali

I've a problem here:
I am using Microsoft Access and linked it to SQL server.
My code is as below:

note: cmbCustomerDetails is a combo box & type is a value
list

'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim lngMove As Long

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
rst.CursorType = adOpenStatic

'Open the connection
Set cnn = CurrentProject.Connection

'Set up the Command objects's Connection, SQL and
Parameter types
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT tblDo.DoNumber AS [DO No],
tblDo.DoYear AS [Year], tblDo.DoDate FROM tblDo WHERE
tblDo.DoDate Between" & "#" & [Forms]![frmDeliveryOrder
(pop)]![txtstartYr] & "#" & "And" & "#" & [Forms]!
[frmDeliveryOrder (pop)]![txtendYr] & "#"

End With

Set rst = cmd.Execute
Me.cmbCustomerDetails.AddItem "DO No"
Do Until rst.EOF
Me.cmbCustomerDetails.AddItem Item:=(rst![DO No])

rst.MoveNext
Loop

cnn.Close
Set cnn = Nothing
Set cmd = Nothing

Currently, cmbCustomerDetails could not show rst![DO No].
I would like it to show Year, DoDate as well but how do I
do so?

Any ideas?

Thanx

Ling

.