How to load list box from Access Database

[kiranlalka]

Hi !!

I want to load the data into List Box from Access Data base

How can i set it dynamically, so that i can select the values in the list box

[Hack]

Welcome to the forums.

What programming language are you using? Access VBA?

[kiranlalka]

Welcome to the forums.

What programming language are you using? Access VBA?

Hi Hack,

Its in Excel User Forms List Box i want to load the data from Excel...

Dim db As Database, rs As Recordset, r As Long, dt As String
        Set db = OpenDatabase("C:\Invoice\Invoice.mdb")
      Set rs = db.OpenRecordset("InvoiceTbl", dbOpenTable)  
   With rs
    .AddNew ' create a new record
            dt = invmon.Value & "-" & invdt.Value & "-" & invyear.Value
            .Fields("InvoiceDate") = dt
          .Fields("CompanyID") = 1
            ' add more fields if necessary...
            .Update ' stores the new record
    End With    
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    MsgBox "Data has been saved in access"

I am using the above code to store the data to Access From Excel, Similarly I am looking for info on how to load the data from Access to Excel Forms List Box.

[kiranlalka]

Hi !!

I am using Excel Forms and want to load the data from Access to List Box and data can be selected from the List Box in forms.

Dim db As Database, rs As Recordset, r As Long, dt As String
    ' connect to the Access database
    ' Set cn = New ADODB.Connection
        Set db = OpenDatabase("C:\Invoice\Invoice.mdb")
    ' open the database
    Set rs = db.OpenRecordset("InvoiceTbl", dbOpenTable)
    
   With rs
    .AddNew ' create a new record
            ' add values to each field in the record
            dt = invmon.Value & "-" & invdt.Value & "-" & invyear.Value
            .Fields("InvoiceDate") = dt
          .Fields("CompanyID") = 1
            ' add more fields if necessary...
            .Update ' stores the new record
    End With
    
    rs.Close
    Set rs = Nothing
    db.Close 
    Set db = Nothing
    MsgBox "Data has been saved in access"

I am using above code to store the data into Access from Excel...

Now i want to show the data to List Box of User Forms by getting the data from Access DB.

[Hack]

Excel VBA question moved to Office Development

[kiranlalka]

Team,

Any updates on this request... How can i show the Access data in the Excel List Box of User Forms... I had posted the code which i am using to store the data from Excel to List Box but how could i do the reverse of it...

[westconn1]

Set db = OpenDatabase("C:\Invoice\Invoice.mdb")
    ' open the database
    Set rs = db.OpenRecordset("InvoiceTbl", dbOpenTable)
      ' first part stays the same
    do until rs.eof
     listbox1.additem rs(f)   ' where f is the index of the field to fill the listbox
     rs.movenext
  loop

alternatively you can use an sql query to populate the recordset, with only the desired results from the table

[seenu_1st]

sql query something like this to open only where the field name 'FirstName' has 'seenu'

Set rs = db.OpenRecordset("select * from MyTable where FirstName='seenu'")

[kiranlalka]

Hi Seenu,

I was trying to access using the SQL but was getting TypeMismatch issue....

Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Invoice\Invoice.mdb")
    Set rs = db.OpenRecordset("select BName from BrokerTbl")
Do Until rs.EOF
     Invoice_Preparation.brokername.AddItem rs
     rs.MoveNext
     Loop  
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

[seenu_1st]

kiran, try like this

Invoice_Preparation.brokername.AddItem rs!BName