opening an Access table from VB6

dartcoach · December 14th, 2005, 01:42 PM

Hi All,

I just got Visual Basic 6 and I'm completely new to it. I've been coding VBA for a while, so creating forms & such isn't a problem. My problem is how to I attach an Access table as the recordsource for the form.

Also, with a combobox dropdown, how do I create the rowsource?

Thanks for your help in advance.

Kevin

dartcoach

kindler · December 14th, 2005, 01:55 PM

sql_q = "SELECT * FROM table"
[Forms]![frmName].RecordSource = sql_q

I believe that should work.

penta · December 15th, 2005, 11:56 AM

Hi,
i understand u have a form on VB6. Open the VB6 toolbox, choose DAO or ADO reference, and the respective connection tool Data or ADOC. Open properties window and fill in DataSource following the forms, having selected the respective tool on the combobox. Same for ur combobox.
rgds P

dartcoach · December 15th, 2005, 06:23 PM

Ok all,

I've got a table that I can walk through on the main form. I've got a combo box on the main form, but I can't seem to get it to utilize any data. I've tried adding items to the list in the onload of the mainform - I get no errors, but no data is in the dropdown.

Also, I somehow didn't get the MSDN loaded, so I have no help functions. I've got a second form I want to open, but can't seem to come up with the right syntax to open it.

All your help is appreciated.

Kevin

dartcoach

Bob Bedell · December 16th, 2005, 01:13 AM

Hi Kevin,

This will load a combo box with data from an ADO recordset during the form load event. The ItemData property is an array VB6 maintains to store each data items primary key value. This way the primary keys are synchronized with the sorted list, allowing you to refer to the key value in code. For example:

Code:

cboData.ListIndex = 0

initially populates the combo box with the first data item in the list.

I included a small event procedure that lets you see how this works a little better:

Code:

Private Sub cboData_Click()
   MsgBox (cboData.ItemData(cboData.ListIndex) & " " & cboData.List(cboData.ListIndex))
End Sub

Also, the cmdOpenForm2_Click event shows how to open a second form:

Code:

Private Sub cmdOpenForm2_Click()
    Dim secondForm As New Form2
    Form2.Show
End Sub

Hereâs the code that loads the combo box:

Code:

Private Sub Form_Load()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strConnection As String

    Dim strProvider As String
    Dim strSource As String

    strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    strSource = "Data Source=" & App.Path & "\DataDB.mdb;"

    Set cnn = New ADODB.Connection
    strConnection = strProvider & strSource & "Persist Security Info=False"
    cnn.Open strConnection
    Set rst = New ADODB.Recordset

    With rst
        .ActiveConnection = cnn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open "tblData"
    End With

    'Clear combo box
    Me.cboData.Clear

    'Load combo box
    Do While Not rst.EOF
      cboData.AddItem rst!fldData
      cboData.ItemData(cboData.NewIndex) = rst!fldID
      rst.MoveNext
   Loop

   ' Set initial display to first item in ListIndex
   cboData.ListIndex = 0

End Sub

Hope that helps get you a bit further.

Bob

penta · December 16th, 2005, 08:09 AM

Hi,
let me correct my last reply. When u open the properties window u choose the
ConnectionString property to connect the table to ur form. To add new records
u choose on the EOFAction property nr 2-adDoAddNew.
Hope it helps,
P

dartcoach · December 16th, 2005, 10:51 AM

Thanks to all.
Bob, thanks for the code samples. They worked so far. The combo box is populated, but why won't the dropdown work? I can arrow and see the records, but when I click on the arrow, it doesn't dropdown my list. I've looked at all the properties for the combo box, but where in Access you can tell a combobox how many items you want to see, I don't see a similar property in VB6.

Thanks again for all your help.

Kevin

dartcoach

Bob Bedell · December 16th, 2005, 11:55 AM

Hi Kevin,

Quote:

quote:The combo box is populated, but why won't the dropdown work? I can arrow and see the records, but when I click on the arrow, it doesn't dropdown my list.

I'm not quite sue what you're saying here.

A VB6 combo box doesnât have a property like Accessâs ListRows property that lets you set the maximum number of rows displayed in the drop-down list. You can play with the Style property, however:

0 - Dropdown Combo gives you an arrow, drop-down list and the ability to add items to the list (default)

1 - Simple Combo, no arrow, navigate list with arrow keys, can enter items.

2 â Dropdown List gives you a fixed list.

The default style gives you a vertical scroll bar if the list exceeds 8 items. If you just drop a standard combo on a form and place the following code in the Form_Load event, your combo will get loaded with all the printer fonts on your system. Mine lists 62 that I can just scroll through.

Code:

Private Sub Form_Load()
   Dim i   
   AutoRedraw = True  
   For i = 0 To Printer.FontCount - 1   
      Combo1.AddItem Printer.Fonts(i)
   Next i
   Combo1.ListIndex = 0   
   Print "Number of printer fonts: "; Combo1.ListCount
End Sub

HTH,

Bob

dartcoach · December 16th, 2005, 12:09 PM

Bob,

Thanks so much. Got the dropdown to work correctly. Is there any way to increase the number (default is 8)?

Also, is there such a thing in vb as a continuous form?

Thanks again,

Kevin

dartcoach

penta · December 16th, 2005, 12:12 PM

Hi,
i've combobox working on VB6 form. I've checked the properties window for the combo and i've
seen that DataSource has to be on the control that has the connection to the table (Adodc)
and BoundControl property has to be on the field that will display when u click the down
arrow on the combo.
Hope it helps'
P