Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP PLEASE: populate form fields from multiple access tables
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP PLEASE: populate form fields from multiple access tables
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/sh...d.php?t=175357 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP PLEASE: populate form fields from multiple access tables
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 "joel" wrote: 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/sh...d.php?t=175357 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP PLEASE: populate form fields from multiple access tables
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 -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175357 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP PLEASE: Populate certain userform fields from access database | Excel Programming | |||
populate some userform fields from access database | Excel Programming | |||
Populate userform fields from access database | Excel Programming | |||
HELP: Trying to populate form fields posted on intranet from a dat | Excel Programming | |||
HELP: Populate corresponding userform text fields from access data | Excel Programming |