Still Struggling...

[BruceM]

A bit more?

You then probably thought if you put it in tblEmployees, you would end up
with an >empty field in the Substitute teacher records, so you put it in
tblSiteEmp.

Hmm...I think you're giving me too much credit although we're all aware of
my dislike for empty fields. :-(

2) tblSiteEmp exists for one reason only. To store address information for
your >full time staff.

What?! When did this happen? I thought it was to store info that didn't
pertain to subs (addresses and emergency info). BTW Bruce, that's how we
ended up with tblSiteEmp because Subs aren't obligated to nor will they
disclose their address or any emergency info. I will break out in hives if I
have all of those empty fields (approx. 14-16) for *every* sub record. :-(
Just so you both understand, we aren't talking about 1 or 2 subs per day
here. We're a large school. We've had as many as 20 sub requests in a single
day; depending on what's going on (training, workshops, conferences,
illness,
etc.). There's always something going on.

For Preferred Subject, just add a field to tblEmployees and have the users
manually enter whatever the preferred subject is. This will add relatively
little to the data entry process, and it will prevent you from having to
add yet another relationship to your db. If you end up with a few records
where the PreferredSubject field is empty, I think that's acceptable.

At this point, so do I; done!

 

[BruceM]

A little more?

As far as the Subs credentials, that would be handled in the same manner
as the full time staff. If they have some type of credential, then there
will
be a record in tblEmpClassifications to reflect that fact. If not, there
won't
be a record. Simple.

???...not following you. I don't track site staff credentials. The district
handles that. As long as subs have a teaching credential, they can sub in
whatever subject they want. My only reason for tracking who has a particular
credential (Special Education, English, Math, etc.) is to handle emergencies
that may come up. Sp. Ed. and Math at least, are special subjects in my
book.
I know the issues Sp. Ed. faces having worked in that dept. many years. You
can't have just any one there. They won't make it. Math is another subject
that needs a specialist so to speak. Every year something happens where we
need a long-term sub. I just wanted to be ready. But I don't understand what
you are proposing; just add a field to tblEmployees?

but I think it's normalized enough that it would be completely functional.

Agreed!

 

[Beetle]

I posted this reply through Access Monster but am attempting to post
it here just to see if it works.

BTW - "Mark", who's real name is Steve, is just a troll that shows up every
now and
then to slum for work, which is against the rules of the group, but he
doesn't care.
Just ignore him.

On to the reply;

Really?! Both tables?

Yes really. Since each table is only allowed one record per employee, then
EmpID will be a unique value for each record in both tables. Therefore it
qualifies as the PK for both tables.

You're killing me here! What?! ...understanding flowing out. Is it
because in the first example it was 1:1 and in this one it's 1:M?

Yes. Different relationship, different rules. In this case EmpID (the PK
of the parent table) can be repeated many times in the child table, so
it does not qualify as the PK of the child ("many") table. The child table
must have it's own unique PK, like PhoneID.

Whoa, *what*?! <trying hard not to cry> This is hard. I needed this
explanation because some things I was doing without really understanding why;

[quoted text clipped - 3 lines]

have a combo PK. That was one of the original tables I had and yet I don't do
the same thing here.


There it is. What I was inadequately trying to explain earlier. I can see
this is where I'm going to get into trouble because I don't really understand
how you would look at that and *know* it means the same title assigned over
and over to the same employee. I'm going to have to get a handle on this;
more homework.

Perhaps it would help your understanding if we take a look at how the data
would actually appear in the table. First, let's look at the table structure
as you had it;

tblTitlesEmps M:M
*************
TitlesEmpsID (PK) Autonumber
EmpID (FK to tblEmps)
TitlesID (FK to tblTitles)

With the above structure, the only value that has uniqueness enforced is
TitlesEmpsID (the PK), unless you put a unique index on EmpID and
TitlesID (which I'm reasonably sure you didn't do in this case). Now, let's
suppose you have an employee named John Smith whose EmpID is 1.
Let's also suppose that your Titles table has a description like
"Economics Professor" and that the TitleID for this description is 5.
There is nothing preventing the data in your junction table from looking
like;

TitlesEmpsID EmpID TitlesID
1 1 5
2 1 5
3 1 5

