Populate userform fields from access database

[sam]

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")

rs.Close
Set rs = Nothing
cn.Close
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