[RESOLVED] Accesing MSAccess database

[lyilaly]

Here´s another one:

I had this code on a excel page. Now i´m doing this page on VB6. How can i change the routines to access all the data from various tables i have in an access database. Assme that i have tables for all the selections to be made by the comboboxes also.

'=============
'Rig Selection
'=============
Private Sub text1_Change()
Text6.Text = Format(Now(), "MMM-DD-YYYY")
Do While Combo6.ListCount > 0
Combo6.RemoveItem (0)
Loop
Me.Combo6.AddItem "P-101"
Me.Combo6.AddItem "P-102"
Me.Combo6.AddItem "P-103"
Me.Combo6.AddItem "P-110"
Me.Combo6.AddItem "P-111"
Me.Combo6.AddItem "P-112"
End Sub

'=============
'Opcion "Pool"
'=============
Private Sub option1_Click()
Me.Text4.Text = 1
Me.Text5.Text = 1
End Sub

'===================
'Opcion "Vacaciones"
'===================
Private Sub option2_Click()
Me.Text4.Text = 2
Me.Text5.Text = 1
End Sub

'================
'Opcion "Vacante"
'================
Private Sub option3_Click()
Me.Text4.Text = 3
Me.Text5.Text = 1
End Sub

'================
'Tallas de Bragas
'================
Private Sub text3_Change()
Do While Combo1.ListCount > 0
Combo1.RemoveItem (0)
Loop
    Me.Combo1.AddItem "32"
    Me.Combo1.AddItem "34"
    Me.Combo1.AddItem "36"
    Me.Combo1.AddItem "38"
    Me.Combo1.AddItem "40"
    Me.Combo1.AddItem "42"
    Me.Combo1.AddItem "44"
    Me.Combo1.AddItem "46"
    Me.Combo1.AddItem "48"
    Me.Combo1.AddItem "50"
    Me.Combo1.AddItem "52"
    Me.Combo1.AddItem "54"
    Me.Combo1.AddItem "56"
    Me.Combo1.AddItem "N/A"
End Sub

'===============
'Tallas de Botas
'===============
Private Sub combo1_Change()
Do While Combo2.ListCount > 0
Combo2.RemoveItem (0)
Loop
    If Combo1.DataChanged = True Then
    '= "32" Or "34" Or "36" Or "38" Or "40" Or "42" Or "44" Or "46" Or "48" Or "50" Or "52" Or "54" Or "56" Then
        Me.Combo2.AddItem "35"
        Me.Combo2.AddItem "36"
        Me.Combo2.AddItem "37"
        Me.Combo2.AddItem "38"
        Me.Combo2.AddItem "39"
        Me.Combo2.AddItem "40"
        Me.Combo2.AddItem "41"
        Me.Combo2.AddItem "42"
        Me.Combo2.AddItem "43"
        Me.Combo2.AddItem "44"
        Me.Combo2.AddItem "45"
        Me.Combo2.AddItem "46"
        Me.Combo2.AddItem "N/A"
    End If
End Sub

'=============
'Tipo de Casco
'=============
Private Sub combo2_Change()
Do While Combo3.ListCount > 0
Combo3.RemoveItem (0)
Loop
    If Combo2.Text = "35" Or "36" Or "37" Or "38" Or "39" Or "40" Or "41" Or "42" Or "43" Or "44" Or "45" Or "46" Then
        Me.Combo3.AddItem "Verde Nuevo"
        Me.Combo3.AddItem "Verde Usado"
        Me.Combo3.AddItem "Blanco Nuevo"
        Me.Combo3.AddItem "Blanco Usado"
        Me.Combo3.AddItem "N/A"
    End If
End Sub

'==============
'Tipo de Lentes
'==============
Private Sub combo3_Change()
    Do While Combo4.ListCount > 0
            Combo4.RemoveItem (0)
    Loop
        If Combo3.Text = "Verde Nuevo" Or "Verde Usado" Or "Blanco Nuevo" Or "Blanco Usado" Or "N/A" Then
            Me.Combo4.AddItem "Claros"
            Me.Combo4.AddItem "Oscuros"
            Me.Combo4.AddItem "Ambos"
            Me.Combo4.AddItem "N/A"
        End If
End Sub

