create drop down list from access, in VB

[wolf99]

Hi
Im very new to VB and access, but have a steep learning curve to scramble up.
Can anyone let me know how to populate a dropdown list combo box with data from access table column?

am using MVB 6, access and access 2000

thanks!

[DJMaze]

You have the data access components in MS VB?

If so, it should be easy by dropping a DB Lookup box and hook it to a data source.

NOTE: If the application is for business then i advice you to not use access for multi-tier. Use a simple MS SQL or a far more superior SQL server like PostgreSQL.

[wolf99]

Thats what I thought, but either Ive done it wrong, or missed something
here is what I have:

P2List data:
Name: P2List
Connect: Access
DatabaseName: C:\Setup.mdb
RecordSource: Setup
(everything else pretty much as default)

select ComboBox:
DataField: Product Desc (not primary key, does this matter?)
DataSource P2List
Style: 2-Dropdown List
(everything else pretty much as default again)

I guess I should have specified this in my initial post. In the db "Setup.mdb", the table "Setup" contains 3 test records, but the box just drops one empty line.....
any suggestions?

thanks

[wolf99]

just realised that if I use the properties box to populate the list with random test items, they drop down OK and if I select one of them, it changes the key related field in the db.

So am I using the right control at all?

basically I need a drop down list of CURRENT records to select one, so that other fields in the record may be read.

[DJMaze]

DataSource: a dataset (table)
DataField: a field of the dataset

Lookup: another dataset
LookupField: a field of the other dataset
LookupKey: a field of the other dataset

As you can see the word "lookup" was the hint in my first post and is important.
Therefore there are two kinds of DB combo boxes.

1. a dropdown based on strings
2. a dropdown based on a Lookup dataset

#2 is the advanced version where you see column X but it will store Y as value in the datasource.

If this is still all new to you and you are using the free version of MS then i doubt you have the advanced components.
Then you must use #1 and fill the dropdown programaticly and store the value programaticly.

[wolf99]

OK well I dont see the lookup fields you mention, so guess this has to be coded.
problem is I really know pretty much nothing about VB (or access), having come from C and assembly and am trying to cobble my learning together from old VB programs as I go along.

I got some code from a site:

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

but I really have no clue what half of it means. Do I need to use SQL? is there a shorter way to do this (it seems to me there should be)? etc, etc.
(Thanks for your hints btw)
If you could even point me in the right direction, maybe a tutroial site that would include this? I searched MSDn, google, etc for info on lookup box, but became more confused

thank you for taking the time to help me, it is very much appreciated.

[DJMaze]

I'm sorry i can't help you more then this.
In past couple of years i realy started to hate Microsoft products so my knowledge has faded away.

Currently i'm only using C, C++, D, Delphi and PHP (so not the whole .NET memory eating shebang crap).

[wolf99]

hey, thanks anway DJMaze, a point in the right direction is always better then nothing at all. Thanks for all your help

PS. using VB I realize how much I love C

[wolf99]

doing a bit of research on this, I found out about a control called a dbcombo.

I havnt been able to find much concise information for noob's on it though.

If anyone knows about this could they answer a few questions for me.

Q1: is this available in a normal basic VB application (ie not activeX, etc)

Q2: where is the control? I dont see it in the tool box, do I need Service Pack6?

Q3: If I dont need the pack and it should be appearing in the toolbox, how do I go about coding a control form the ground up? (I have only used the toolbox so far) I dont want someone to write me half a program here, just maybe things like, do I write it in along with the other form subs?, do I start "private dbcombo sub.." or what?, etc

thanks for any help at all, is much appreciated, even as mentioned if its just a link to another good vb forum, etc

Ta