populate some userform fields from access database
 

Hi All,

I have a userform designed in excel, and I want to pull certain data 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

populate some userform fields from access database
 

Take a look at a similar link posted earlier today, titled: ADO copy from SQL
Server into Excel 2003 List Box.

I think there is some useful information in there, I could be wrong.
Partially, I am not sure how much of your provided code works successfully or
not or what it is it does. It looks like you already have to have the
student's name for part 2 to work, and you identified that you are wanting to
pull the name out of the database (which is what it looks like you have to
know in order for part 2 to work.... so you see my confusion about whether
your existing code works or not or if it is that you need more code before
your current part 2 to get part 2 to work?)



"sam" wrote:

Hi All,

I have a userform designed in excel, and I want to pull certain data 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