HELP PLEASE: populate form fields from multiple access tables

[sam]

Hi All,

I know how to pull data from access and populate fields in excel userform
when I have data in a single row in a single table, BUT I am having issues
with pulling data from multiple tables and multiple rows for a student and
displaying them on the form.

For eg: I have Two tables Students and Courses in access

Students table consists of Student_ID, First_Name, Last_Name and other
personal details:

Student_ID First_Name Last_Name
JP11456 John Paul
MN3443 Megan Noel
MM4456 Molly Moer

Courses table consists of Student_ID, course names and quarter_ID details:

Student_ID Course Quarter_ID
JP11456 Math SP01
JP11456 Phy SP01
MN3443 Bio SP01
MN3443 Math SP01
MM4456 Chem SP01
MM4456 Math SP01

So basically, Once I input "JP11456" in the excel form I want to pull all
the info of this particular student on the form, something like this:

Student ID: JP11456

First Name: John
Last Name: Paul
Course1: Math
Course2: Phy
Course3: NA
Quarter: SP01

Hope I made it clear,

Thanks in advance

 

[sam]

Hey Joel,

Thanks for your help.

I think I might have confused you about what I want. Basically, I want to
auto populate data in my userform from access database based on what I put in
Student_ID field in the excel userform, Below is the description to what I
want, Hopefully I made it easy for everyone to understand:

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

I combined the sheets together so it will be easy to put the data into
the userform. See if you like this format

Sub Combinesheets()

Set StudentNameSht = Sheets("Sheet1")
Set StudentCourseSht = Sheets("sheet2")
Set Destsht = Sheets("sheet3")

With StudentCourseSht
'sort sheet by Student ID and Quarter
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending, _
key2:=.Range("C1"), _
order2:=xlAscending
End With

With StudentNameSht
'copy header row to dest sht
.Rows(1).Copy _
Destination:=Destsht.Rows(1)

RowCount1 = 2
DestRowCount = 2
Do While .Range("A" & RowCount1) <> ""
StudentID = .Range("A" & RowCount1)
Set StudentIDRange = .Range("A" & RowCount1 & ":C" & RowCount1)

ColCount = 5
'find student course records
With StudentCourseSht
StudentIDRange.Copy _
Destination:=Destsht.Range("A" & DestRowCount)
Set c = .Columns("A").Find(what:=StudentID, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
RowCount2 = c.Row
Quarter = .Range("C" & RowCount2)
Destsht.Range("D" & DestRowCount) = Quarter
Do While .Range("A" & RowCount2) = StudentID
If .Range("C" & RowCount2) <> Quarter Then
DestRowCount = DestRowCount + 1
StudentIDRange.Copy _
Destination:=Destsht.Range("A" & DestRowCount)
Destsht.Range("D" & DestRowCount) = Quarter
ColCount = 5
Quarter = .Range("A" & RowCount2)
End If
Course = .Range("B" & RowCount2)
Destsht.Cells(DestRowCount, ColCount) = Course
ColCount = ColCount + 1
RowCount2 = RowCount2 + 1
Loop
End If

End With
DestRowCount = DestRowCount + 1
RowCount1 = RowCount1 + 1
Loop

End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=175357

Microsoft Office Help

.