surely a form with a ListBox can be used in a query?

[1.156]

Fred and Tina were kind enough to try to help in an earlier thread but
I'm still getting nowhere.

I have a simple query that works fine but the user must type in a
"Genotype" to display all the records which match this genotype field in
the main table. I got all this working from the query wizzard. What I
need is to add a ListBox (I don't need a ComboBox I believe -- am I
right?) which displays all the available genotypes (available in a
separate, presently unused table called Genotype) so the user can first
select a Genotype from the list and then get the same display of all the
records with this Genotype.

I'm following a thick reference book on the subject and I've made a form
and added a ListBox to it (unbound, I'm told to use). Problem is that
nowhere have I found a clue on how to incorporate this form containing
the listbox and fields into the query. I've looked everywhere in the
query parts of the book and even tho this info must be there, I haven't
stumbled into it.

Could someone give me some words which hint as to what steps I should be
trying to get this form accepted by the query?

Thanks, Rob

 

[Duane Hookom]

You would set the criteria of the "Genotype" field to:
Forms!frmMyForm!lboGenotype
Substitute your form and control name and make sure the form is open when
you run the report/query.

 

[1.156]

Duane, thanks a lot for trying to help.

My form is named "frm_G" and the NAME propertyof the listbox is
"Genotype". So, in the query criterion under the Genome field I typed

Forms!frmfrm_G!lboGenotype!

I have the form open and then I run the query. All I get is a box
labeled "Enter parameter value" with a single window above which is
Forms!frmfrm_G!lboGenotype!

Of course I want it to display the form with the listbox so I can select
Genotype.

Can you tell where I've gone wrong?

Thanks again, Rob

 

[tina]

when Duane said "substitute", he meant substitute completely, as

Forms!frm_G!Genotype!

hth

 

[1.156]

Tina, yes that helps. Now I actually get something that looks a lot
like a form pop up when I run the query so something is working better.
Unfortunaltely, the form has nothing at all on it -- it's blank. It
looks nothing like the design view of the form. I'm closer and will
keep trying.
Thanks a lot, Rob

 

[John Vinson]

Tina, yes that helps. Now I actually get something that looks a lot
like a form pop up when I run the query so something is working better.
Unfortunaltely, the form has nothing at all on it -- it's blank. It
looks nothing like the design view of the form. I'm closer and will
keep trying.

This will happen when you have a Form bound to a recordset which is a)
empty, so you can't see any existing records and b) not updateable, so
you can't see the new record either. A form to be used for query
criteria should be *unbound* - make sure that its Recordsource
property is blank.

 

[1.156]

John, thanks for the help but this form says "unbound" in its window.
But it would be nice to find this Recordsource property but everything I
do to find the "properties" of this form I get just a meaningless window
with no info- it has a "general" tab and that's all. So I guess I can't
be sure if it's unbound or not. I can find loads of properties of
tables and queries but not forms.

The main problem is that when I run the query I do not see the listbox I
need to make the starting selection. All I get is a list of field
headings with nothing below them..

Sure wish I could find this simple concept of using a form in a query
mentioned in some literature. They go on and on about queries and forms
but never a word about how to use a form in a query.

Thanks for trying, Rob

 

[John Vinson]

John, thanks for the help but this form says "unbound" in its window.

I'm not sure what you mean by "its window".

But it would be nice to find this Recordsource property but everything I
do to find the "properties" of this form I get just a meaningless window
with no info- it has a "general" tab and that's all. So I guess I can't
be sure if it's unbound or not. I can find loads of properties of
tables and queries but not forms.

Open the Form in design view. Right mouseclick the little square box
at the upper left intersection of the rulers. Select Properties from
this dropdown list; the Record Source property is the first one on the
Data tab.

The main problem is that when I run the query I do not see the listbox I
need to make the starting selection. All I get is a list of field
headings with nothing below them..

Sure wish I could find this simple concept of using a form in a query
mentioned in some literature. They go on and on about queries and forms
but never a word about how to use a form in a query.

AHA! There's the problem.

No, you are doing it reasonably - but backwards. Don't expect the
Query to open the form for you; it won't.

Instead, *open the Query from the Form*. If it's a select query to
display data, it would be best to create a Form (for onscreen use)
and/or a Report (for printing) based on the query; put a Command
Button on the form to open this Form (or maybe two buttons, one to
open the form, one to print the report).

If it's an action query, or if (unwisely, IMHO) you want to give the
user a query datasheet, you can put a command button to run (open) the
query.

 

[1.156]

John, thanks for your continued response. I did find the Record Source
property and there's nothing there so I guess the form is unbound as it
should be.

You say

Instead, *open the Query from the Form*. If it's a select query to
display data, it would be best to create a Form (for onscreen use)
and/or a Report (for printing) based on the query; put a Command
Button on the form to open this Form (or maybe two buttons, one to
open the form, one to print the report).

I guess it is a 'select' query; I only want to display some records in the main table based on selecting a single field in it. It's hard to imagine a more simple goal and I'm surprised Access doesn't have simple machinery to do it. You say I should put a command button on the form to open this form. I don't follow that. Surely you don't mean the form opens itself -- so you must mean two forms. I only have one as far as I know. I'll start looking up command buttons.

Earlier I was told that my query wouldn't open the form so I have it opened in design view when I double click the query in the query list.

Thanks again for trying to help. Amazingly, after several months I still can't make a simple query work.

Rob

 

[1.156]

I finally pushed the right button and my form now actually displays a
listbox with the proper things to select from. But nothing happens when
I click on or highlight my selection. It appears that I need an action
from the click event from MyListBox
In my query, I have Forms!MyForm!MyListBox in the Criteria line. If
this is correct, can someone suggest an event to assign to MyListBox? I
think this is all I need to get this simple query to work.

Thanks, Rob

 

[tina]

i believe i can answer that, but i need a clear picture of your setup. you
have a table of records that includes a field for Genotype, and you want to
be able to look at a subset of those records, based on a specific Genotype
that you choose. correct?
okay, now. is that table bound to a form? *if not, suggest you build a form
to show the records - the rule of thumb is to view/modify data via a form
that is bound to the records, not in the table directly.*
if the table *is* bound to a form, is that the same form you have the
listbox on?
if yes, do you want to open the form with all the records showing, and then
choose a Genotype in the listbox to filter the records?
or do you want to open the form as "blank", and *then* choose a Genotype in
the list box to show the specific records?
you have to decide *how* you want to filter and display your records, before
we can suggest ways to do it.

hth
p.s.

Amazingly, after several months I still can't
make a simple query work.

Access is a great tool, but it can be very frustrating when you're starting
out - it's much harder to learn to use than other common Office software,
because you have to understand some theory rather than just opening the
software and start typing. and we've all gone thru it - it really does get
better.
if you haven't taken a class, or gotten a good basic reference manual, i
think it would really help you a lot to do either - or both.

 

[1.156]

Tina, thanks for trying to help again.

Yes, I have two tables. The main one has lots of fields, including
Genotype. I have a working query which asks for a Genotype but you
have to type it into a window, and it can be misspelled, etc. The user
types in the Genotype and then sees a bunch of records of that Genotype.

But I want to expand this so that instead of typing the Genotype, the
user can select it from a list. I built another table called
tbl_Genotype to contain a list of all the possible Genotypes (later, I'd
like to build this table from the Main one because this list could
expand - but for now I'll settle for this scheme). I made an unbound
form (I was earlier told to use an unbound form) called frm_G which
displays all the desired fields and has MyListBox which does indeed
display the Genotypes to be picked. But here's what doesn't work: I
highlight the Genotype and hit Enter (which the book said to do) and
nothing happens. It seems I'm almost there -- I just need an event or
something so that the selected Genotype will go to the query and display
all those records I want.

So do you think I'm really close or far away?

Rob

 

[tina]

I made an unbound

form (I was earlier told to use an unbound form) called frm_G which
displays all the desired fields and has MyListBox which does indeed
display the Genotypes to be picked.

yes, i think you are close. when you say frm_G "displays all the desired
fields", i'm assuming you mean all the desired fields from the main table.
and i also assume you included those desired fields in your query. but as
long as the form itself is unbound, you won't see the records from the
underlying query, so we need to fix that. the following solution is based on
the above assumptions:

bind frm_G to the query, which i'll call qryData for my explanation here.
to bind, open the form in design view and press F4. in the Properties box,
click the Data tab. on the RecordSource line, enter qryData - the "real
name" of your query will be available from the droplist on that line.
each control on the form also needs to be bound to a field in qryData. click
on each control, then go to the Data tab again, ControlSource line, and
choose a field name from the droplist. each control will have its' control
source set to only one field in qryData.
since the Genotype list box is on the same form, it will be easier to filter
the records after the form is open, rather than before. to do this:
open the query in design view. remove the criteria in the Genotype column.
save and close the query. open the form in design view. on the menu bar,
click View, Code; the VB Editor window will open. there may be code there
already. if there is, go to the bottom of the existing code. paste the
following:

Private Sub MyListBox_AfterUpdate()

Me.Filter = "[Genotype] = '" & Me!MyListBox & "'"
Me.FilterOn = True

End Sub

if your list box control is *not* named MyListBox, then substitute the
correct name in the "Private..." line above, and in the "Me.Filter = ..."
line, also.
if the genotype field in qryData is *not* named Genotype, then substitute
the correct name in the "Me.Filter = ..." line.
also, the above code assumes that the Genotype field in qryData, and the
BoundColumn in the listbox, are data type Text. if those fields are not text
but numeric, then use this code instead

Private Sub MyListBox_AfterUpdate()

Me.Filter = "[Genotype] = " & Me!MyListBox
Me.FilterOn = True

End Sub

again, make the name corrections i explained above.

okay, hopefully this will get your form running and filtering records as you
want. as an aside, you should have a relationship between your main table
and the Genotype list table - but i don't think i want to tackle explaining
that here, having already written a "book". if you want an example of same,
let me know and i'll send you one in a small database.

hth

 

[1.156]

Tina, thanks a lot for taking the time to help me.

Things are looking much better now. By the way, F4 doesn't give
properties (it does nothing) but I presume I got the same thing done by
highlighting the form and right clicking to get Properties. I bound the
form to the query and I think that's working. I removed the criteria
line in the query and pasted the filter code you gave into the form.
Now I run the form and the listbox shows the right list for selection
with the top one highlighted. And, the first record's fields
corresponding to that top selection are correctly displayed in their
corresponding text boxes. Bumping the little arrow down at the bottom
cycles thru all the records corresponding to that selection - great.
But, when I try to select a different entry in the Listbox, I get a beep
and nothing else happens. I want to be able to make other selections
and see the corresponding records for them. So apparently I've left
something out.

I hate to try your patience but I think I'm very close; can you suggest
something to fix this?

By the way, I think I do have that relationship (you mention at the
bottom of your response) between the two tables set in the query. Heres
the SQL for my query now:

SELECT tbl_Genotype_Name.[*Genotype],
[tbl_Mouse_Samples_Master-1].[*Mouse_ID],
[tbl_Mouse_Samples_Master-1].[*Slides],
[tbl_Mouse_Samples_Master-1].Slide_Prep,
[tbl_Mouse_Samples_Master-1].Slide_Location,
[tbl_Mouse_Samples_Master-1].[*Depositor]
FROM tbl_Genotype_Name INNER JOIN [tbl_Mouse_Samples_Master-1] ON
tbl_Genotype_Name.[*Genotype] = [tbl_Mouse_Samples_Master-1].[*Genotype];

tbl_Mouse_Samples_Master-1 is my main data table and
tbl_Genotype_Name is the table I made that contains all the unique
*Genotype entries I want to select on. (I use *Genotype for the field
name to indicate it MUST be entered in the main table -- not all fields
must be populated when data is entered.)

Thanks again, Rob

I made an unbound
form (I was earlier told to use an unbound form) called frm_G which
displays all the desired fields and has MyListBox which does indeed
display the Genotypes to be picked.

yes, i think you are close. when you say frm_G "displays all the desired
fields", i'm assuming you mean all the desired fields from the main table.
and i also assume you included those desired fields in your query. but as
long as the form itself is unbound, you won't see the records from the
underlying query, so we need to fix that. the following solution is based on
the above assumptions:

bind frm_G to the query, which i'll call qryData for my explanation here.
to bind, open the form in design view and press F4. in the Properties box,
click the Data tab. on the RecordSource line, enter qryData - the "real
name" of your query will be available from the droplist on that line.
each control on the form also needs to be bound to a field in qryData. click
on each control, then go to the Data tab again, ControlSource line, and
choose a field name from the droplist. each control will have its' control
source set to only one field in qryData.
since the Genotype list box is on the same form, it will be easier to filter
the records after the form is open, rather than before. to do this:
open the query in design view. remove the criteria in the Genotype column.
save and close the query. open the form in design view. on the menu bar,
click View, Code; the VB Editor window will open. there may be code there
already. if there is, go to the bottom of the existing code. paste the
following:

Private Sub MyListBox_AfterUpdate()

Me.Filter = "[Genotype] = '" & Me!MyListBox & "'"
Me.FilterOn = True

End Sub

if your list box control is *not* named MyListBox, then substitute the
correct name in the "Private..." line above, and in the "Me.Filter = ..."
line, also.
if the genotype field in qryData is *not* named Genotype, then substitute
the correct name in the "Me.Filter = ..." line.
also, the above code assumes that the Genotype field in qryData, and the
BoundColumn in the listbox, are data type Text. if those fields are not text
but numeric, then use this code instead

Private Sub MyListBox_AfterUpdate()

Me.Filter = "[Genotype] = " & Me!MyListBox
Me.FilterOn = True

End Sub

again, make the name corrections i explained above.

okay, hopefully this will get your form running and filtering records as you
want. as an aside, you should have a relationship between your main table
and the Genotype list table - but i don't think i want to tackle explaining
that here, having already written a "book". if you want an example of same,
let me know and i'll send you one in a small database.

hth

Tina, thanks for trying to help again.

Yes, I have two tables. The main one has lots of fields, including
Genotype. I have a working query which asks for a Genotype but you
have to type it into a window, and it can be misspelled, etc. The user
types in the Genotype and then sees a bunch of records of that Genotype.

But I want to expand this so that instead of typing the Genotype, the
user can select it from a list. I built another table called
tbl_Genotype to contain a list of all the possible Genotypes (later, I'd
like to build this table from the Main one because this list could
expand - but for now I'll settle for this scheme). I made an unbound
form (I was earlier told to use an unbound form) called frm_G which
displays all the desired fields and has MyListBox which does indeed
display the Genotypes to be picked. But here's what doesn't work: I
highlight the Genotype and hit Enter (which the book said to do) and
nothing happens. It seems I'm almost there -- I just need an event or
something so that the selected Genotype will go to the query and display
all those records I want.

So do you think I'm really close or far away?

Rob

tina wrote:


you

to

Genotype

form

then

in

before

starting

get

 

[tina]

well, i'm glad it's at least partly working!
the way you opened the Properties box was fine, by the way - as long as it
gets open, that's all that matters.
as for the "beep and nothing happens" on the second attempt to use the
listbox, i'm stumped. it *should* work (i hate when that happens... <g>). if
your db is A97 or newer, i'll take a look and try to figure out the problem
if you want to send me a copy. or maybe somebody else will pick up on this
thread and have an idea.
if you decide to send your db, make a copy and do the following to the copy:
1. remove proprietary data and enter a few "dummy" records.
2. compact the db.
3. zip to under 1 MB.
4. refer to the newsgroups in the email subject line, and send to me at
ttaccKILLALLSPAMess1 at yahoo dot com
removing all the CAPITAL letters.

Tina, thanks a lot for taking the time to help me.

Things are looking much better now. By the way, F4 doesn't give
properties (it does nothing) but I presume I got the same thing done by
highlighting the form and right clicking to get Properties. I bound the
form to the query and I think that's working. I removed the criteria
line in the query and pasted the filter code you gave into the form.
Now I run the form and the listbox shows the right list for selection
with the top one highlighted. And, the first record's fields
corresponding to that top selection are correctly displayed in their
corresponding text boxes. Bumping the little arrow down at the bottom
cycles thru all the records corresponding to that selection - great.
But, when I try to select a different entry in the Listbox, I get a beep
and nothing else happens. I want to be able to make other selections
and see the corresponding records for them. So apparently I've left
something out.

I hate to try your patience but I think I'm very close; can you suggest
something to fix this?

By the way, I think I do have that relationship (you mention at the
bottom of your response) between the two tables set in the query. Heres
the SQL for my query now:

SELECT tbl_Genotype_Name.[*Genotype],
[tbl_Mouse_Samples_Master-1].[*Mouse_ID],
[tbl_Mouse_Samples_Master-1].[*Slides],
[tbl_Mouse_Samples_Master-1].Slide_Prep,
[tbl_Mouse_Samples_Master-1].Slide_Location,
[tbl_Mouse_Samples_Master-1].[*Depositor]
FROM tbl_Genotype_Name INNER JOIN [tbl_Mouse_Samples_Master-1] ON
tbl_Genotype_Name.[*Genotype] = [tbl_Mouse_Samples_Master-1].[*Genotype];

tbl_Mouse_Samples_Master-1 is my main data table and
tbl_Genotype_Name is the table I made that contains all the unique
*Genotype entries I want to select on. (I use *Genotype for the field
name to indicate it MUST be entered in the main table -- not all fields
must be populated when data is entered.)

Thanks again, Rob

I made an unbound
form (I was earlier told to use an unbound form) called frm_G which
displays all the desired fields and has MyListBox which does indeed
display the Genotypes to be picked.

yes, i think you are close. when you say frm_G "displays all the desired
fields", i'm assuming you mean all the desired fields from the main table.
and i also assume you included those desired fields in your query. but as
long as the form itself is unbound, you won't see the records from the
underlying query, so we need to fix that. the following solution is based on
the above assumptions:

bind frm_G to the query, which i'll call qryData for my explanation here.
to bind, open the form in design view and press F4. in the Properties box,
click the Data tab. on the RecordSource line, enter qryData - the "real
name" of your query will be available from the droplist on that line.
each control on the form also needs to be bound to a field in qryData. click
on each control, then go to the Data tab again, ControlSource line, and
choose a field name from the droplist. each control will have its' control
source set to only one field in qryData.
since the Genotype list box is on the same form, it will be easier to filter
the records after the form is open, rather than before. to do this:
open the query in design view. remove the criteria in the Genotype column.
save and close the query. open the form in design view. on the menu bar,
click View, Code; the VB Editor window will open. there may be code there
already. if there is, go to the bottom of the existing code. paste the
following:

Private Sub MyListBox_AfterUpdate()

Me.Filter = "[Genotype] = '" & Me!MyListBox & "'"
Me.FilterOn = True

End Sub

if your list box control is *not* named MyListBox, then substitute the
correct name in the "Private..." line above, and in the "Me.Filter = ..."
line, also.
if the genotype field in qryData is *not* named Genotype, then substitute
the correct name in the "Me.Filter = ..." line.
also, the above code assumes that the Genotype field in qryData, and the
BoundColumn in the listbox, are data type Text. if those fields are not text
but numeric, then use this code instead

Private Sub MyListBox_AfterUpdate()

Me.Filter = "[Genotype] = " & Me!MyListBox
Me.FilterOn = True

End Sub

again, make the name corrections i explained above.

okay, hopefully this will get your form running and filtering records as you
want. as an aside, you should have a relationship between your main table
and the Genotype list table - but i don't think i want to tackle explaining
that here, having already written a "book". if you want an example of same,
let me know and i'll send you one in a small database.

hth

Tina, thanks for trying to help again.

Yes, I have two tables. The main one has lots of fields, including
Genotype. I have a working query which asks for a Genotype but you
have to type it into a window, and it can be misspelled, etc. The user
types in the Genotype and then sees a bunch of records of that Genotype.

But I want to expand this so that instead of typing the Genotype, the
user can select it from a list. I built another table called
tbl_Genotype to contain a list of all the possible Genotypes (later, I'd
like to build this table from the Main one because this list could
expand - but for now I'll settle for this scheme). I made an unbound
form (I was earlier told to use an unbound form) called frm_G which
displays all the desired fields and has MyListBox which does indeed
display the Genotypes to be picked. But here's what doesn't work: I
highlight the Genotype and hit Enter (which the book said to do) and
nothing happens. It seems I'm almost there -- I just need an event or
something so that the selected Genotype will go to the query and display
all those records I want.

So do you think I'm really close or far away?

Rob

tina wrote:



i believe i can answer that, but i need a clear picture of your setup.

you


have a table of records that includes a field for Genotype, and you want

to


be able to look at a subset of those records, based on a specific

Genotype


that you choose. correct?
okay, now. is that table bound to a form? *if not, suggest you build a

form


to show the records - the rule of thumb is to view/modify data via a form
that is bound to the records, not in the table directly.*
if the table *is* bound to a form, is that the same form you have the
listbox on?
if yes, do you want to open the form with all the records showing, and

then


choose a Genotype in the listbox to filter the records?
or do you want to open the form as "blank", and *then* choose a Genotype

in


the list box to show the specific records?
you have to decide *how* you want to filter and display your records,

before


we can suggest ways to do it.

hth
p.s.




Amazingly, after several months I still can't
make a simple query work.




Access is a great tool, but it can be very frustrating when you're

starting


out - it's much harder to learn to use than other common Office software,
because you have to understand some theory rather than just opening the
software and start typing. and we've all gone thru it - it really does

get


better.
if you haven't taken a class, or gotten a good basic reference manual, i
think it would really help you a lot to do either - or both.






I finally pushed the right button and my form now actually displays a
listbox with the proper things to select from. But nothing happens when
I click on or highlight my selection. It appears that I need an action


from the click event from MyListBox


In my query, I have Forms!MyForm!MyListBox in the Criteria line. If
this is correct, can someone suggest an event to assign to MyListBox? I
think this is all I need to get this simple query to work.

Thanks, Rob

1.156 wrote:





Fred and Tina were kind enough to try to help in an earlier thread but
I'm still getting nowhere.

I have a simple query that works fine but the user must type in a
"Genotype" to display all the records which match this genotype field
in the main table. I got all this working from the query wizzard.
What I need is to add a ListBox (I don't need a ComboBox I believe --
am I right?) which displays all the available genotypes (available in
a separate, presently unused table called Genotype) so the user can
first select a Genotype from the list and then get the same display of
all the records with this Genotype.

I'm following a thick reference book on the subject and I've made a
form and added a ListBox to it (unbound, I'm told to use). Problem is
that nowhere have I found a clue on how to incorporate this form
containing the listbox and fields into the query. I've looked
everywhere in the query parts of the book and even tho this info must
be there, I haven't stumbled into it.

Could someone give me some words which hint as to what steps I should
be trying to get this form accepted by the query?

Thanks, Rob