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

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?

-- 
Rick (MVP - Excel)


"sam" <sam@discussions.microsoft.com> wrote in message 
news:47AC8DF5-75E0-4A5C-A2A3-83F38B4C0BC8@microsoft.com...
> 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
>
> 

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



"Rick Rothstein" wrote:

> 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?
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "sam" <sam@discussions.microsoft.com> wrote in message 
> news:47AC8DF5-75E0-4A5C-A2A3-83F38B4C0BC8@microsoft.com...
> > 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
> >
> > 
> 
> .
> 

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

"Rick Rothstein" wrote:

> 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?
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "sam" <sam@discussions.microsoft.com> wrote in message 
> news:47AC8DF5-75E0-4A5C-A2A3-83F38B4C0BC8@microsoft.com...
> > 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
> >
> > 
> 
> .
>