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

 

[joel]

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

 

[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

.

 

[joel]

The code I wrote put all the info you would need to put into a userform
on one row of sheet 3. Then all you have to do is located the sudent
row that you are looking for and take the data starting in column 5.

If you have the dat like you shown in multiple rows then the code is
simply some thing like this



if Range("A1") <> "" then
textbox1 = Range("A1")
if Range("A2") <> "" then
textbox2 = Range("A2")
if Range("A3") <> "" then
textbox3 = Range("A3")
end if
end if
end if