[RESOLVED] Add data to combo from other table

[sujittalukder]

I have five tables in access database. and five types of data entry form in vb6.
Table1 contains several fields. First field is "Name of the company"
All tables have several fields but the first field of all the table is "Name of the company"
Data to table1 is saved through form1
Data to table to is saved through form 2 and so on.
Name of the company can be entered through form 1 only ie if an new company is reqiured to be entered then the same can be done through form1 only.

As other form also needs name of the company so I have added a combobox to every form for the field "Name of the company"

Now I want to display the name of the company in the combox box1 of form 2 ( for all forms except form 1) from the field 0 (ie the first field) of table 1
how can I do so?

[MaximilianMayrhofer]

Your database structure seems somewhat awkward.. But you want something like this:

Public Class Form1
        
    Dim ds As New DataSet()
    'To connect to a .mdb file
    Dim conn As New OleDb.OleDbConnection()
    Dim da As New OleDb.OleDbDataAdapter()        
        
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Creating and filling our dataset from the database
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\*Your directory path*\*Database name*.mdb; User Id=admin;Password=;"
        da.SelectCommand = New OleDb.OleDbCommand("SELECT Name_of_the_company FROM *Table name*")
        da.SelectCommand.Connection = conn
        da.Fill(ds)
 
        'If the dataset is not empty
        If ds.Tables(0).Rows.Count > 0 Then
            'Fill the combobox with the list of available records
            BindCombo(combobox1, ds.Tables(0))
        End If
    End Sub
 
    Private Sub BindCombo(ByVal cbo As ComboBox, ByVal dt As DataTable)
        With cbo
            'Any quotation marks are required
            .Items.Clear()
            .DropDownStyle = ComboBoxStyle.DropDownList
            .DataSource = dt
            .DisplayMember = "Name_of_the_company" 
            .ValueMember = "Name_of_the_company" 
            '.SelectedIndex = 0
        End With
    End Sub

[sujittalukder]

Thanks for the code. As i am learning VB6, so some more help is needed:
Where to put tje code in a standard module or class module?
At the top of the code vb.net code is written? So is it for vb.net or for vb6

[sujittalukder]

I have made a small sample file to explain my problem. Hope this will give more clarification.

[Hack]

At the top of the code vb.net code is written? So is it for vb.net or for vb6

His example is VB.NET

[sujittalukder]

thanks but I need the same for VB6. Also i have attached a file in last post. Can someone plz help me to sort out the problem

[sujittalukder]

I have tried to do same by Adodc and adodb but not getting the result.
For form 3, adodc is used. Here I want data from field(0). But no data comes in combo1 if field(0) is used. But If field(1) is used, data are coming. Why?
Form form2, I tried Adodb. Here no data is coming.
Can some one please help me.
File is attached.

[si_the_geek]

Don't use the Recordcount property to loop thru the records - it is much more efficient (and takes less code) to use the .EOF property instead, eg:

connect 

Do While Not rs.EOF
    Combo1.AddItem rs.Fields(0)
    rs.MoveNext
Loop

The problem you had was that the RecordCount was -1 (there are records, but the exact number hasn't been determined yet). The .EOF and .BOF properties do not have that kind of issue.

Note also that your line to open the recordset is less efficient than it should be - it would be better to replace the last two parameters with these three:
adOpenReadOnly, adLockForwardOnly, adCmdText


All of this is shown in the article How can I fill a combobox with values in a database? from our Database Development FAQs/Tutorials (at the top of this forum)

[sujittalukder]

Thanks si the greek. for the reply and guidance and for the tutorial link too. Very useful.