As you can see, the same employee can have the same title assigned
multiple times, because the only "rule" is that TitlesEmpsID must be
unique for each record.

Now, if we get rid of TitlesEmpsID, and use EmpID and TitlesID as
a combined two field PK then;

EmpID TitlesID
1 5
1 5 <<<<<this would not be allowed
1 3
1 4

With this structure, the same employee can have more than one title
assigned, but never the same one twice, because the combination of
the two values must be unique.

Updated comment: I'm almost afraid to ask because I think I should have this
down since you've already explained but, "describe a persons role within a
Department"? Isn't that the same as EmpTitle?

You might be right. I would have to know a little bit more to say for sure.
From what I do know, it seems like you need;

1) A way to describe what Dept.(s) an employee works in. In this case
tblDepts holds descriptive information about the different departments
an tblEmpDepts (the junction table) controls the relationship.

2) A way to describe what an employee does within each Dept. So the
descriptive information like "Dept. Chair", "Physics Prof.", or
"Custodian"
would be held in? tblTitles? tblSubjects?. Your choice I guess. Then the
relationship would be managed by tblEmpTitles (or whatever you call it).

So maybe it will turn out that tblTitles an tblSubjects are essentially the
same thing. Maybe you can actually get rid of a couple of tables!

???...not following you. I don't track site staff credentials. The district
handles that. As long as subs have a teaching credential, they can sub in

[quoted text clipped - 7 lines]

long-term sub. I just want to be ready. But I don't understand what you are
proposing; just add a field to tblEmployees?

No, I am not proposing that you add any fields at all. Perhaps I can explain
it a little better. Your tblEmployees holds the *names* of all potential
employees, whether they are full time or subs. tblClassifications holds
the descriptions of all possible credential types. tblEmpClassifications
manages the relationship between the two. Therefore, *if* a sub
had some type of credential, and *if* you wanted to store that data in
your application, then a record would be created in tblEmpClassifications
just the same as it would for a full time employee. If you don't want
to track that info, then no record is ever created. No empty fields or
anything to worry about.

 

[John... Visio MVP]

Hmmmm, curious about the names. "rlaird" in the email. "Steve" as a
signature and ""Mark" <[email protected]>" in the from header.

Confused? You soon will be!

rlaird is Roberta Laird who lives with Steve. He has worn out is welcome
with PCDatasheet and Steve, so now he is using his wifes email address. This
really speaks to his credibility if he can not survive using his own name.

John...

 

[BruceM]

I've been away off and on, and haven't been following the whole discussion,
but let me mention a few things about combo boxes. A combo box, as has been
mentioned, can be bound or unbound. A search combo box (to find an employee
record, for instance) is an example of an unbound combo box.
A bound combo box need to get its information from somewhere. One
possibility is a Value List. If you have to select one of the five boroughs
of NYC you could just have a list: Brooklyn; Bronx; Manhattan; Queens;
Staten Island.
For the 50 states you may want to show either the full name or the
two-letter abbreviation, depending on the circumstances. In that case a
lookup table is the best choice. You can have the StateID, StateAbbrev,
StateName. All you would see in the combo box is either the name or the
abbreviation (or both, if you like). In any case, only one value will be
displayed after the selection has been made. Maybe you could do away with
the StateID, and store the abbreviation instead since it is unlikely to
change, but the principle remains the same.
Another option is to build a combo box row source based on values already
storred in the field. If the value you want to use isn't there, type it it.
I don't think there is a use for this approach in your database, but it
comes in handy at times. The Row Source is the field to which the combo box
is bound.
If you store the EmployeeID you can view other Employee information as
needed, at any time. Your EmployeeID is stored in each payroll record, but
no other information about you needs to be stored there. EmployeeID is the
link to the rest of your information.
This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

O.k., so I lost the struggle. Well, maybe not. I won't ask a question,
just
jog your memory and give background info for what I didn't answer before.

Memory jog:

I'm not sure what you mean here. ClassDescription is certainly appropriate
in tblClassifications, and TitleDescription in tblTitles, so I'm not sure
why
you
think this was a mistake. If you tell me what form you are working on and
which
tables are involved I can give you more specific advice, but in the
meantime
here is some general information.

Additional Info:

The reason I said I think I made a mistake there was because I created the
field but naturally, that will not give me the drop down list that I was
looking for. I should have created the combo box. I was working in
frmEmployees. If I kept it the way I had it, I would have to input the
same
info over and over and you know my coaches go for that sort of thing:
There you have it. I'm going to make it out without asking a zillion and
one
questions. Of course if you'd rather...

But the reason we did that is to get rid of tblSubs...remember? You told
me
to use tblEmployees for the fields that are common to both subs and site
employees. Then you said to create a sub form for tblSiteEmps.

To this point we have been talking almost exclusively about tables. I
don't
recall saying much about forms, but I could be wrong. Anyway, having the
tables set up like you do is correct, but that doesn't necessarily mean
that you have to use two separate *forms*. If you are interested in having
the names and addresses on one form, I can try to explain that later, but
if you are happy with how you have it now, then I'll just leave it alone.
Maybe
you don't need to think about anything more right now.

Yeah...about that. Can I ask you a question? I think I made a mistake by
inputting fields for ClassDescription and TitleDescription. Naturally,
there
wasn't a drop-down list.

I'm not sure what you mean here. ClassDescription is certainly appropriate
in tblClassifications, and TitleDescription in tblTitles, so I'm not sure
why
you
think this was a mistake. If you tell me what form you are working on and
which
tables are involved I can give you more specific advice, but in the
meantime
here is some general information.

First, when referring to objects on forms like text boxes, combo boxes,
etc.
they are called controls. Tables and queries have fields, forms and
reports
have controls. Controls have a "Control Source" property. If the control
source
is a field (in a table or query), then the control is bound. If the
control
source is
something other than a field (like a calculation), or if there is no
control
source
(a control does not have to have a "control source") then it is unbound.
So
to make
a bound control, you use a field as the control source. Whether a control
should
be bound or unbound depends on what you are doing. Right now,
it sounds like you are working with the subforms for your employees where
you need to assign the proper Classification and Title, so the control
should
be bound, because that data needs to be stored in the table. Unbound
controls
are typically used to perform searches, do calculations, etc.

Now, in the case of a combo box (or a list box), there will also be a "Row
Source"
property, which basically determines what data, or values, are *displayed*
in
the
combo box. I use the term display loosely, because you can, an in most
cases
would, hide certain values in a combo box so the users don't see them.
This
can
be data from a table or query, or just a list of values that you define
yourself.

"...the fun is really gonna start'? What do you know that I
don't?

I would say you're finding out right about now.

Control Source. Row Source. Bound. Unbound. Master/Child links.
Bound Column. Column Count.

Hey, there's no crying in baseball or relational database design ;- )

[quoted text clipped - 36 lines]

Now that you're ready to try creating some forms, the fun is really
gonna
start :- )

 

[Beetle]

Just for clarification, in one of my previous posts, when I stated the
following;

I was talking specifically about tables, not forms.

Now that you have moved on to creating some forms, it will be helpful
if you can be very specific in describing what you are doing. There is a
bit more terminology when it comes to forms, and it is very easy for us
to misunderstand what you are doing since we are not there to see it.

As I said before, if you are referring to objects on forms, they are called
controls. Also, it helps if you tell us the name of the form (or forms) you
are working with as well as the name of the table(s) or query(s) that
are the recordsource of the form(s). For example, you said you are working
on frmEmployees, but does this form have a subform? If so, are the
controls (combo boxes) you are having trouble with on the main form or
the subform? What is the Control Source and the Row Source of the
combo box(s) you are having trouble with?

Let's start with that, then we can move on to your other issues.
--
_________

Sean Bailey

Hi Bruce,
Thanks for the information. BTW, I see what you mean about starting
additional threads. It seems I did it again...sorry about that. I'll try to
keep it straight.
I'm having a bit of a problem.This happened early last week. I don't know if
you read the post where I stated I didn't get a drop-down list for my combo
box. In the beginning it was unbound but since it didn't work as expected, I
figured it should probably be bound so I changed it. I was trying to get a
drop-down list for tblTitles and tblClassifications. Once I created the bound
box, I received the following error message, "The value you entered isn't
valid for this field. For example you may have entered text in a numeric
field or a # that is larger than the field size." It doesn't highlight any
field in particular.
Strange happenings:
1. When clicking through records, the timing is off maybe half a beat.
You'll see the previous records classification or title after I have moved to
the next record but then it changes.

2. The date created field has the date in the first record only. Subsequent
records have the time.

3. Titles aren't in alpha order. I'm sure it's because I sorted it after I
created the combo box but those changes aren't reflected in the list. I think
it would be easier to find titles if it's in alpha order.

4. I input a default value for State but again, those changes are reflected.
It still shows the original input.

I thought it may have something to do with the combo box since the message
appeared right after but not being sure, I took the following steps:

1. Checked tblEmployee and tblSiteEmp. The only # is the autonumber; all
other fields are text.

2. In tblSiteEmps the Address field is listed as 255 so I changed it to
something reasonable. I know it probably wasn't that but since I was already
there, I took care of it.

3. I deleted both combo boxes and re-created them. Same problem.

4. I checked the input mask for the date created field. As far as I can see,
it's correct. It says short date.

I read a post dated 7/2/08 that had the same error message as mine but that
person had code in the AfterUpdate event and their RowSource info was
different so I didn't think I could adapt their answer to my own problem.
It's probably similar though. What do you think?

This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

<lol> Was I? You're probably right.

I've been away off and on, and haven't been following the whole discussion,
but let me mention a few things about combo boxes. A combo box, as has been
mentioned, can be bound or unbound. A search combo box (to find an employee
record, for instance) is an example of an unbound combo box.
A bound combo box need to get its information from somewhere. One
possibility is a Value List. If you have to select one of the five boroughs
of NYC you could just have a list: Brooklyn; Bronx; Manhattan; Queens;
Staten Island.
For the 50 states you may want to show either the full name or the
two-letter abbreviation, depending on the circumstances. In that case a
lookup table is the best choice. You can have the StateID, StateAbbrev,
StateName. All you would see in the combo box is either the name or the
abbreviation (or both, if you like). In any case, only one value will be
displayed after the selection has been made. Maybe you could do away with
the StateID, and store the abbreviation instead since it is unlikely to
change, but the principle remains the same.
Another option is to build a combo box row source based on values already
storred in the field. If the value you want to use isn't there, type it it.
I don't think there is a use for this approach in your database, but it
comes in handy at times. The Row Source is the field to which the combo box
is bound.
If you store the EmployeeID you can view other Employee information as
needed, at any time. Your EmployeeID is stored in each payroll record, but
no other information about you needs to be stored there. EmployeeID is the
link to the rest of your information.
This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

O.k., so I lost the struggle. Well, maybe not. I won't ask a question,
just

[quoted text clipped - 123 lines]

gonna
start :- )

 

[Aria]

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
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;

[quoted text clipped - 28 lines]

gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Beetle]

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. The junction table should have these fields;

EmpID
TitleID

Now, if you are using the wizard, Access will create text boxes and labels
for each of these fields. I would delete both the text box and the label
for EmpID (it's usually not a good idea to display ID numbers to users) and
the label for TitleID. Then change the TitleID text box to a combo box with
the following properties (if you're not using the wizard, just start with a
combo box to begin with);

Control Source = TitleID (in tblEmpTitles)
Row Source = Select TitleID, TitleDescription From tblTitles OrderBy
TitleDescription
Bound Column = 1
Column Count = 2
Column Widths = 0,1

Now you can manipulate the size an appearance of the subform so that
it appears to be just a group of combo boxes on your form. For example
you might set the width so that it is only as wide as your combo box, and
set the height so that the user could view maybe two or three combo
boxes at once. Other properties of the form you may want to change
might be;

Default View = Continuous
Scroll Bars = Vertical Only
Record Selectors = No
Navigation Buttons = No
Border Style = Thin

This way, when you make a selection in the combo box, the associated ID
numbers will be properly stored in tblEmpTitles, and you have the ability
to assign more than one title to an employee.

--
_________

Sean Bailey

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
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Aria]

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
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Aria]

I'm really sorry, Beetle. You've lost me. I don't think this is turning out
right on my end. I've tried countless times. I don't see anything that says
Record Selectors. I'll try again tomorrow. Right now, I'm working on a
computer that has Access 2000. I don't know if that makes any difference. If
it does, tomorrow, I'll try on a computer with Access 2003.
I also need to check our discussions to see why we have a junction table for
EmpClassifications. There must be a good reason but it escapes me right now.
--
Aria W.

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. The junction table should have these fields;

