How to make forms like that of System Mechanic

[kinjalgp]

How to fill array of textboxes with data from access database which has fields like 1,2,3,4,5,6, using FOR To Next statement.

[Serge]

Try something like this. Add a reference to Microsoft ActiveX Data Objects 2.x Library:

Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer
    
    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "C:\MyDB.mdb", "admin", ""
    
    rs.Open "Select * From Customer Where CustomerId = YourNumber", cn, adOpenStatic
    
    If Not rs.EOF Then
        For i = 0 To rs.Fields.Count - 1
            Text1(i).Text = rs.Fields(i).Value
        Next
    End If
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set xn = Nothing
End Sub

Assuming that C:\MyDB.mdb is my database, Customer is my table and CustomerId is a criteria for my record.

Also, make sure that you have enough textboxes on the form (i.e. the number of Textboxes should match the number of fields in the table)

Regards,

[Paul282]

Nice bit of code, Think I'll borrow it too

you just need the '.MoveNext' statement in the while loop otherwise you're in an infinite loop of the same record.

[Serge]

No I don't. I don't have a loop there, since I'm assuming that I have only 1 record. I use For...Next to loop through fields collection not through records. But yes, you're right that if you use Do...Loop to loop through recordset, you need MoveNext

Regards,

[kinjalgp]

I used the following code to fill the dynamic textboxes with data in my access database using the following code bu it shows error No. 6325 "Item not found in this collection"

Private db As Database
Private rs As Recordset

Private Sub Form_Load()
Set db = OpenDatabase(App.Path & "\Database.mdb")
End Sub

Private sub cmdFind_click()
Set rs = db.OpenRecordset("SELECT * FROM Data WHERE Number = '" & txtNumber & "'", dbOpenDynaset)
For i = 1 To 14
Text1(i).Text = rs!A & (i)
rs.close
End sub

Note:A1, A2, A3...etc are the fields in my database