HELP JOEL: auto populate certain userform fields from access datab
 

Here is what I have so far for getting fields autopopulate based on what I
put in Roll Number field... What I want it to do is.. On excel userform..
when I input the Student Roll Number, I want certain other fields like,
Student name, Subjects taken to auto populate from an access database,, and
all this details are in access database. Hope you can help me with this.

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

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:Documents\Students.accdb;DefaultDir =C:\Documents\" _
), Array( _
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;;UID=admin ;" _
)), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT Students.Roll_No, Students.Name" & "FROM
`C:\DOCUMENTS\Students.accdb`.Students Students" _
& "WHERE Students.Roll_No = Roll_No.value")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
.Refresh BackgroundQuery:=False
End With
End Sub


rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub