Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unanswered: grabbing values from access table in vba

    i'm trying to populate a combo box on an excel spreadsheet with values from a table in an access db - here is the part of the code that is supposed to do this....


    strDBName = "\\NJFILE01\GFIM-SHR\MUNICIPAL DATA\Muni_AAA_Dbase.mdb"

    Set dbD = OpenDatabase(strDBName)
    Set rs = dbD.OpenRecordset("Select distinct [Date] from [tblTreasury]", dbreadonly)

    y = -1
    Do While Not rs.EOF
    y = y + 1
    ReDim Preserve temp(y)
    temp(y) = rs.Fields(0).Value
    rs.MoveNext
    Loop

    For i = 0 To UBound(temp)
    VBAProject.Sheet11.cbBgnDate.AddItem temp(i)
    VBAProject.Sheet11.cbEndDate.AddItem temp(i)
    Next i



    ....i have this executing on the on_open event - it does work, but its very slow - there's about 3000 dates and the for loop takes a few minutes to loop - there has to be a faster more efficient way to do this - can anyone help?

  2. #2
    Join Date
    Nov 2002
    Location
    The Netherlands
    Posts
    61
    strDBName = "\\NJFILE01\GFIM-SHR\MUNICIPAL DATA\Muni_AAA_Dbase.mdb"
    Set dbD = OpenDatabase(strDBName)
    blSQL = "Select distinct tblTreasury.[Date] from tblTreasury"
    With Me.YourCbx
    Set rs = db.OpenRecordset(blSQL)
    If rs.RecordCount > 0 Then
    .RowSourceType = "Table/Query"
    .RowSource = blSQL
    .Enabled = True
    'display * fields
    .ColumnCount = 1
    .ColumnHeads = True
    Else
    .ColumnCount = 1
    .RowSourceType = "Value List"
    .RowSource = "No records Found."
    End If
    End With

    Is this the thing you want? Haven't tried it for combo boxes before. Only on listboxes, but i see they almost have the same controls.
    Last edited by DeathWing; 12-04-02 at 03:21.

  3. #3
    Join Date
    Dec 2002
    Posts
    2
    thanks for your help but i think the combo box properties you've used in your code are for applicable to combos in Access, not Excel, which i'm using - i'm just pulling the values from access into a combo on a spreadsheet - that looks like a nice smooth way to execute this - would you possibly know the analogous syntax for excel?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •