• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

VBA Programming -bind data with Combo Box

Give some ideas on how to bind data with combo box . I need Codings too.
0
logicslab
Asked:
logicslab
  • 2
1 Solution
 
tbsgadiCommented:
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
 
tbsgadiCommented:
logicslab,

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

Gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now