?
Solved

VBA Programming -bind data with Combo Box

Posted on 2008-11-04
3
Medium Priority
?
386 Views
Last Modified: 2013-11-28
Give some ideas on how to bind data with combo box . I need Codings too.
0
Comment
Question by:logicslab
  • 2
2 Comments
 
LVL 46

Accepted Solution

by:
tbsgadi earned 2000 total points
ID: 22883585
Hi logicslab,

Have a look at the attached code

Good Luck!

Gary
Public Sub PopulateLBWithData(DBPath As String, _
TableName As String, FieldName As String, _
oListControl As Object,Optional Distinct As Boolean = False, _
Optional OrderBy As String) 
'PURPOSE: Populate a list box, combo box
'or control with similar interface with data
'from one field in a Access Database table 
'Parameters: DBPath: FullPath to Database
'TableName: The Name of the Table
'FieldName: Name of the Field
'Distinct: Optional -- True if you want distinct value
'Order By:  Optional -- Field to Order Results by 
'Must have reference to DAO in your project 
Dim sSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.TableDef
Dim f As DAO.Field 
Dim sTest As String
Dim bIsNumeric As Boolean
Dim i As Integer 
On Error Resume Next 
'validate all parameters
    
oListControl.AddItem "a"
oListControl.Clear
If Err.Number > 0 Then Exit Sub 
sTest = Dir(DBPath)
If sTest = "" Then Exit Sub 
Set db = Workspaces(0).OpenDatabase(DBPath)
If Err.Number > 0 Then Exit Sub 
Set td = db.TableDefs(TableName)
If Err.Number > 0 Then
    db.Close
    Exit Sub
End If 
Set f = td.Fields(FieldName)
    If Err.Number > 0 Then
        db.Close
        Exit Sub
    End If 
If Len(OrderBy) Then
    Set f = td.Fields(OrderBy)
    If Err.Number > 0 Then
        db.Close
        Exit Sub
    End If
End If
    
sSQL = "SELECT "
If Distinct Then sSQL = sSQL & "DISTINCT "
sSQL = sSQL & "[" & FieldName & "] FROM [" & TableName & "]" 
If OrderBy <> "" Then sSQL = sSQL & " ORDER BY " & OrderBy 
Set rs = db.OpenRecordSet(sSQL, dbOpenForwardOnly) 
With rs
    Do While Not .EOF
        oListControl.AddItem rs(FieldName)
        .MoveNext
    Loop
    .Close
End With 
db.Close
End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 22883589
logicslab,

Also look at the following
http://www.fontstuff.com/access/acctut13b.htm

Gary
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question