Maybe I'm not doing much good by jumping in occasionally, but I'm going to
jump in again anyhow. These are some observations and comments based on my
reading of the thread.
As I recall, tblTitlesEmps is a junction table comprising TitleID and
EmployeeID fields. A query based on tblTitlesEmps can therefore return only
those fields. A query can only return fields that are in the tables (or
queries) on which the query is based. Maybe I'm stating something you
already know, but there seems to be a disconnect somewhere. I'm not sure
what you mean by "a query based on tblTitlesEmps referencing
TitleDescription". Maybe you mean you are including tblTitles in the query,
but if this is about displaying information on the Titles subform (which is
based on tblTitleEmps), the following about combo boxes may help.
Regarding combo boxes, I suspect a disconnect with the concept of Row Source
and Control Source. Think of it this way (don't do it, but rather just
picture it; this is a thought experiment only): Instead of having a combo
box to select the Title for the Employee you have a text box in which you
input the TitleID (which is a number field, as I recall; the
TitleDescription is a separate field). In order for you to input the
numbers you need a separate list of Titles and their ID numbers. If the
Title is Math Teacher you look on the list and see that the ID for Math
Teacher is 123, so you input 123 into the TitleID text box. In order to see
the text "Math Teacher" in addition to the number 123 you need to add
tblTitles to the form's Record Source query.
Instead of doing all that, your combo box Row Source takes the place of the
printout you needed in order to work with the TitleID text box. The combo
box *stores* TitleID just as happened with the text box, but it *displays*
TitleDescription. The displayed text is a convenience for the user. The
combo box Row Source SQL or stored query means you don't need to include
tblTitles in the subform's Record Source query.
In case you haven't discovered it yet, if your combo box Row Source is
something like:
Select TitleID, TitleDescription From tblTitles Order By TitleDescription
you can click the three dots next to Row Source to view this as a query
design grid. You can also switch to datasheet view to be sure the SQL
produces the expected results. I can't see why the FROM clause isn't
working, but if you switch to design view and datasheet view you may be able
to discover something. BTW, the Row Source SQL doesn't use leading or
closing quotes. I don't know if they are in the postings for clarity or if
they are part of the SQL.
Just to be sure you are clear on the terms SQL and Query, they are the same
thing for purposes of this discussion. SQL is the language behind the
queries. If you are going to use a particular Row Source SQL for combo
boxes on other forms you may want to create and save a query, then used the
named query for the Row Source rather than typing the SQL each time. It
makes no difference to Access which you choose (maybe there's a slight
performance advantage one way or the other, but that is of no concern now).
Again, I may be restating what you already know, but I have lost track of
some of the details of what has been discussed here.
Aria said:
Well on a bright note; cboClassifications is working well. The only thing
it
doesn't say in the SQL statement is Order By ClassDescription. I think
because I ran a query *before* I created the combo box. I'm afraid to
change
it in case I end up with problems in this one too. Right now, I'm really
frustrated with creating this subform. It isn't working and I don't know
how
to fix it.
These are the steps Ive taken:
1. Recordsource=tblTitlesEmps
2. Deleted text box and label for TitleID.
3. Changed label for EmpID to combo box.
4. Set EmpID as the control source.
5. Set Default view=Continuous
6. Unsure of this step since you stated, "...should be
a SQL statement like the one I posted previously, which gets its values
from tblTitles. I input the statement you provided yesterday, "Select
TitleID, TitleDescription From tblTitles Order By TitleDescription
I believe this is the same statement that is working for
cboClassifications.
I think it returned the same message as before; there is a problem with
the
FROM clause. It also wasn't a continuous form even though I had changed
the
default view. Then I started trying a lot of different things like
changing
it to TitleID as in yesterday's post; same message. Then I tried creating
a
query based on tblTitlesEmps referencing TitleDescription. I didn't get
the
FROM message but it didn't return any values. I know I should post the SQL
statement but I deleted it. I deleted everything that didn't return the
value
I need. I know...it isn't helpful. I going to back away from this right
now
and try again tomorrow.
--
Aria W.
Beetle said:
I believe this might be where the problem lies. Currently, it's a form.
I
was trying to make a subform based on your post yesterday, but I was
unsure
exactly what you meant. I didn't understand why this wouldn't be a
subform of
tblEmployees. You mentioned Titles (tblTitles? frmTitles?) but since I
didn't
quite understand what you meant, I tried it both ways to see if I
could get
what I think I need
It *should* be a subform in frmEmployees. It's recordsource should be
tblTitlesEmps. It should have a combo box that has EmpID (from
tblTitlesEmps)
as the control source, and the row source of this combo box should be
a SQL statement like the one I posted previously, which gets its values
from tblTitles.
BTW, I re-read all of our discussions about Titles and Classifications
and
for the life of me cannot figure out why we have this junction table,
EmpsClass. Each employee can only have one classification although many
employees can have the same classification. I think that would be 1:M.
The
only thing I remember discussing concerning dual classifications were
administrators but we were leaving them as is. I'll leave it alone for
now.
In that case, then you need a field in tblEmployees for ClassID as a FK,
since
tblEmployees would be the "many" side of the relationship. Then in your
frmEmployees you would just add a combo box that is bound to this field
(the control source), and the row source would be something like;
Select ClassID, ClassDescription From tblClassifications OrderBy
ClassDescription
Bound Column = 1
Column Count = 2
Column Widths = 0,1
You would not need tblEmpClassifications (the junction table) in this
case.
--
_________
Sean Bailey
Aria said:
frmEmployees
Record Source = tblEmployees
Default View = Single Form
Purpose = enter general info about all employees
Correct (subs and site staff).
sfrmSiteEmps (a subform of frmEmployees)
Record Source = tblSiteEmps
Default View = Single Form
Purpose = enter address and other info related to full time staff
Correct.
frmTitles (which I think is a subform in frmEmployees ?)
Record Source = tblTitlesEmps (the junction table)?
Default View = Continuous ?
Purpose = select the appropriate Title(s) for each employee ?
I believe this might be where the problem lies. Currently, it's a form.
I
was trying to make a subform based on your post yesterday, but I was
unsure
exactly what you meant. I didn't understand why this wouldn't be a
subform of
tblEmployees. You mentioned Titles (tblTitles? frmTitles?) but since I
didn't
quite understand what you meant, I tried it both ways to see if I
could get
what I think I need.
frmClassifications (which I think is also a subform in frmEmployees
?)
Record Source = tblEmpClassifications (the junction table) ?
Default View = Continuous ?
Purpose = select the appropriate Classification(s) for each employee
?
No, this is not a subform. This is a form. The combo box is part of
tblEmployees. I did not experience the error message that I received
with
cboTitles so I left it alone.
BTW, I re-read all of our discussions about Titles and Classifications
and
for the life of me cannot figure out why we have this junction table,
EmpsClass. Each employee can only have one classification although many
employees can have the same classification. I think that would be 1:M.
The
only thing I remember discussing concerning dual classifications were
administrators but we were leaving them as is. I'll leave it alone for
now.
--
Aria W.
:
OK. I would like to make sure I have a clear understanding of the
forms
you currently have. From what I can tell, you have the following;
frmEmployees
Record Source = tblEmployees
Default View = Single Form
Purpose = enter general info about all employees
sfrmSiteEmps (a subform of frmEmployees)
Record Source = tblSiteEmps
Default View = Single Form
Purpose = enter address and other info related to full time staff
frmTitles (which I think is a subform in frmEmployees ?)
Record Source = tblTitlesEmps (the junction table)?
Default View = Continuous ?
Purpose = select the appropriate Title(s) for each employee ?
frmClassifications (which I think is also a subform in frmEmployees
?)
Record Source = tblEmpClassifications (the junction table) ?
Default View = Continuous ?
Purpose = select the appropriate Classification(s) for each employee
?
Is this correct?
--
_________
Sean Bailey
:
OK, so what you need to do is use subforms for this.I'll use
Titles as an
example. The junction table (tblEmpTitles) stores the data that
defines
the relationship, so you add another subform that uses the
junction table as it's recordsource.
I'm sorry; maybe I've misunderstood... There is a combo box for
Class
Description on the form tblEmployees which contains a subform,
tblSiteEmps. I
deleted the combo box for Title Description on this form and
frmTitles. Am I
creating a new subform for frmTitles or frmEmployees?
In design view both scroll bars will appear regardless.
Yes, this is in design view. I can't see the labels and I can't
scroll to
the labels. Even when I try to re size it the result is the same. I
can
possibly delete the text box for EmpID and the label too if I catch
the tip,
but I can't see or reach the label for TitleID to change it into a
combo box
with the horizontal scroll bar in the way. I must be missing
something.
(if you're not using the wizard, just start with a combo box to
begin with);
This is on frmTitles, correct?
Not sure why that would be unless you put the subform inside a
form >that is already continuous. You can't have a continuous
subform inside >another continuous form/subform.
Other properties of the form you may want to change might be;
Default View = Continuous
I assumed you meant for me to set this property for frmTitles,
although I
may be mistaken. As far as I can see, the only one set to
continuous is
frmTitles. The other forms, frmEmployees, frmClassifications and
sfrmSiteEmps
is set to single form.
It may help if you copy and paste your row source SQL.
After I used the wizard to make the combo box it said this:
SELECT [tblTitlesEmps].[EmpID], [tblTitlesEmps].[TitleID] FROM
[tblTitlesEmps]
I then changed it to what you had which was this:
SELECT TitleID, TitleDescription From tblTitles OrderBy
TitleDescription
Hopefully, this is making sense to you.
Not yet, but I'm still trying...
Thanks for your help.
--
Aria W.
:
The horizontal bar stayed even though I had switched it to
vertical only.
Are you talking about in design view or regular form view? In
design view
both scroll bars will appear regardless.
Should I delete the other combo box we originally made. The one
that is
giving the invalid value message?
Yes
1. Form/subform can't be set to continues veiw. Microsoft will
set to single
form.
Not sure why that would be unless you put the subform inside a
form that
is already continuous. You can't have a continuous subform inside
another
continuous form/subform.
2. Syntax error in From clause. I double checked to see if I
had input
"FromtblTitles" correctly. It seem to be correct so I'm not
sure what the
problem is there.
It may help if you copy and paste your row source SQL.
My other question is about the Control Source. I'm supposed to
put "TitleID
(intblTitlesEmps)" So that table(tblTitlesEmps) will be
referenced? I'm just
trying to clarify because I don't see anything else that refers
to
tblTitlesEmps. Or will it be recorded in both places because
TitleID is a
field in both tblTitles and tblTitlesEmps?
Yes. The record source of the subform should be tblTitlesEmps and
the
control source of the combo box should be TitleID from that
table. The
appropriate TitleID from tblTitles (which comes from the *row*
source of
the combo box) will be recorded in tblTitlesEmps (via the
*control* source
of the combo box).
Hopefully, this is making sense to you.
--
_________
Sean Bailey
:
Thanks, Bruce. I finally found it. I'm still working on it but
I think I may
be getting closer. I had to make the combo box because I could
not get the
wizard to give me what I think I need. It created 2 scroll bars
(1 vertical
and 1 horizontal). I couldn't see the labels and couldn't
scroll to the
labels. The horizontal bar stayed even though I had switched it
to vertical
only. Should I delete the other combo box we originally made.
The one that is
giving the invalid value message? It's now giving me a couple
of new messages.
1. Form/subform can't be set to continues veiw. Microsoft will
set to single
form.
2. Syntax error in From clause. I double checked to see if I
had input
"FromtblTitles" correctly. It seem to be correct so I'm not
sure what the
problem is there.
My other question is about the Control Source. I'm supposed to
put "TitleID
(intblTitlesEmps)" So that table(tblTitlesEmps) will be
referenced? I'm just
trying to clarify because I don't see anything else that refers
to
tblTitlesEmps. Or will it be recorded in both places because
TitleID is a
field in both tblTitles and tblTitlesEmps?
--
Aria W.
:
I'm just sort of popping in as time permits, so this will not
be a thorough
reply. Record Selectors is a property on the Format tab of
the form's
Property Sheet. If it is Yes you will see a bar on the left
side of the
window, with an arrow pointing to the current record and an
asterisk for the
new record (in a continuous form). In a single form I think
you just see
the arrow for every record.
The drop-down list isn't in alphabetical order because you
didn't tell it to
be. In Sean's Row Source SQL there is an Order By statement.
This
determines the sort order. If you use a named query as the
Row Source there
is an Order By statement in the query. To see the query's
underlying
language (I don't recall if you know this), open the query
and click View >>
SQL.
Just to stress the point, the combo box has a Row Source,
which determines
the list the user will see when clicking the down arrow. A
combo box is a
control that may be bound to a field, so it may also have a
Control Source,
which is a field in the form's Record Source. The combo box
is not based on
tblTitles, but rather is bound to a single field in
tblTitles. When you
make a selection from the combo box, the bound column in the
combo box Row
Source is written to the field in the table. If EmployeeID
is the (hidden)
bound column, and FullName is the visible column, selecting a
name will
cause the associated EmployeeID to be stored in the combo box
Control Source
field.
I didn't give you all of the information that you requested.
The problem is
on the main form. The recordsource, I believe is tblTitles.
I think I
found
the answer to one of my problems. The drop-list isn't in
alpha order
because
the sort was based on qryTitles, but the combo box is based
on tblTitles.
--
Aria W.
:
Yes, that's correct.
--
Aria W.
:
You may need to refresh my memory a bit here. You had
m:m relationships
for Employees to Titles and Employees to
Classifications, so you have
