Passing Query Results to Form

[Sami]

Maybe I am going about this totally wrong....

What I have written is a query pulling SSN, Student-ID, LastName,
FirstName, MiddleInitital from a table.

Sort is so LastName, FirstName, MiddleInitial, SSN, and StudentID are
all ascending, and in this order.

I want to the person using this query, in a ListBox, to be able to
look up a person (student) by last name to see if they are in the
table already, and then pass all the above information to the form, of
which this listbox is a part of.

How do I do this? I can only pass all information if I use a list box
based on the table, but as a result, I cannot make a listbox with the
students sorted alphabetically by last name.

Help! I am also a novice, so again, step by step - and thanks!

[Salad]

Re: Passing Query Results to Form

Sami wrote:
[color=blue]
> Maybe I am going about this totally wrong....
>
> What I have written is a query pulling SSN, Student-ID, LastName,
> FirstName, MiddleInitital from a table.
>
> Sort is so LastName, FirstName, MiddleInitial, SSN, and StudentID are
> all ascending, and in this order.
>
> I want to the person using this query, in a ListBox, to be able to
> look up a person (student) by last name to see if they are in the
> table already, and then pass all the above information to the form, of
> which this listbox is a part of.
>
> How do I do this? I can only pass all information if I use a list box
> based on the table, but as a result, I cannot make a listbox with the
> students sorted alphabetically by last name.
>
> Help! I am also a novice, so again, step by step - and thanks![/color]

In the listbox (lets' call it List0) AfterUpdate event you could do
something similar to this. Assumes The first column is the StudendID and
is hidden and the form is bound to the student table

Sub List0_AfterUpda te
Dim rst As DAO, .Recordset
Set rst = Me.recordsetclo ne
rst.FindFirst "StudentID = " & Me.List0
Me.bookmark = rst.bookmark
set rst = nothing
end sub

As the person selects a name, the after update event is called, looks for
the employee record, finds it, and displays it.

[Sami]

Re: Passing Query Results to Form

Hi, if this works, great - but how would I do this? I have no idea
where I would begin for such a thing. Thanks.

On Thu, 19 Feb 2004 06:34:49 GMT, Salad <oil@vinegar.co m> wrote:
[color=blue]
>Sami wrote:
>[color=green]
>> Maybe I am going about this totally wrong....
>>
>> What I have written is a query pulling SSN, Student-ID, LastName,
>> FirstName, MiddleInitital from a table.
>>
>> Sort is so LastName, FirstName, MiddleInitial, SSN, and StudentID are
>> all ascending, and in this order.
>>
>> I want to the person using this query, in a ListBox, to be able to
>> look up a person (student) by last name to see if they are in the
>> table already, and then pass all the above information to the form, of
>> which this listbox is a part of.
>>
>> How do I do this? I can only pass all information if I use a list box
>> based on the table, but as a result, I cannot make a listbox with the
>> students sorted alphabetically by last name.
>>
>> Help! I am also a novice, so again, step by step - and thanks![/color]
>
>In the listbox (lets' call it List0) AfterUpdate event you could do
>something similar to this. Assumes The first column is the StudendID and
>is hidden and the form is bound to the student table
>
>Sub List0_AfterUpda te
> Dim rst As DAO, .Recordset
> Set rst = Me.recordsetclo ne
> rst.FindFirst "StudentID = " & Me.List0
> Me.bookmark = rst.bookmark
> set rst = nothing
>end sub
>
>As the person selects a name, the after update event is called, looks for
>the employee record, finds it, and displays it.[/color]

[Salad]

Re: Passing Query Results to Form

Sami wrote:
[color=blue]
> Hi, if this works, great - but how would I do this? I have no idea
> where I would begin for such a thing. Thanks.[/color]

Well. I assume you created a form for students, perhaps via the Form Wizard.
If so, your form is linked (bound) to the Students table or to a query that
displays student records. Then I assume you have a list box to the left or
right of the student data that contains the student names. If you are with me
so far then dbl-click on the listbox to pull up the property sheet. Click on
the Events tab and then select the OnClick event, hit the triple dots and
paste the code in.

Change the reference from List0 to whatever your listbox name is. Also, I
assume StudentID is the first col of the list box, hidden or visible or is the
bound field of the listbox

Should work.

Later on, you can open up the query and view the SQL code. Then copy that
code into the rowsource of the list box. Then you could put some checkboxes
over the listbox to display the sort order....by last first, ssn, etc. Then
when you click on the checkbox, you get the SQL, remove the order by, add the
new sort order, and maybe swap the field order so your listbox search is more
dynamic. Ex:

If Me.SortByName then Me.List0.rowsou rce = "Select LastName, FirstName,
StudentID, SSN From ....
If Me.SortBySSN Then Me.List0.RowSou rce = "Select SSN, LastName, FirstName,
StudentID From ..

By changing the row source, and a slight adjustment to the column widths this
could be very cool.



[color=blue]
>
>
> On Thu, 19 Feb 2004 06:34:49 GMT, Salad <oil@vinegar.co m> wrote:
>[color=green]
> >Sami wrote:
> >[color=darkred]
> >> Maybe I am going about this totally wrong....
> >>
> >> What I have written is a query pulling SSN, Student-ID, LastName,
> >> FirstName, MiddleInitital from a table.
> >>
> >> Sort is so LastName, FirstName, MiddleInitial, SSN, and StudentID are
> >> all ascending, and in this order.
> >>
> >> I want to the person using this query, in a ListBox, to be able to
> >> look up a person (student) by last name to see if they are in the
> >> table already, and then pass all the above information to the form, of
> >> which this listbox is a part of.
> >>
> >> How do I do this? I can only pass all information if I use a list box
> >> based on the table, but as a result, I cannot make a listbox with the
> >> students sorted alphabetically by last name.
> >>
> >> Help! I am also a novice, so again, step by step - and thanks![/color]
> >
> >In the listbox (lets' call it List0) AfterUpdate event you could do
> >something similar to this. Assumes The first column is the StudendID and
> >is hidden and the form is bound to the student table
> >
> >Sub List0_AfterUpda te
> > Dim rst As DAO, .Recordset
> > Set rst = Me.recordsetclo ne
> > rst.FindFirst "StudentID = " & Me.List0
> > Me.bookmark = rst.bookmark
> > set rst = nothing
> >end sub
> >
> >As the person selects a name, the after update event is called, looks for
> >the employee record, finds it, and displays it.[/color][/color]