Apply filter to List Box in subform.

[Mesa]

OK, I am pulling out my hair trying to figure this one out......

I have a main form with a subform. In the subform I have a table with
a One to Many relationship with a table in the main form. The subform
links field is linked to the main form tables primary key. In short,
i can type multiple notes in the subform about a record in the main
form.

Ok, here is the kicker, inside the subform I made a list box to select
the indiviual notes per record. It works fine except it displays all
the notes of all records and I want to display only the notes for each
selected record. I know I have to change the [Properties, Data, Row
Source] because I know by default it displays all the records of the
field, but i dont know any way to do what i am trying to do.

Thankyou for looking this question up.

 

[Bob Hairgrove]

OK, I am pulling out my hair trying to figure this one out......

I have a main form with a subform. In the subform I have a table with
a One to Many relationship with a table in the main form. The subform
links field is linked to the main form tables primary key. In short,
i can type multiple notes in the subform about a record in the main
form.

Are you storing the notes as one note in each subform record?

Ok, here is the kicker, inside the subform I made a list box to select
the indiviual notes per record.

You say "per record" ... of the subform's table or of the main form's
table?

It works fine except it displays all
the notes of all records and I want to display only the notes for each
selected record. I know I have to change the [Properties, Data, Row
Source] because I know by default it displays all the records of the
field, but i dont know any way to do what i am trying to do.

Thankyou for looking this question up.

Can you give us some more details about the table structures (i.e.,
what columns are there in each table)?

 

[Mesa]

Thanks for helping, on the first table named "Contacts" which is the
main form table.

Column 1 is "Record" (which is the Primary Key)
Column 2 is "First Name"
Column 3 is "Last Name"

The next table is named "Note" which is the subform table. It also
has a one to many relationship to "Contacts" linked by "Record"

Column 1 is "Record"
Column 2 is "Name"
Column 3 is "Memo"

The subform links field is "Record"

 

[Bob Hairgrove]

Thanks for helping, on the first table named "Contacts" which is the
main form table.

Column 1 is "Record" (which is the Primary Key)
Column 2 is "First Name"
Column 3 is "Last Name"

The next table is named "Note" which is the subform table. It also
has a one to many relationship to "Contacts" linked by "Record"

Column 1 is "Record"
Column 2 is "Name"

"Name" is a reserved word ... although you can get around it by using
brackets (i.e. "[Name]"), you will probably find it better to avoid
using reserved words in your object names.

Column 3 is "Memo"

Likewise, "Memo" is a reserved word.

The subform links field is "Record"

OK. Thanks for clarifying. Now you have a listbox in your subform ...
however, each subform record only has one memo field (i.e. notes). So
what is the advantage of using a list box when you could display the
note for the current record in a text box bound to your memo field?

 

[Mesa]

Well, I plan to store several notes per record and have the
convenience of clicking on them to display the information.

if you want to see an image go here
http://img295.imageshack.us/my.php?image=imageib7.jpg

the bottom right corner is where the sub for is. a picture is worth a
thousand words.

well, i done some research and this is the closest ive gotten bellow
but still not working


Dim strSQL As String
strSQL = "Select " & Me!Record.Value
strSQL = strSQL & " Name"
Me!List.RowSourceType = "Table/Query"
Me!List.RowSource = strSQL

 

[Mesa]

OK, I got another break thru, unfortunately it is very alien to me. i
can use a where clause in the rowsource property for example

SELECT Name
FROM Note
WHERE

but this is very confusing to me because I dont know how to tell the
WHERE to grab only the records that share the links fields

PLZ somebody help me!!!

 

[tina]

okay, having figured out that you need a WHERE clause in the listbox
control's RowSource SQL statement (very good, by the way), now you're almost
there.

if you've ever built a query and added criteria to a field in the Design
grid, you actually built a WHERE clause without knowing it. in this case,
instead of using a "hard" value, you're going to use a *reference* to the
primary key field of the main form. it's easy enough to do:

open the subform in Design view. click on the listbox, and click on the
Properties "line" in the Properties box. click the Build button (...) at the
right side of the line. now you're in a design view that looks just like a
query design view. in the Record column, add the following criteria, as

Forms!MainFormName!Record

replace "MainFormName" with the correct name of the main form, of course.
also, note Bob's comments about reserved words, elsewhere in this thread;
you'll save yourself a lot of grief if you take them to heart. for more
information, see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#ReservedWords.

hth

 

[Mesa]

Hey Tina, thanks for some help. Your freekin AWSOME WOOHOOO! Ive
been on this for like 10 hrs. Final code looks like this
below........I got to learn this SQL

SELECT [(Note)].Name
FROM [(Note)]
WHERE ((([Forms]![Main]![Record])=[Record]));

 

[John W. Vinson]

OK, I got another break thru, unfortunately it is very alien to me. i
can use a where clause in the rowsource property for example

SELECT Name
FROM Note
WHERE

but this is very confusing to me because I dont know how to tell the
WHERE to grab only the records that share the links fields

PLZ somebody help me!!!

Please help us, and we'll try.

We cannot see your screen, we do not know how your table is
constructed, and we do not know the names of the controls on your
form.

You say "share the links fields". What ARE the links fields? Where on
your form (mainform or subform) are those fields available? What
fieldname would need to match?

Note that the word Name is a VERY bad choice for a fieldname. A table
has a Name property; a field has a Name property; a form control has a
Name property. Access (and you, and I) *will* get confused about what
Name is a Name for. Pick another Name for Name and you'll be better
off!

John W. Vinson [MVP]

 

[tina]

you got it, very good.
btw, query Design view is simply a graphical representation of a SQL
statement. when you build a query in Design view, Access takes that
"picture" and writes the SQL statement, which are the real instructions that
are executed when the query runs. besides purchasing a good book on SQL, a
good way to learn more is to build queries in query Design view, and then
switch to SQL view to read the SQL statement. you can usually put the cursor
in words that are all capitals (such as SELECT, FROM, WHERE, JOIN, etc) and
press F1 to read more about those parts of the statement in Access Help.

hth