Long story short, I basically have a database within a
database. I was running a Select Query via code due the
fact that I have two list boxes on my form.
lstCurrentMembers list the current members of
the "Subdatabase" and the lstMembers lists those members
of the "Main Database".
I have it set up so that the user can select members from
lstMembers (i.e. Main Database) and they will be added to
the Subdatabase. Then I set up a text box so that the
user can search the lstCurrentMembers for individual
members by typing in a name. The problem that I ran into
was that when the search was run, it was showing members
of the Main Database, NOT the Subdatabase. Hence, it
would show the members of the "Main", even though they
were not in the "Sub".
For example, John Doe is a member of "Main" but not a
member of "Sub". When the search was executed, he would
show up as a member of "Sub" in lstCurrentMembers.
The code that I was requesting was used (in addition to
other lines of code that made the search function work) in
the search field, to join the two tables so that only the
members of "Sub" would show.
That is why I was running a SQL Query inside of code.
Whew! Hope this makes some sence.
Thanks again for all of your help.
MC
-----Original Message-----
While we may post SQL here, most of us would recommend that you create this,
as any other query, using the Query Builder. That will be dead-dog-easy. You
_could_ add both tables to the data sources for the Query, joining on Member
Number, and drag down Member Number from tblOne and LastName from tblTwo.
But, if the only thing you are getting from tblOne is the MemberNumber, you
can just eliminate tblOne from the Query and get both the MemberNumber and
LastName from tblTwo.
Why are you running a Select Query from code? That's generally not a good
practice in a developed application, because datasheet view gives your users
too many opportunities to accidentally do things that can be problematical.
It's much better, as a practical matter, to show them only your Forms and
Reports.
Larry Linson
Microsoft Access MVP
Hello.
I am trying to intergrate a line of code to be mine own,
however I am not sure what the syntax is. The original
code looks as follows:
lstCurrentMembers.RowSource = "SELECT
[MemberNumber],LastName FROM tblTwo " & strCriteria
& "ORDER BY LastName"
However, I want the code to use the query that looks as
follows:
Field MemberNumber LastName
--------------------------------------
Table tblOne tblTwo
The Field 'MemberNumber' is the primary key field that
joins the two tables.
How do I write the line of code that will perform this
query?
Thanks in advance,
MC
.
)