EmpID
TitleID

Now, if you are using the wizard, Access will create text boxes and labels
for each of these fields. I would delete both the text box and the label
for EmpID (it's usually not a good idea to display ID numbers to users) and
the label for TitleID. Then change the TitleID text box to a combo box with
the following properties (if you're not using the wizard, just start with a
combo box to begin with);

Control Source = TitleID (in tblEmpTitles)
Row Source = Select TitleID, TitleDescription From tblTitles OrderBy
TitleDescription
Bound Column = 1
Column Count = 2
Column Widths = 0,1

Now you can manipulate the size an appearance of the subform so that
it appears to be just a group of combo boxes on your form. For example
you might set the width so that it is only as wide as your combo box, and
set the height so that the user could view maybe two or three combo
boxes at once. Other properties of the form you may want to change
might be;

Default View = Continuous
Scroll Bars = Vertical Only
Record Selectors = No
Navigation Buttons = No
Border Style = Thin

This way, when you make a selection in the combo box, the associated ID
numbers will be properly stored in tblEmpTitles, and you have the ability
to assign more than one title to an employee.

--
_________

Sean Bailey

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
junction
tables for each of them, is that correct?

Aria wrote:
I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[BruceM]

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
junction
tables for each of them, is that correct?

Aria wrote:
I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears
to be
cboTitleDescription. I deleted the combo box a couple of days ago in
trying
to isolate the problem. I saved the form without cboTitleDescription.
When I
opened the form today and cycled through the records,
cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID],
[tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column
Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated
the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Aria]

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
junction
tables for each of them, is that correct?

Aria wrote:
I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears
to be
cboTitleDescription. I deleted the combo box a couple of days ago in
trying
to isolate the problem. I saved the form without cboTitleDescription.
When I
opened the form today and cycled through the records,
cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID],
[tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column
Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated
the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Beetle]

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
junction
tables for each of them, is that correct?

Aria wrote:
I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears
to be
cboTitleDescription. I deleted the combo box a couple of days ago in
trying
to isolate the problem. I saved the form without cboTitleDescription.
When I
opened the form today and cycled through the records,
cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID],
[tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column
Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated
the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Aria]

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
junction
tables for each of them, is that correct?

Aria wrote:
I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears
to be
cboTitleDescription. I deleted the combo box a couple of days ago in
trying
to isolate the problem. I saved the form without cboTitleDescription.
When I
opened the form today and cycled through the records,
cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID],
[tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column
Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated
the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Beetle]

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
junction
tables for each of them, is that correct?

Aria wrote:
I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears
to be
cboTitleDescription. I deleted the combo box a couple of days ago in
trying
to isolate the problem. I saved the form without cboTitleDescription.
When I
opened the form today and cycled through the records,
cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID],
[tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column
Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated
the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Aria]

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
junction
tables for each of them, is that correct?

Aria wrote:
I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears
to be
cboTitleDescription. I deleted the combo box a couple of days ago in
trying
to isolate the problem. I saved the form without cboTitleDescription.
When I
opened the form today and cycled through the records,
cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID],
[tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column
Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated
the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Beetle]

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

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
junction
tables for each of them, is that correct?

Aria wrote:
I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears
to be
cboTitleDescription. I deleted the combo box a couple of days ago in
trying
to isolate the problem. I saved the form without cboTitleDescription.
When I
opened the form today and cycled through the records,
cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID],
[tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column
Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated
the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

Message posted via AccessMonster.com

 

[Aria]

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.

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

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

 

[BruceM]

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.

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.

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

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

 

[Aria]

Maybe I'm not doing much good by jumping in occasionally, but I'm going to

jump in again anyhow.

Thank you so much for your explanation. It was very helpful. Jump in
whenever you can...

Maybe you mean you are including tblTitles in the query

You're correct. When I did the query, I also pulled in tblTitles.

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)

You're right. There is a disconnect. I understand the concept but I can't
visualize how this works. Because I can't "see" it, I'm not "getting" it. My
query made sense to me because it pulled in all the information I needed.
Your "picture this" experiment is helpful. I'm going to have to go through it
a few more times to make sure it sinks in. Thank you so much.