Populate userform fields from access database



Hi All,

I have a userform designed in excel, and I want to pull certain info from
access database based on what I put in Student_ID field.

Here is what I have so far for getting fields autopopulate based on what I
put in Student_ID field... What I want it to do is.. On excel userform..
when I input the Student_ID Number, I want certain other fields like,
Student name, Subjects taken etc. related to that ID to auto populate from
an access database,

Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC just
'indicating where the file is located

Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

rs.Open "Loan_Presentation", cn, 1, 3, 2

"SELECT Students.Roll_No, Students.Name" & "FROM
`C:\DOCUMENTS\Students.accdb`.Students Students" _
& "WHERE Students.Roll_No = Roll_No.value")

Set rs = Nothing
Set cn = Nothing

End Sub

Thanks in advance



muddan madhu

rs.Open Table1, cn, adOpenDynamic, adLockBatchOptimistic

With rs

.Fields("Student name") = UserForm1.Textbox1.Value
.Fields("Subjects taken") = UserForm1.TextBox2.Value

End With

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question