By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,458 Members | 1,201 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,458 IT Pros & Developers. It's quick & easy.

Passing Query Results to Form

P: n/a
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!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Sami wrote:
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!


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_AfterUpdate
Dim rst As DAO, .Recordset
Set rst = Me.recordsetclone
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.

Nov 12 '05 #2

P: n/a
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 <oi*@vinegar.com> wrote:
Sami wrote:
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!


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_AfterUpdate
Dim rst As DAO, .Recordset
Set rst = Me.recordsetclone
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.


Nov 12 '05 #3

P: n/a
Sami wrote:
Hi, if this works, great - but how would I do this? I have no idea
where I would begin for such a thing. Thanks.
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.rowsource = "Select LastName, FirstName,
StudentID, SSN From ....
If Me.SortBySSN Then Me.List0.RowSource = "Select SSN, LastName, FirstName,
StudentID From ..

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



On Thu, 19 Feb 2004 06:34:49 GMT, Salad <oi*@vinegar.com> wrote:
Sami wrote:
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!


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_AfterUpdate
Dim rst As DAO, .Recordset
Set rst = Me.recordsetclone
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.


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.