Re: How to add/load records to combobox ?

"Mac" <msamani@xxxxxxxxxxx> wrote in message
news:ObqDLbn1FHA.1132@xxxxxxxxxxxxxxxxxxxxxxx
> VB 6 QUESTION
>
>
> I tried to put this question several time but I guess I did not give much
> information.
>
> I have a table of about 1200 records in sql2k. It has vendor number and
> vendor name, address, limit, group code etc.
>
> When an user load my program, I want the complete table to be loaded to
> combobox so user can arrow down and find the customer name or number and
> they should be able to scroll back and forth. Once user hit return key,
> it should display rest of the information to my form.
>
> I am looking for a syntax that add my cursor record to combobox.
>
> Thanks

Hopefully, this'll be enough to give you that "kick start" <g>... I'm no DB
expert by any means so there are probably (most likely <g>) more efficient
ways of doing some of this stuff...

This needs a new project with 2 combobox controls and 3 textbox controls
(all default names)
Lay out the controls vertically and stretch them across the form (doesn't
really matter)

Open the Project/References dialog, scroll down and place a check in
"Microsoft ActiveX Data Objects 2.8 Library".

Paste this code and run. You may have to adjust the path to the northwind
DB.....
'===========
'Started with....
'How To Bind Complex-Bound Controls at Run Time with Visual Basic 6.0
'http://support.microsoft.com/default.aspx?scid=kb;en-us;189668
Option Explicit

Dim cnNWind As New ADODB.Connection
Dim rsCustomers As New ADODB.Recordset
Dim rsOrders As New ADODB.Recordset

'Setup an array to hold the primary keys
'You can use a combobox's ItemData property too but that's
'limited to Long's only.
Private msCustomerIDs() As String

Private Sub Combo1_Click()
'Click fires when an item is selected or ListIndex changes.
Dim sSQL As String

'Load a second combo with all orders from this customer
With Combo1

'Get all orders for this customer
sSQL = "SELECT * FROM Orders Where CustomerID = '" _
& msCustomerIDs(.ListIndex) & "'"

Debug.Print sSQL 'verify the string looks right

rsOrders.Open sSQL, cnNWind

End With

'Fill the second combo based on the info from the first
With Combo2
.Clear

'Fill the combo with orders from this customer
Do While .ListCount < rsOrders.RecordCount
.AddItem rsOrders.Fields("OrderID").Value
rsOrders.MoveNext
Loop


rsOrders.Close

'Select the first item. Doing this fires Click, and the whole
'process starts over.
.ListIndex = 0

End With

End Sub

Private Sub Combo2_Click()
'When the user selects an order, show the dates in the textboxes
Dim sSQL As String

With Combo2

sSQL = "SELECT * FROM Orders Where OrderID = " _
& .List(.ListIndex)

Debug.Print sSQL 'verify the string looks right

End With

rsOrders.Open sSQL, cnNWind

Text1.Text = rsOrders.Fields("OrderDate").Value
Text2.Text = rsOrders.Fields("RequiredDate").Value
Text3.Text = rsOrders.Fields("ShippedDate").Value

rsOrders.Close
End Sub

Private Sub Form_Load()
Dim strConn As String

strConn = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=" & _
"C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;"

cnNWind.CursorLocation = adUseClient
cnNWind.Open strConn

rsCustomers.Open "SELECT * FROM Customers" _
, cnNWind, adOpenStatic, adLockOptimistic, adCmdText

ReDim msCustomerIDs(rsCustomers.RecordCount - 1)

'Load Combo1 with customers
With Combo1
.Clear

Do While .ListCount < rsCustomers.RecordCount

.AddItem rsCustomers.Fields("CompanyName").Value
'In this DB, CustomerID is the primary key so save that
msCustomerIDs(.NewIndex) = rsCustomers.Fields("CustomerID").Value

'Don't forget this <g> Seems I always do.
rsCustomers.MoveNext
Loop

'Select the first item. Doing this fires Click
.ListIndex = 0
End With

rsCustomers.Close

End Sub
'===========

--
Ken Halter - MS-MVP-VB - http://www.vbsight.com
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm
Please keep all discussions in the groups..


.

Relevant Pages

  • Re: Playing AVI and MPEG using MCI
    ... "mciSendStringA" (ByVal lpstrCommand As String, ... Dim mlRet As Long ... Private Sub CenterObject ... If mlRet 0 Then ...
    (microsoft.public.vb.controls)
  • Change this Program
    ... Private sPathFrom As String ... Private Sub enableControl ... Dim oColor As OLE_COLOR ... Dim sPathTo As String ...
    (microsoft.public.vb.general.discussion)
  • Re: Error when running vb app with FlexGrid control
    ... Private Sub cmdNetChange_Click ... On Error GoTo Command1_Click_Error ... Private Sub fnGetData(strParam0 As String, strParam1 As String, FLX As ... Dim rsADOObject As Recordset ...
    (microsoft.public.vb.general.discussion)
  • Sum in a dynamic query
    ... Private Sub Form_Open ... Dim db As Database, Tbl As TableDef ... Dim db As Database, qd As QueryDef, ctl As Control, s As String, Item As ...
    (comp.databases.ms-access)
  • Re: Importing a Spreadsheet with VBA
    ... Sub CreateTableFromXL(PathAndFile As String, TableName As String, _ ... Dim db As DAO.Database ... Dim sSQL As String ... Dim sVALUES As String ...
    (microsoft.public.access.modulesdaovba)