'==========
'Salvavidas
'==========
Private Sub combo4_Change()
    Do While Combo5.ListCount > 0
             Combo5.RemoveItem (0)
    Loop
        If combo4text = "Claros" Or "Oscuros" Or "Ambos" Or "N/A" Then
            Me.Combo5.AddItem "Nuevo"
            Me.Combo5.AddItem "Usado"
            Me.Combo5.AddItem "Pendiente"
            Me.Combo5.AddItem "N/A"
        End If
End Sub

'==============================
'Add data to the database sheet
'==============================
Private Sub Command1_Click()
    Dim lRow As Long
    Dim lPart As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data")

    '============================
    'Locate first row on database
    '============================
    lRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
  
    lPart = Me.Text1.Value
    'lPart = Me.text1.ListIndex
    
    '=======================
    'Check
    '=======================
    If Trim(Me.Text1.Value) = "" Then
        Me.Text1.SetFocus
        MsgBox "Please enter a part number"
Exit Sub
    End If
    '====================
    'Copy to the database
    '====================
        With ws
            .Cells(lRow, 1).Value = Me.Text1.Value
            .Cells(lRow, 2).Value = Me.Text2.Value
            .Cells(lRow, 3).Value = Me.Text3.Value
            .Cells(lRow, 4).Value = Me.Combo1.Value
            .Cells(lRow, 5).Value = Me.Cells(17, 10).Value
            .Cells(lRow, 6).Value = Me.Combo2.Value
            .Cells(lRow, 7).Value = Me.Cells(19, 10).Value
            .Cells(lRow, 8).Value = Me.Combo3.Value
            .Cells(lRow, 9).Value = Me.Combo4.Value
            .Cells(lRow, 10).Value = Me.Combo5.Value
            .Cells(lRow, 11).Value = Me.Combo6.Text
        End With
    
    '=============================
    'Clear data from txtbx & cbobx
    '=============================
        Me.Text1.Value = ""
        Me.Text2.Value = ""
        Me.Text3.Value = ""
        Me.Combo1.Value = ""
        Me.Combo2.Value = ""
        Me.Combo3.Value = ""
        Me.Combo4.Value = ""
        Me.Combo5.Value = ""
        Me.Combo6.Value = ""
        Me.Cells(17, 10).Value = ""
        Me.Cells(19, 10).Value = ""
End Sub

'=====================
'Command Button Clear
'=====================
Private Sub Command2_Click()
    Me.Text1.Text = ""
    Me.Text2.Text = ""
    Me.Text3.Text = ""
    Me.Combo1.Text = ""
    Me.Combo2.Text = ""
    Me.Combo3.Text = ""
    Me.Combo4.Text = ""
    Me.Combo5.Text = ""
    Me.Combo6.Text = ""
    Me.Text4.Text = ""
    Me.Text5.Text = ""
    Me.Text6.Text = ""
    Me.Option1.Value = False
    Me.Option2.Value = False
    Me.Option3.Value = False
    Me.Text1.SetFocus
End Sub

'=====================================================
'Command Button Borrar todo e ir a la pagina principal
'=====================================================
Private Sub Command3_Click()
    Me.Text1.Text = ""
    Me.Text2.Text = ""
    Me.Text3.Text = ""
    Me.Combo1.Text = ""
    Me.Combo2.Text = ""
    Me.Combo3.Text = ""
    Me.Combo4.Text = ""
    Me.Combo5.Text = ""
    Me.Combo6.Text = ""
    Me.Text4.Text = ""
    Me.Text5.Text = ""
    Me.Text6.Text = ""
    Me.Option1.Value = False
    Me.Option2.Value = False
    Me.Option3.Value = False
    Form2.Hide
    Form1.Show
End Sub
'====================================

thanks

[Hack]

Create a recordset with an SQL SELECT query and load your combos from the results. Assuming you are using ADO code to connect to your database, and example would be

Dim sSQL As String
sSQL = "SELECT fieldname FROM tablename "
Set rs = New ADODB.Recordset
rs.Open sSQL, ConnectionObject
Do While Not rs.EOF
     Combo6.Additem rs.Fields.Item("fieldname").Value
     rs.MovedNext
Loop
rs.Close
Set rs = Nothing 

[lyilaly]

Sorry,

But can you help me also with the ADO connection code? Don´t know how to.

Thanks

[si_the_geek]

Moved to Database Development forum

For an example, see the ADO Tutorial link in my signature.

[lyilaly]

Thanks for you help....

Solved