Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP PLEASE: Populate certain userform fields from access database | Excel Programming | |||
HELP: Populate corresponding userform text fields from access data | Excel Programming | |||
Auto populate fields | Excel Discussion (Misc queries) | |||
Auto Populate fields | Excel Programming | |||
Auto populate fields | Excel Discussion (Misc queries) |