Database Question using VBA

[mmulvenna]

I have spent the past 6-8 months learnig VISIO and am fairly comfortable with it. I have a number of drawings with VBA code and forms that drive them. I have lots of list/combo boxes that I populate from an access data base (2007 using ADO) using the following code.

Code Select Expand

Sub fillarray(tblname, fld)

    ' The database file name is in the windows registry
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    Dim db_filename As String
    Dim strConnect As String
    strConnect = "Microsoft.ACE.OLEDB.12.0"
    Dim saveErr As Long
    'On Error GoTo ErrorHandler
    'First, get the name of the database from the registry and open
    saveErr = 0
    db_filename = GetJTFRegistry("Data Base Name", "")
    If saveErr = 0 Then
        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
                & "Data Source=" & db_filename
        rs.CursorLocation = adUseServer
        ' Open the recordset GetRows to fill the array
        ' Using the adCmdTableDirect opens a base table against Jet, which
        ' is generally the fastest, most functional way to access tables.

        rs.Open tblname, cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
        ReDim fldname(rs.RecordCount)
        Dim i As Integer
        Dim y As Integer
        arrfields = rs.GetRows(rs.RecordCount, 0, fld)
        For i = 0 To rs.RecordCount - 1
            For y = 0 To 0
                fldname(i) = arrfields(y, i)

            Next

        Next
               'fldname is used to populate the ComboBoxes             i.e. CMBBOX.list = fldname
        rs.Close
        cn.Close
    End If



    Exit Sub

I store a number of things like logo image to display on the forms, background image to load on the forms, data base name, excel template names for reports, etc in the windows registry.

I am VERY new to ACCESS and would like some pointers on how to do the following:

I have created in ACCESS a table of vendors and an table of vendor parts.

On various VISIO drawings I would like the user to select a vendor and a vendor part and place the image of the part(field in data base) on my drawing, and/or place a part number from the selected part in  a user cell for the particular shape on the VISIO drawing.

I know how to create/get the user cell and put data into it and how to place an image on my drawing.

What I dont know is how to present the vendor list and subsequent part list to the user and get the data from the data base for the part the user selects.

If anyone has an samples they could share or point me to an example(s) I would really appreceiate it.

Thanks in advance for any help anyone is able to provide.

[mmulvenna]

Thanks to everone who viewed my post. I was WAAAYYYY overthinking this.  ::)I just ended up loading a vendor list box, after picking  the vendor then loaded a vendor category list box, then a vendor sub category list box, then after the user picks the category and the sub category I then loaded the the parts list box.   User picks the part and away we go with everthing I listed above. All listbox controls loaded from the data base using the GETROWS method

Very easy, very straight forward.

Sometime my senior moments get in the way.

Senior Citizen Mike

[rodsoares101]

Hi Mike, you could post an example of this drawing for us newbies learn something new.
Take care,
Rod.

[mmulvenna]

Okay here is a sample of visio and the db. The form does not do anytrhing other than fill the combo boxes. Make sure you have the reference to ado 2.8 libraries. It should be included in this vsd but just make sure it is. I just copied a bunch of stuff from some of my drawings and made no attempt to clean it up............sooooooooooooo good luck

You will need to modify the variable dbfile (in module1 <alt><F11>) name for your location after you download the attached files

Right click on the rectangle and choose the test db function.

It will display a form that fills a combo box. Pick a vendor and it fills the category box. Pick a category and it fills a sub category.

These were built under VISTA, Office ULTIMATE 2007 and VISIO 2007

Good Luck
Mike