Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 14th, 2005, 01:42 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default opening an Access table from VB6

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
__________________
dartcoach
 
Old December 14th, 2005, 01:55 PM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

I believe that should work.
 
Old December 15th, 2005, 11:56 AM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old December 15th, 2005, 06:23 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old December 16th, 2005, 01:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


 
Old December 16th, 2005, 08:09 AM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old December 16th, 2005, 10:51 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old December 16th, 2005, 11:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


 
Old December 16th, 2005, 12:09 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old December 16th, 2005, 12:12 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Opening ,dbf file through ADO in VB6? vchell VB Databases Basics 2 September 11th, 2007 03:43 AM
opening VBA project in VB6 joseph1 VB How-To 0 July 8th, 2006 08:25 AM
Run ACCESS Aplication Without Opening ACCESS kamal1667 Access 3 January 2nd, 2006 12:13 PM
Opening unknown table names NiMot VB Databases Basics 2 July 17th, 2003 02:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.