Getting text from a text box

[Ken->]

I have a subform with a bunch of text boxes that are filled in from a query
and I want to click on one of them and get the text in it to use in another
query. It took forever for me to realize that I can't do this with a
listbox because the click was never seen so I switched to using textboxes.
I'm still sort of clueless about how they all got filled in and nicely
arranged with just the right number of them and all. Can I make a macro
that runs on the click event that gets the text from the box that was
clicked on? After I do that I want to hide the subform and make another one
visible and fill it in with a query using the text as a condition. This
seem like something that everyone would want to do sooner or later. Please
help a novice.
Thanks
Ken

 

[Guest]

I'm a little puzzled by your question. The short answer is yes, what you
want to do is probably possible, but I'm not sure which of two main
possibilities you are going for. You spoke of clicking on a list box and
getting the text to use for a query. Were you trying to select a record
based on your list box selection, or do you mean to run a query (or a report
based on a query) with the contents of a text box as the criteria?
More information is needed. What is the purpose of the database? How is it
set up (tables, relationships, etc.)? It sounds like maybe you used autoform
to get a form with the text boxes lined up and filled in, but where did the
data come from in the first place? Can you give an example of information in
one subform that would be used for another one?

 

[Ken->]

The goal is a database of artists and their discipline for the area around
here and we want to be able to find for example the artists in Bradley
county who are dancers and perform jazz dance.
So the database has three tables, one is the artists with their address,
phone numbers etc.]
The second table has three fields with an ID, the discipline and the
subdiscipline.
The third table is a junction table with the id of an artist and the id of a
subdiscipline (the discipline comes with it).
I created the query to fill in the subform with the disciplines by using the
find duplicates wizard and changing the criteria from >1 to >0 so that I get
a list of all of the disciplines without having all of the subdisciplines
cluttering up the works. This is for entering the data. The person
entering the data would fill in the artists address information and then
select from one of the disciplines and then I want to present just the
subdisciplines that go with that disciplines and then add a record to the
junction table to connect the artist with that subdiscipline. I would like
to have a multiple select so the operator could select all of the
subdisciplines at once and add a record for each. Then the operator could
select another discipline and subdiscipline as needed.
The table of disciplines and subdisciplines will be entered first and we
anticipate that it will be pretty much static.
Now when it comes time to get a report like I mentioned at the beginning
where we want the artists who live in a particular county and have a
particular discipline/subdiscipline I haven't decided if I need to do the
double select of discipline first and then subdiscipline or whether I should
just present the whole table and let the operator do a multi select to get
all of the subdisciplines at once.
Of course I haven't thought much beyond the immediate problem of getting the
result of the first click on the discipline to use to create a query to
present the subdisciplines. This sounds like it would be one of the most
common kinds of databases but I haven't seen an example yet.
I believe I used the wizard to create the subform and I have to show it in
datasheet view to see all of the records. I wrote a macro that just puts up
a message box and attached that macro to the click event of the text box in
the subform and the macro executes so at least the click is being seen.
So, it is the second possibility you spoke of - I want to run a query with
the contents of the text box as a criteria. Of course I will ask how to do
that next after I try on my own and don't figure it out.
Thanks,
Ken

 

[Guest]

If each artist may have many disciplines, and each discipline may be
associated with many artists, that is a many-to-many relationship, and a
junction table is needed to resolve that relationship. I think it would go
something like this:

tblArtist
ArtistID (primary key, or PK)
First
Last

tblDiscipline
DisciplineID (PK)
Discipline

tblListing (junction table)
ListingID (PK)
DisciplineID (foreign key, or FK)
ArtistID (FK)

Start by setting up the relationships (Tools > Relationships) by dragging
PKs on top of FKs in the Relationships window.
I expect that a form based on tblArtist is the main form; a form based on
tblListing is the subform. DisciplineID in tblListing is populated by a
combo box based on tblDiscipline.
If on the other hand each artist may have only one main discipline, the
relationships will be set up differently. If a subdiscipline may be
associated with several disciplines (e.g. jazz could be both music and
dance), that is a different matter than if a subdiscipline may be associated
with just one discipline.
Sorry that this is not more precise, but there are a lot of ways this could
be set up depending on the nature of the relationships. Once it is set up
your other requirements can be managed.
I will be pretty busy between now and Thanksgiving. If you post more
questions I will try to respond, but if some while goes by without a response
you may want to consider starting a new thread.

The goal is a database of artists and their discipline for the area around
here and we want to be able to find for example the artists in Bradley
county who are dancers and perform jazz dance.
So the database has three tables, one is the artists with their address,
phone numbers etc.]
The second table has three fields with an ID, the discipline and the
subdiscipline.
The third table is a junction table with the id of an artist and the id of a
subdiscipline (the discipline comes with it).
I created the query to fill in the subform with the disciplines by using the
find duplicates wizard and changing the criteria from >1 to >0 so that I get
a list of all of the disciplines without having all of the subdisciplines
cluttering up the works. This is for entering the data. The person
entering the data would fill in the artists address information and then
select from one of the disciplines and then I want to present just the
subdisciplines that go with that disciplines and then add a record to the
junction table to connect the artist with that subdiscipline. I would like
to have a multiple select so the operator could select all of the
subdisciplines at once and add a record for each. Then the operator could
select another discipline and subdiscipline as needed.
The table of disciplines and subdisciplines will be entered first and we
anticipate that it will be pretty much static.
Now when it comes time to get a report like I mentioned at the beginning
where we want the artists who live in a particular county and have a
particular discipline/subdiscipline I haven't decided if I need to do the
double select of discipline first and then subdiscipline or whether I should
just present the whole table and let the operator do a multi select to get
all of the subdisciplines at once.
Of course I haven't thought much beyond the immediate problem of getting the
result of the first click on the discipline to use to create a query to
present the subdisciplines. This sounds like it would be one of the most
common kinds of databases but I haven't seen an example yet.
I believe I used the wizard to create the subform and I have to show it in
datasheet view to see all of the records. I wrote a macro that just puts up
a message box and attached that macro to the click event of the text box in
the subform and the macro executes so at least the click is being seen.
So, it is the second possibility you spoke of - I want to run a query with
the contents of the text box as a criteria. Of course I will ask how to do
that next after I try on my own and don't figure it out.
Thanks,
Ken

I'm a little puzzled by your question. The short answer is yes, what you
want to do is probably possible, but I'm not sure which of two main
possibilities you are going for. You spoke of clicking on a list box and
getting the text to use for a query. Were you trying to select a record
based on your list box selection, or do you mean to run a query (or a report
based on a query) with the contents of a text box as the criteria?
More information is needed. What is the purpose of the database? How is it
set up (tables, relationships, etc.)? It sounds like maybe you used autoform
to get a form with the text boxes lined up and filled in, but where did the
data come from in the first place? Can you give an example of information in
one subform that would be used for another one?

 

[Ken->]

Bruce,
I have it set up just like you say except in the Discipline table there is
also a subdiscipline field so that there are many duplicates in the
discipline field and also duplicates in the subdiscipline field but no
duplicate discipline/subdiscipline combinations. But that is really beside
the point.
The real question is that I have populated a list from a query of the
discipline table without regard to how it is related to anything else and I
get the list of disciplines (the duplicates have been removed by the query).
What I am trying to find out how to do is pick one discipline out of that
list to use as a criteria for another query.
Thanks,
Ken

If each artist may have many disciplines, and each discipline may be
associated with many artists, that is a many-to-many relationship, and a
junction table is needed to resolve that relationship. I think it would go
something like this:

tblArtist
ArtistID (primary key, or PK)
First
Last

tblDiscipline
DisciplineID (PK)
Discipline

tblListing (junction table)
ListingID (PK)
DisciplineID (foreign key, or FK)
ArtistID (FK)

Start by setting up the relationships (Tools > Relationships) by dragging
PKs on top of FKs in the Relationships window.
I expect that a form based on tblArtist is the main form; a form based on
tblListing is the subform. DisciplineID in tblListing is populated by a
combo box based on tblDiscipline.
If on the other hand each artist may have only one main discipline, the
relationships will be set up differently. If a subdiscipline may be
associated with several disciplines (e.g. jazz could be both music and
dance), that is a different matter than if a subdiscipline may be associated
with just one discipline.
Sorry that this is not more precise, but there are a lot of ways this could
be set up depending on the nature of the relationships. Once it is set up
your other requirements can be managed.
I will be pretty busy between now and Thanksgiving. If you post more
questions I will try to respond, but if some while goes by without a response
you may want to consider starting a new thread.

The goal is a database of artists and their discipline for the area around
here and we want to be able to find for example the artists in Bradley
county who are dancers and perform jazz dance.
So the database has three tables, one is the artists with their address,
phone numbers etc.]
The second table has three fields with an ID, the discipline and the
subdiscipline.
The third table is a junction table with the id of an artist and the id of a
subdiscipline (the discipline comes with it).
I created the query to fill in the subform with the disciplines by using the
find duplicates wizard and changing the criteria from >1 to >0 so that I get
a list of all of the disciplines without having all of the subdisciplines
cluttering up the works. This is for entering the data. The person
entering the data would fill in the artists address information and then
select from one of the disciplines and then I want to present just the
subdisciplines that go with that disciplines and then add a record to the
junction table to connect the artist with that subdiscipline. I would like
to have a multiple select so the operator could select all of the
subdisciplines at once and add a record for each. Then the operator could
select another discipline and subdiscipline as needed.
The table of disciplines and subdisciplines will be entered first and we
anticipate that it will be pretty much static.
Now when it comes time to get a report like I mentioned at the beginning
where we want the artists who live in a particular county and have a
particular discipline/subdiscipline I haven't decided if I need to do the
double select of discipline first and then subdiscipline or whether I should
just present the whole table and let the operator do a multi select to get
all of the subdisciplines at once.
Of course I haven't thought much beyond the immediate problem of getting the
result of the first click on the discipline to use to create a query to
present the subdisciplines. This sounds like it would be one of the most
common kinds of databases but I haven't seen an example yet.
I believe I used the wizard to create the subform and I have to show it in
datasheet view to see all of the records. I wrote a macro that just puts up
a message box and attached that macro to the click event of the text box in
the subform and the macro executes so at least the click is being seen.
So, it is the second possibility you spoke of - I want to run a query with
the contents of the text box as a criteria. Of course I will ask how to do
that next after I try on my own and don't figure it out.
Thanks,
Ken

I'm a little puzzled by your question. The short answer is yes, what you
want to do is probably possible, but I'm not sure which of two main
possibilities you are going for. You spoke of clicking on a list box and
getting the text to use for a query. Were you trying to select a record
based on your list box selection, or do you mean to run a query (or a report
based on a query) with the contents of a text box as the criteria?
More information is needed. What is the purpose of the database? How

is
it

set up (tables, relationships, etc.)? It sounds like maybe you used autoform
to get a form with the text boxes lined up and filled in, but where

did
the

data come from in the first place? Can you give an example of

information
in

one subform that would be used for another one?



:

I have a subform with a bunch of text boxes that are filled in from

a
query

and I want to click on one of them and get the text in it to use in another
query. It took forever for me to realize that I can't do this with a
listbox because the click was never seen so I switched to using textboxes.
I'm still sort of clueless about how they all got filled in and nicely
arranged with just the right number of them and all. Can I make a macro
that runs on the click event that gets the text from the box that was
clicked on? After I do that I want to hide the subform and make

another
one

visible and fill it in with a query using the text as a condition. This
seem like something that everyone would want to do sooner or later. Please
help a novice.
Thanks
Ken