HELP: Populate Excel FORM from Access - Pull from multiple rows

[sam]

Hi All,

I know how to pull data from access and populate fields in excel userform
when I have only one row to pull from for a particualar Student_Id, BUT I am
having issues
with pulling data from multiple rows for the same student( if the student
has multiple courses, it is displayed in multiple rows in a table) and
displaying them on the form once I input student ID.

For eg: I have a table "Student_Details" in access

Student_Details consists of Student_ID, Course, Grade, Quarter_ID

Student_ID Course Grade Quarter_ID
JP11456 Math A SP01
JP11456 Phy B SP01
MN3443 Bio B SP01
MN3443 Math A SP01
MM4456 Chem A SP01
MM4456 Math B SP01

So Basically, Once I Input Student_ID as "JP11456" in the student_ID field
in the excel Form, I want all the student data to be displayed in the form in
their respective text boxes, something like this:

Student ID: JP11456

Course Grade Quarter_ID
Math A SP01
Phy B SP01
NA NA NA

I have created upto 3 textboxes for each field "Course", "Grade",
"Quarter_ID" as any student can take a maximum on only 3 courses per quarter.

Hope I made it clear,

Thanks in advance

 

[Rick Rothstein]

Two questions...

1) Is your data always going to be sorted by the Student ID?

2) What are the names and locations with respect to each other (top left,
middle middle, etc.) for your controls?

 

[sam]

Hey Rick,

If you mean the data is sorted by Student_ID in the database then NO its
not. If you mean if the data will be pulled based on what I input in
Student_ID field on the form then YES.

Also about the names and locations of the columns in the database are as
follows:

Student_ID Course Grade Quarter_ID
JP11456 Math A SP01
JP11456 Phy B SP01
MN3443 Bio B SP01
MN3443 Math A SP01
MM4456 Chem A SP01
MM4456 Math B SP01

Names and locations of the fields on the Excel userform are as follows:

Student ID: [JP11456]

Course Grade Quarter_ID

Math A SP01
Phy B SP01
NA NA NA

Here, [JP11456] is the textbox where I will input the student ID, other
textboxes which will be populated from the database are:

[Math] [A ] [SP01 ]
[Phy] [ B ] [SP01 ]
[NA ] [ NA ] [NA ]

"Student ID", "Course", "Grade", "Quarter_ID" are the lables for the text
boxes
[NA] will be displayed in the textboxes which are empty.

Hope I made it clear.

thanks a lot for helping

 

[sam]

Hey Rick,

Did you get a chance to look into it.

I got something similar to what I want, I got to make it to work such that
It populates a listbox with the Students Grades But not able to get what I
really want.

Here is the piece of code that populates the listbox with students grades

Dim strSQL As String, conn As Object, rst As Object, k As Long, vaData As
Variant

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C\Documents\Students\Students_Data.accdb;"

strSQL = "SELECT Students_Grades" & _
" FROM Students_Info WHERE Student_ID = " & _
Me.StudentId.Value


Set rst = conn.Execute(strSQL)

With rst
Set .ActiveConnection = Nothing
k = .Fields.Count
vaData = .GetRows
End With

conn.Close

With UserForm1
With .listBox1
.Clear
.BoundColumn = k
.List = Application.Transpose(vaData)
.ListIndex = -1
End With
End With

Hope this helps

Thanks in advance