Still Struggling...

[Aria]

I've run into a bit of a problem. It didn't work. Please tell me where I'm
going wrong. I input the following into the Afterupdate event:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[LastName, FirstName] = " & Me.cboStaffLookup
Me.Bookmark = rs.Bookmark

or should it say," rs.FindFirst "[LastName], [FirstName] = " &
Me.cboStaffLookup ?

I'm getting the following message:
"Micorsoft Access can't find the macro 'Dim rs As Object
Set rs=Me
The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been save. Note that when you enter the macrogroupname, macroname syntax in
an argument, yu must specify the name the macro's macro group was last saved
under."

So, I went to macros and didn't get beyond "Find Record". I didn't know what
else to do there or if I even needed to be there since it wasn't mentioned.

Yes, look in tblEmployees if that contains the records that are not sorting
properly. Regarding the drop-down arrow, I meant in datasheet view. It
will show up when you click into the field. In Design view, the Lookup tab
at the bottom of the window will show "Combo Box".

I didn't see anything unusual other than what I already mentioned about the
#14 listed under title description. There were no drop-down arrows in
datasheet view nor did the Lookup tab show "Combo Box". It said text box for
Class Description, Title Description, LN, FN and MI. Is this a problem? When
I go to frmEmployees, Class Description is a combo box. Title Description is
a subform.

Your explanation was fine. It answered the question I posed. Thanks.

--
Aria W.

Hi Bruce,
Thanks for responding. It seemed so easy in the example. Why does
everything
have to turn into a problem? I'm sorry, I forgot to include column widths.
I
thought about that much later.

Column widths =0";0.6459";0.625"
List widths=1.2708"

My mistake. It's list width that sets the overall width.

In this case it should be unbound. You aren't trying to store the value,
but rather to go to the selected record.

I thought it should be unbound but I've been wrong so often that now I
automatically think, "You're wrong. It must be the other way."

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Use your actual control and field names, but other than that it should work
as written.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know,
we
can drop it.

Here is a simpler example of using a variable. This is a string (text)
variable:

' This declares that when strMsg appears in the code it is to be treated as
a text string
Dim strMsg as String

' This assigns a value to the string variable we have just declared
strMsg = "You are about to change the value"

' The next two lines of code will produce the same message
MsgBox "You are about to change the value"
MsgBox strMsg

If this code is in the Before Update event for a text box the message "You
are about to change the value" will appear before the value is updated.
Using the variable can be tidier, and simplifies things when you need to use
the same text string several times. In the VBA examples in VBA Help all
values are assigned to variables. I can't say I see the benefit of that,
but it may help to understand what's going on with Help.

In my example, Dim rs As Object declares to the After Update event that rs
is an Object. I can't really describe why RecordsetClone is an Object, nor
can I clearly describe what RecordsetClone is. However, this line of code
tells the After Update event that rs is the same as Me.RecordsetClone:

Set rs = Me.RecordsetClone

3. Me.Bookmark--I've seen that before and have been dying to ask. What
does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

For one thing, it's very different from a Bookmark in Word. This
explanation may be a bit sketchy, but it's the best I can do. Form VBA
Help, a bookmark "uniquely identifies a particular record in the form's
underlying table". This:
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
identifies a particular record in the form's record source. A bookmark is
assigned to each record in the Record Source table or query. RecordsetClone
is a copy of the Recordset, so it has the same bookmarks. RecordsetClone
lets you perform actions such as Find that cannot be performed on the form.
Once the particular record you selected is identified in the copy
(RecordsetClone), Me.Bookmark = rs.Bookmark goes to the record with the same
bookmark as the one that was found in the RecordsetClone.
That's the best I can do with that one.

Again, if you don't want to get into it, that's fine.

As far as the SQL statement, I opened it again in Design view and this is
what it shows:

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in
frmEmployees
with subform SiteEmps, so the table you want me to look in is
tblEmployees?
Just to cover all, I checked both tables. I did not see a drop-down arrow.
I
assume you mean in Design View. There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees
have a
title description (Admin., teacher, etc.). The sixth and final entry shows
a
#...#14. I don't know why. When you open the form, it list the title.

Yes, look in tblEmployees if that contains the records that are not sorting
properly. Regarding the drop-down arrow, I meant in datasheet view. It
will show up when you click into the field. In Design view, the Lookup tab
at the bottom of the window will show "Combo Box". I couldn't say what is
going on with #...#14.

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use
it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.

You can preview the report with something like this:
DoCmd.OpenReport "ReportName", acViewPreview

The menu bar or toolbar can contain the print command or icon.

--
Aria W.

I can't figure why it isn't sorting, although I have one idea I will
address
later. Try this:
SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM
tblEmployees ORDER BY
[LastName], [FirstName];

This doesn't really address your questions in some ways, but it is an
example of how an alias (LastFirst) can be used. You didn't mention
column
widths, but for a three column colmbo box in which the first column is
hidden then would be something like 0";1";1". In my example the column
count would be 2 and the column widths 0";1". From what I can tell the
rightmost visible column will fill the rest of the space in the combo
box.
If there is one visible column it will be the width of the combo box
unless
its width is greater than the combo box width. If the combo box is 2.5"
wide and the visible columns are 1";1" the rightmost column will actually
be
1.5". If the combo box is 1.5" wide and the visible columns are 1";1"
the
rightmost column will hang over the edge of the combo box when you click
the
down arrow.

Anyhow, back to the combo box. In this case it should be unbound. You
aren't trying to store the value, but rather to go to the selected
record.
For this the combo box needs an After Update event something like this
(cboEmployee is the name of the combo box):

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

You could also do it this way:

Me.RecordsetClone.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = Me.RecordsetClone.Bookmark

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.
I
don't want to get too deeply into this, but when you hear about variable
declaration and use, this is one example.

Back to the sorting in the combo box. Open the SQL in Design View and be
sure it is what it should be. Unless there is a lookup field in the
table,
it should work. To see if there is a lookup field, open the table and
see
if any of the fields have a drop-down arrow. If they do, report back
about
it. It needs to be fixed.

Assuming you get the combo box to work as intended, just what action are
you
performing when you print the record? Best choice would be to create a
report (it can be very simple if you like), and to print the report. It
is
an easy enough matter to limit printing the report to a single selected
record.

Apparently there are problems in the Microsoft forum. I hate to break
the
thread (sorry Bruce) but I was wondering if you could help me with
something?
Currently, I'm working with frmEmployees. I want the form to be
user-friendly.
I know there are going to be issues there. There is an example of what
I
want
to do in my book and I followed what they said to do, but of course it
isn't
working for me. In frmEmployees I added a combo box. It's unbound;
maybe
it
should be bound. I did try that but it again only showed the last name.
What
I'm trying to do is create a drop-down list of employee last name,
first
name.
When the user clicks on the name, it goes to that employee record. I
added
a
print record button (I don't know if it works because currently, I'm
stuck
on
this). I see the names in the drop-down but it isn't going to that
record
and
when I click on the name it then only shows the last name.
This is the SQL statement: SELECT [tblEmployees].[EmpID],
[tblEmployees].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as the
control
source but Access didn't like that so I took it out. The Row Source
Type
is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've
looked
at
the query. It looks like what I want. I've also looked at it in
datasheet
view. The names are there but it did not sort ascending like I asked.
What
mistakes have I made and how do I fix it?

Beetle284 wrote:
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.

[quoted text clipped - 15 lines]
gonna
start :- )

 

[Aria]

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post before I
sent off my last one. Boy, do I need to make some changes to what I did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in
his code but I thought I was supposed to put the fields I was interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

The bookmarks are only valid for the period of time that the form is >open. If you close and re-open the form, those same records may have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a >report and then use a command button on your form to print (or print >preview) it.

I did print the form and understand what you're saying. I didn't really care
for the way it looked. The only reports I have right now are relationship
diagrams. I don't have any saved macros either. How hard would it be for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and then under
comments (?) list the above?
--
Aria W.

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what *type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) that
your form is based on.

You can declare anything you want, as long as you declare it properly so
that Access knows what it is. In other words, you could (theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less universally
recognized (by application designers and such) as representing a recordset
of some type.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

It is a placeholder of sorts, but it has nothing to do with the actual data
that
is stored in your table. Everytime you open a bound form, a unique bookmark
is created for each record in that form's recordset. The bookmarks are only
valid for the period of time that the form is open. If you close and re-open
the form, those same records may have a different bookmark assigned.

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

The last field is not shown because it is only used for sorting.

There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14.

When you opened which table?

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a report
and then use a command button on your form to print (or print preview) it.

--
_________

Sean Bailey

Hi Bruce,
Thanks for responding. It seemed so easy in the example. Why does everything
have to turn into a problem? I'm sorry, I forgot to include column widths. I
thought about that much later.

Column widths =0";0.6459";0.625"
List widths=1.2708"

In this case it should be unbound. You aren't trying to store the value, >but rather to go to the selected record.

I thought it should be unbound but I've been wrong so often that now I
automatically think, "You're wrong. It must be the other way."

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

Again, if you don't want to get into it, that's fine.

As far as the SQL statement, I opened it again in Design view and this is
what it shows:

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?
Just to cover all, I checked both tables. I did not see a drop-down arrow. I
assume you mean in Design View. There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14. I don't know why. When you open the form, it list the title.

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.




--
Aria W.

I can't figure why it isn't sorting, although I have one idea I will address
later. Try this:
SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM
tblEmployees ORDER BY
[LastName], [FirstName];

This doesn't really address your questions in some ways, but it is an
example of how an alias (LastFirst) can be used. You didn't mention column
widths, but for a three column colmbo box in which the first column is
hidden then would be something like 0";1";1". In my example the column
count would be 2 and the column widths 0";1". From what I can tell the
rightmost visible column will fill the rest of the space in the combo box.
If there is one visible column it will be the width of the combo box unless
its width is greater than the combo box width. If the combo box is 2.5"
wide and the visible columns are 1";1" the rightmost column will actually be
1.5". If the combo box is 1.5" wide and the visible columns are 1";1" the
rightmost column will hang over the edge of the combo box when you click the
down arrow.

Anyhow, back to the combo box. In this case it should be unbound. You
aren't trying to store the value, but rather to go to the selected record.
For this the combo box needs an After Update event something like this
(cboEmployee is the name of the combo box):

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

You could also do it this way:

Me.RecordsetClone.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = Me.RecordsetClone.Bookmark

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable. I
don't want to get too deeply into this, but when you hear about variable
declaration and use, this is one example.

Back to the sorting in the combo box. Open the SQL in Design View and be
sure it is what it should be. Unless there is a lookup field in the table,
it should work. To see if there is a lookup field, open the table and see
if any of the fields have a drop-down arrow. If they do, report back about
it. It needs to be fixed.

Assuming you get the combo box to work as intended, just what action are you
performing when you print the record? Best choice would be to create a
report (it can be very simple if you like), and to print the report. It is
an easy enough matter to limit printing the report to a single selected
record.

Apparently there are problems in the Microsoft forum. I hate to break the
thread (sorry Bruce) but I was wondering if you could help me with
something?
Currently, I'm working with frmEmployees. I want the form to be
user-friendly.
I know there are going to be issues there. There is an example of what I
want
to do in my book and I followed what they said to do, but of course it
isn't
working for me. In frmEmployees I added a combo box. It's unbound; maybe
it
should be bound. I did try that but it again only showed the last name.
What
I'm trying to do is create a drop-down list of employee last name, first
name.
When the user clicks on the name, it goes to that employee record. I added
a
print record button (I don't know if it works because currently, I'm stuck
on
this). I see the names in the drop-down but it isn't going to that record
and
when I click on the name it then only shows the last name.
This is the SQL statement: SELECT [tblEmployees].[EmpID], [tblEmployees].

 

[BruceM]

I've run into a bit of a problem. It didn't work. Please tell me where I'm
going wrong. I input the following into the Afterupdate event:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[LastName, FirstName] = " & Me.cboStaffLookup
Me.Bookmark = rs.Bookmark

It should read:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup

This assumes the bound column in cboStaffLookup is EmployeeID (or EmpID, if
that is the name of the field). An unbound combo box still has a bound
column. The bound column is the one that Access "sees" when the combo box
is referenced. The Row Source SQL is:
SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM
tblEmployees ORDER BY [LastName], [FirstName];

If you view the SQL in datasheet view you will see that the first column is
EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is the
same as EmpID in the combo box."

Remember, the combo box drop-down list is the equivalent of your
hypothetical printout matching employees with their ID numbers. When you
select a name from the list you are invisibly selecting their number. You
are then taking this number to the full Employee listing and finding the
record in which EmpID is the same as the selected number. As far as the
user is concerned the name is being selected, but Access is quietly using
the ID number instead.

or should it say," rs.FindFirst "[LastName], [FirstName] = " &
Me.cboStaffLookup ?

I'm getting the following message:
"Micorsoft Access can't find the macro 'Dim rs As Object
Set rs=Me
The macro (or its macro group) doesn't exist, or the macro is new but
hasn't
been save. Note that when you enter the macrogroupname, macroname syntax
in
an argument, yu must specify the name the macro's macro group was last
saved
under."

It isn't a macro, but rather an event procedure. To create the event
procedure, right click the combo box, then select Properties. When the
Properties Sheet (the thing with tabs for Format, Data, etc.) shows up,
click the Events tab and click After Update. Click the three dots at the
right, select Code Builder, then click OK. You should see the VBA editor
with the following:

Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code. After you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option Explicit
are under Option Compare Database. Add them if they are not. If they are
not, in the VBA editor click Tools > Options. Click the Editor tab, and
check the box Require Variable Declaration.
Still in the editor, click Debug > Compile. This will highlight any typos
and other such errors in the code.

So, I went to macros and didn't get beyond "Find Record". I didn't know
what
else to do there or if I even needed to be there since it wasn't
mentioned.


I didn't see anything unusual other than what I already mentioned about
the
#14 listed under title description. There were no drop-down arrows in
datasheet view nor did the Lookup tab show "Combo Box". It said text box
for
Class Description, Title Description, LN, FN and MI. Is this a problem?
When
I go to frmEmployees, Class Description is a combo box. Title Description
is
a subform.

Sounds OK. A combo box in a table is *entirely* different from a combo box
on a form. Combo boxes belong on forms, but should not be in tables. I
don't know what is happening with the sort order, but let's not get too many
things cooking on a Friday afternoon.

Your explanation was fine. It answered the question I posed. Thanks.

--
Aria W.

Hi Bruce,
Thanks for responding. It seemed so easy in the example. Why does
everything
have to turn into a problem? I'm sorry, I forgot to include column
widths.
I
thought about that much later.

Column widths =0";0.6459";0.625"
List widths=1.2708"

My mistake. It's list width that sets the overall width.

In this case it should be unbound. You aren't trying to store the
value,
but rather to go to the selected record.

I thought it should be unbound but I've been wrong so often that now I
automatically think, "You're wrong. It must be the other way."


Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I
am
certainly willing to try, if you don't mind the questions that are sure
to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Use your actual control and field names, but other than that it should
work
as written.

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object
variable.

2. Well, maybe I don't need to know, but I was wondering if Access
already
knows that rs is an abbreviation for Me.RecordsetClone because the =
sign
isn't used until the second statement. If it's more than I need to
know,
we
can drop it.

Here is a simpler example of using a variable. This is a string (text)
variable:

' This declares that when strMsg appears in the code it is to be treated
as
a text string
Dim strMsg as String

' This assigns a value to the string variable we have just declared
strMsg = "You are about to change the value"

' The next two lines of code will produce the same message
MsgBox "You are about to change the value"
MsgBox strMsg

If this code is in the Before Update event for a text box the message
"You
are about to change the value" will appear before the value is updated.
Using the variable can be tidier, and simplifies things when you need to
use
the same text string several times. In the VBA examples in VBA Help all
values are assigned to variables. I can't say I see the benefit of that,
but it may help to understand what's going on with Help.

In my example, Dim rs As Object declares to the After Update event that
rs
is an Object. I can't really describe why RecordsetClone is an Object,
nor
can I clearly describe what RecordsetClone is. However, this line of
code
tells the After Update event that rs is the same as Me.RecordsetClone:

Set rs = Me.RecordsetClone

3. Me.Bookmark--I've seen that before and have been dying to ask. What
does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

For one thing, it's very different from a Bookmark in Word. This
explanation may be a bit sketchy, but it's the best I can do. Form VBA
Help, a bookmark "uniquely identifies a particular record in the form's
underlying table". This:
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
identifies a particular record in the form's record source. A bookmark
is
assigned to each record in the Record Source table or query.
RecordsetClone
is a copy of the Recordset, so it has the same bookmarks. RecordsetClone
lets you perform actions such as Find that cannot be performed on the
form.
Once the particular record you selected is identified in the copy
(RecordsetClone), Me.Bookmark = rs.Bookmark goes to the record with the
same
bookmark as the one that was found in the RecordsetClone.
That's the best I can do with that one.

Again, if you don't want to get into it, that's fine.

As far as the SQL statement, I opened it again in Design view and this
is
what it shows:

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in
frmEmployees
with subform SiteEmps, so the table you want me to look in is
tblEmployees?
Just to cover all, I checked both tables. I did not see a drop-down
arrow.
I
assume you mean in Design View. There is something a little odd though.
I
have only input 6 employee names using frmEmployees just so I can check
to
see if things are working. When I opened the table, 5 of the employees
have a
title description (Admin., teacher, etc.). The sixth and final entry
shows
a
#...#14. I don't know why. When you open the form, it list the title.

Yes, look in tblEmployees if that contains the records that are not
sorting
properly. Regarding the drop-down arrow, I meant in datasheet view. It
will show up when you click into the field. In Design view, the Lookup
tab
at the bottom of the window will show "Combo Box". I couldn't say what
is
going on with #...#14.

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will
use
it.
I can hear some of the conversations already. I did think about making
a
report after I created that button. The print button is convenient but
I
think I would rather it go to print preview. I don't know, I'm still
just
trying to get through all of my other issues.

You can preview the report with something like this:
DoCmd.OpenReport "ReportName", acViewPreview

The menu bar or toolbar can contain the print command or icon.

--
Aria W.


:

I can't figure why it isn't sorting, although I have one idea I will
address
later. Try this:
SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM
tblEmployees ORDER BY
[LastName], [FirstName];

This doesn't really address your questions in some ways, but it is an
example of how an alias (LastFirst) can be used. You didn't mention
column
widths, but for a three column colmbo box in which the first column is
hidden then would be something like 0";1";1". In my example the
column
count would be 2 and the column widths 0";1". From what I can tell
the
rightmost visible column will fill the rest of the space in the combo
box.
If there is one visible column it will be the width of the combo box
unless
its width is greater than the combo box width. If the combo box is
2.5"
wide and the visible columns are 1";1" the rightmost column will
actually
be
1.5". If the combo box is 1.5" wide and the visible columns are 1";1"
the
rightmost column will hang over the edge of the combo box when you
click
the
down arrow.

Anyhow, back to the combo box. In this case it should be unbound.
You
aren't trying to store the value, but rather to go to the selected
record.
For this the combo box needs an After Update event something like this
(cboEmployee is the name of the combo box):

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

You could also do it this way:

Me.RecordsetClone.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = Me.RecordsetClone.Bookmark

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object
variable.
I
don't want to get too deeply into this, but when you hear about
variable
declaration and use, this is one example.

Back to the sorting in the combo box. Open the SQL in Design View and
be
sure it is what it should be. Unless there is a lookup field in the
table,
it should work. To see if there is a lookup field, open the table and
see
if any of the fields have a drop-down arrow. If they do, report back
about
it. It needs to be fixed.

Assuming you get the combo box to work as intended, just what action
are
you
performing when you print the record? Best choice would be to create
a
report (it can be very simple if you like), and to print the report.
It
is
an easy enough matter to limit printing the report to a single
selected
record.

Apparently there are problems in the Microsoft forum. I hate to
break
the
thread (sorry Bruce) but I was wondering if you could help me with
something?
Currently, I'm working with frmEmployees. I want the form to be
user-friendly.
I know there are going to be issues there. There is an example of
what
I
want
to do in my book and I followed what they said to do, but of course
it
isn't
working for me. In frmEmployees I added a combo box. It's unbound;
maybe
it
should be bound. I did try that but it again only showed the last
name.
What
I'm trying to do is create a drop-down list of employee last name,
first
name.
When the user clicks on the name, it goes to that employee record. I
added
a
print record button (I don't know if it works because currently, I'm
stuck
on
this). I see the names in the drop-down but it isn't going to that
record
and
when I click on the name it then only shows the last name.
This is the SQL statement: SELECT [tblEmployees].[EmpID],
[tblEmployees].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as
the
control
source but Access didn't like that so I took it out. The Row Source
Type
is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've
looked
at
the query. It looks like what I want. I've also looked at it in
datasheet
view. The names are there but it did not sort ascending like I
asked.
What
mistakes have I made and how do I fix it?

Beetle284 wrote:
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.

[quoted text clipped - 15 lines]
gonna
start :- )

 

[Beetle]

Just ignore my last post...

So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing is just telling Access to create a clone of whatever the
recordset is. You don't need to tell it the name of the table or query.

--
_________

Sean Bailey

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post before I
sent off my last one. Boy, do I need to make some changes to what I did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in
his code but I thought I was supposed to put the fields I was interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

The bookmarks are only valid for the period of time that the form is >open. If you close and re-open the form, those same records may have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a >report and then use a command button on your form to print (or print >preview) it.

I did print the form and understand what you're saying. I didn't really care
for the way it looked. The only reports I have right now are relationship
diagrams. I don't have any saved macros either. How hard would it be for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and then under
comments (?) list the above?
--
Aria W.

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what *type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) that
your form is based on.

You can declare anything you want, as long as you declare it properly so
that Access knows what it is. In other words, you could (theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less universally
recognized (by application designers and such) as representing a recordset
of some type.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

It is a placeholder of sorts, but it has nothing to do with the actual data
that
is stored in your table. Everytime you open a bound form, a unique bookmark
is created for each record in that form's recordset. The bookmarks are only
valid for the period of time that the form is open. If you close and re-open
the form, those same records may have a different bookmark assigned.

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

The last field is not shown because it is only used for sorting.

There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14.

When you opened which table?

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a report
and then use a command button on your form to print (or print preview) it.

--
_________

Sean Bailey

Hi Bruce,
Thanks for responding. It seemed so easy in the example. Why does everything
have to turn into a problem? I'm sorry, I forgot to include column widths. I
thought about that much later.

Column widths =0";0.6459";0.625"
List widths=1.2708"

In this case it should be unbound. You aren't trying to store the value, >but rather to go to the selected record.

I thought it should be unbound but I've been wrong so often that now I
automatically think, "You're wrong. It must be the other way."


Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

Again, if you don't want to get into it, that's fine.

As far as the SQL statement, I opened it again in Design view and this is
what it shows:

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?
Just to cover all, I checked both tables. I did not see a drop-down arrow. I
assume you mean in Design View. There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14. I don't know why. When you open the form, it list the title.

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.




--
Aria W.


:

I can't figure why it isn't sorting, although I have one idea I will address
later. Try this:
SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM
tblEmployees ORDER BY
[LastName], [FirstName];

This doesn't really address your questions in some ways, but it is an
example of how an alias (LastFirst) can be used. You didn't mention column
widths, but for a three column colmbo box in which the first column is
hidden then would be something like 0";1";1". In my example the column
count would be 2 and the column widths 0";1". From what I can tell the
rightmost visible column will fill the rest of the space in the combo box.
If there is one visible column it will be the width of the combo box unless
its width is greater than the combo box width. If the combo box is 2.5"
wide and the visible columns are 1";1" the rightmost column will actually be
1.5". If the combo box is 1.5" wide and the visible columns are 1";1" the
rightmost column will hang over the edge of the combo box when you click the
down arrow.

 

[Beetle]

I accidentally hit post before I was done with my last response. Here is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

What Bruce suggested should work fine.

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

tblEmployees

tblSiteEmps

tblTitles

tblClassifications

tblTitlesEmps

--
_________

Sean Bailey

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post before I
sent off my last one. Boy, do I need to make some changes to what I did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in
his code but I thought I was supposed to put the fields I was interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

The bookmarks are only valid for the period of time that the form is >open. If you close and re-open the form, those same records may have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a >report and then use a command button on your form to print (or print >preview) it.

I did print the form and understand what you're saying. I didn't really care
for the way it looked. The only reports I have right now are relationship
diagrams. I don't have any saved macros either. How hard would it be for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and then under
comments (?) list the above?
--
Aria W.

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what *type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) that
your form is based on.

You can declare anything you want, as long as you declare it properly so
that Access knows what it is. In other words, you could (theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less universally
recognized (by application designers and such) as representing a recordset
of some type.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

It is a placeholder of sorts, but it has nothing to do with the actual data
that
is stored in your table. Everytime you open a bound form, a unique bookmark
is created for each record in that form's recordset. The bookmarks are only
valid for the period of time that the form is open. If you close and re-open
the form, those same records may have a different bookmark assigned.

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

The last field is not shown because it is only used for sorting.

There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14.

When you opened which table?

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a report
and then use a command button on your form to print (or print preview) it.

--
_________

Sean Bailey

Hi Bruce,
Thanks for responding. It seemed so easy in the example. Why does everything
have to turn into a problem? I'm sorry, I forgot to include column widths. I
thought about that much later.

Column widths =0";0.6459";0.625"
List widths=1.2708"

In this case it should be unbound. You aren't trying to store the value, >but rather to go to the selected record.

I thought it should be unbound but I've been wrong so often that now I
automatically think, "You're wrong. It must be the other way."


Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

Again, if you don't want to get into it, that's fine.

As far as the SQL statement, I opened it again in Design view and this is
what it shows:

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?
Just to cover all, I checked both tables. I did not see a drop-down arrow. I
assume you mean in Design View. There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14. I don't know why. When you open the form, it list the title.

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.




--
Aria W.


:

I can't figure why it isn't sorting, although I have one idea I will address
later. Try this:
SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM
tblEmployees ORDER BY
[LastName], [FirstName];

This doesn't really address your questions in some ways, but it is an
example of how an alias (LastFirst) can be used. You didn't mention column
widths, but for a three column colmbo box in which the first column is
hidden then would be something like 0";1";1". In my example the column
count would be 2 and the column widths 0";1". From what I can tell the
rightmost visible column will fill the rest of the space in the combo box.
If there is one visible column it will be the width of the combo box unless
its width is greater than the combo box width. If the combo box is 2.5"
wide and the visible columns are 1";1" the rightmost column will actually be
1.5". If the combo box is 1.5" wide and the visible columns are 1";1" the
rightmost column will hang over the edge of the combo box when you click the
down arrow.

 

[Aria]

Something's not right here. tblEmployees should not have a field for

TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in making sure
everything is OK. Every time you have reservations, there's usually something
amiss. I just want to say, before I post the table structure, that some facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes (temporary).

2. Each employee can only have 1 classification (Admin., Certificated
(teacher et. al), Classified and Substitutes). There are many employees who
have the same classification. tblClassifications 1:M tblEmployees, correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be assigned to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want it) or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so much!
--
Aria W.

I accidentally hit post before I was done with my last response. Here is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

What Bruce suggested should work fine.

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

tblEmployees

tblSiteEmps

tblTitles

tblClassifications

tblTitlesEmps

--
_________

Sean Bailey

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post before I
sent off my last one. Boy, do I need to make some changes to what I did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in
his code but I thought I was supposed to put the fields I was interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

The bookmarks are only valid for the period of time that the form is >open. If you close and re-open the form, those same records may have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

There is something a little odd though. I have only input 6 employee >>names using frmEmployees just so I can check to see if things are >>working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry >>shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a >report and then use a command button on your form to print (or print >preview) it.

I did print the form and understand what you're saying. I didn't really care
for the way it looked. The only reports I have right now are relationship
diagrams. I don't have any saved macros either. How hard would it be for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and then under
comments (?) list the above?
--
Aria W.

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what *type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) that
your form is based on.

You can declare anything you want, as long as you declare it properly so
that Access knows what it is. In other words, you could (theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less universally
recognized (by application designers and such) as representing a recordset
of some type.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

It is a placeholder of sorts, but it has nothing to do with the actual data
that
is stored in your table. Everytime you open a bound form, a unique bookmark
is created for each record in that form's recordset. The bookmarks are only
valid for the period of time that the form is open. If you close and re-open
the form, those same records may have a different bookmark assigned.

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

The last field is not shown because it is only used for sorting.

There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14.

When you opened which table?

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a report
and then use a command button on your form to print (or print preview) it.

--
_________

Sean Bailey


:

Hi Bruce,
Thanks for responding. It seemed so easy in the example. Why does everything
have to turn into a problem? I'm sorry, I forgot to include column widths. I
thought about that much later.

Column widths =0";0.6459";0.625"
List widths=1.2708"

In this case it should be unbound. You aren't trying to store the value, >but rather to go to the selected record.

I thought it should be unbound but I've been wrong so often that now I
automatically think, "You're wrong. It must be the other way."


Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

Again, if you don't want to get into it, that's fine.

As far as the SQL statement, I opened it again in Design view and this is
what it shows:

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending

 

[Aria]

Guess what? It works...it works! I'm trying real hard to hold it together,
but I am so happy. <sniff> Thank you *so* much! OK, so let me tell you what
happened. I'm going to combine parts of both your posts.

Bruce M wrote:
If you view the SQL in datasheet view you will see that the first column is
EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is the
same as EmpID in the combo box."

Beetle wrote:
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

Aria writes:
It was the combination of both your posts that allowed me to follow along
closely. You explained what was happening and then said this is what it will
look like.

Bruce M wrote:
Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code. After you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option Explicit
are under Option Compare Database. Add them if they are not. If they are
not, in the VBA editor click Tools > Options. Click the Editor tab, and
check the box Require Variable Declaration.
Still in the editor, click Debug > Compile. This will highlight any typos
and other such errors in the code.

Aria writes:
Step by step instructions...how could I ask for anything better than that.
The Require Variable Declaration box wasn't checked. I didn't find the Debug

Compile box but it did have Auto Syntax Check box. When I ran it I got a

Compile error (Error 461). I used Help for this part. When I was finished, I
noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in
yellow; highlighted in blue was cbo StaffLookup. I didn't notice the
underscore with cboStaff_Lookup before. That did it.

Bruce M wrote:
I don't know what is happening with the sort order, but let's not get too
many things cooking on a Friday afternoon.

<lol> Well, on my end it may be a little late for that. One more thing...the
sort order is working as it should. I can't believe my book said it could be
done w/o code. I can't thank you both enough. Still trying to hold it
together. Have a great weekend!
--

Aria W.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in making sure
everything is OK. Every time you have reservations, there's usually something
amiss. I just want to say, before I post the table structure, that some facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes (temporary).

2. Each employee can only have 1 classification (Admin., Certificated
(teacher et. al), Classified and Substitutes). There are many employees who
have the same classification. tblClassifications 1:M tblEmployees, correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be assigned to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want it) or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so much!
--
Aria W.

I accidentally hit post before I was done with my last response. Here is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

What Bruce suggested should work fine.

There is something a little odd though. I have only input 6 employee
names using frmEmployees just so I can check to see if things are
working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry
shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

tblEmployees

tblSiteEmps

tblTitles

tblClassifications

tblTitlesEmps

--
_________

Sean Bailey

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post before I
sent off my last one. Boy, do I need to make some changes to what I did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in
his code but I thought I was supposed to put the fields I was interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

The bookmarks are only valid for the period of time that the form is >open. If you close and re-open the form, those same records may have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

There is something a little odd though. I have only input 6 employee >>names using frmEmployees just so I can check to see if things are >>working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry >>shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a >report and then use a command button on your form to print (or print >preview) it.

I did print the form and understand what you're saying. I didn't really care
for the way it looked. The only reports I have right now are relationship
diagrams. I don't have any saved macros either. How hard would it be for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and then under
comments (?) list the above?
--
Aria W.


:

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what *type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) that
your form is based on.

You can declare anything you want, as long as you declare it properly so
that Access knows what it is. In other words, you could (theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less universally
recognized (by application designers and such) as representing a recordset
of some type.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

It is a placeholder of sorts, but it has nothing to do with the actual data
that
is stored in your table. Everytime you open a bound form, a unique bookmark
is created for each record in that form's recordset. The bookmarks are only
valid for the period of time that the form is open. If you close and re-open
the form, those same records may have a different bookmark assigned.

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees

 

[Beetle]

Looks good for the most part with just a few comments;

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be here.)
LN
FN
MI

Other than the fact that TitleDescription doesn't belong here, my other
question is about ClassDescription. It *is* correct to have a field in this
table
as a FK to tblClassifications, but it should be ClassID. The odd thing is that
you say it is a long integer number, so maybe you have it correctly
related to ClassID in tblClassifications. It may be correct, it's just hard
to tell
because you named it ClassDescription. It should be a long integer (which
you say it is) and it should be related to ClassID in tblClassifications.

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want it) or
do we need to do something else?

No, that is correct. You need that subform to enter titles.

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)

In this table EmpID should be a FK to tblEmployees and the PK for this table
should be a combination of *both* the EmpID and TitleID fields. Again,
maybe you already have it that way and you just didn't specify in your post.

Other than that, it looks good as far as I can tell. I won't be around this
week
so hopefully all will go well

Good Luck
--
_________

Sean Bailey

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in making sure
everything is OK. Every time you have reservations, there's usually something
amiss. I just want to say, before I post the table structure, that some facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes (temporary).

2. Each employee can only have 1 classification (Admin., Certificated
(teacher et. al), Classified and Substitutes). There are many employees who
have the same classification. tblClassifications 1:M tblEmployees, correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be assigned to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want it) or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so much!
--
Aria W.

I accidentally hit post before I was done with my last response. Here is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

What Bruce suggested should work fine.

There is something a little odd though. I have only input 6 employee
names using frmEmployees just so I can check to see if things are
working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry
shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

tblEmployees

tblSiteEmps

tblTitles

tblClassifications

tblTitlesEmps

--
_________

Sean Bailey

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post before I
sent off my last one. Boy, do I need to make some changes to what I did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in
his code but I thought I was supposed to put the fields I was interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

The bookmarks are only valid for the period of time that the form is >open. If you close and re-open the form, those same records may have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

There is something a little odd though. I have only input 6 employee >>names using frmEmployees just so I can check to see if things are >>working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry >>shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a >report and then use a command button on your form to print (or print >preview) it.

I did print the form and understand what you're saying. I didn't really care
for the way it looked. The only reports I have right now are relationship
diagrams. I don't have any saved macros either. How hard would it be for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and then under
comments (?) list the above?
--
Aria W.


:

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what *type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) that
your form is based on.

You can declare anything you want, as long as you declare it properly so
that Access knows what it is. In other words, you could (theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less universally
recognized (by application designers and such) as representing a recordset
of some type.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

It is a placeholder of sorts, but it has nothing to do with the actual data
that
is stored in your table. Everytime you open a bound form, a unique bookmark
is created for each record in that form's recordset. The bookmarks are only
valid for the period of time that the form is open. If you close and re-open
the form, those same records may have a different bookmark assigned.

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees

 

[BruceM]

I should have said 'Still in the VBA editor, click Debug >> Compile." This
is in the menu bar, not in the Tools >> Options dialog. I like to have that
command readily available, so I added it to the toolbar. To do that, right
click on the toolbar, click Customize, click the Options tab, click Debug on
the left, and drag Compile from the right side to the toolbar.

BTW, you could sort the row source for the Employee combo box by the
concatenated (LastFirst) field. If you sort on LastName you should sort on
FirstName next, in case two people have the same last name. You don't need
to show the LastName and FirstName fields.

Guess what? It works...it works! I'm trying real hard to hold it together,
but I am so happy. <sniff> Thank you *so* much! OK, so let me tell you
what
happened. I'm going to combine parts of both your posts.

Bruce M wrote:
If you view the SQL in datasheet view you will see that the first column
is
EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is the
same as EmpID in the combo box."

Beetle wrote:
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

Aria writes:
It was the combination of both your posts that allowed me to follow along
closely. You explained what was happening and then said this is what it
will
look like.

Bruce M wrote:
Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code. After you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option Explicit
are under Option Compare Database. Add them if they are not. If they are
not, in the VBA editor click Tools > Options. Click the Editor tab, and
check the box Require Variable Declaration.
Still in the editor, click Debug > Compile. This will highlight any typos
and other such errors in the code.

Aria writes:
Step by step instructions...how could I ask for anything better than that.
The Require Variable Declaration box wasn't checked. I didn't find the
Debug

Compile box but it did have Auto Syntax Check box. When I ran it I got a

Compile error (Error 461). I used Help for this part. When I was finished,
I
noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in
yellow; highlighted in blue was cbo StaffLookup. I didn't notice the
underscore with cboStaff_Lookup before. That did it.

Bruce M wrote:
I don't know what is happening with the sort order, but let's not get too
many things cooking on a Friday afternoon.

<lol> Well, on my end it may be a little late for that. One more
thing...the
sort order is working as it should. I can't believe my book said it could
be
done w/o code. I can't thank you both enough. Still trying to hold it
together. Have a great weekend!
--

Aria W.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should
exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in making
sure
everything is OK. Every time you have reservations, there's usually
something
amiss. I just want to say, before I post the table structure, that some
facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes
(temporary).

2. Each employee can only have 1 classification (Admin., Certificated
(teacher et. al), Classified and Substitutes). There are many employees
who
have the same classification. tblClassifications 1:M tblEmployees,
correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be assigned
to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be
here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want it)
or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so much!
--
Aria W.

I accidentally hit post before I was done with my last response. Here
is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want both
first
and last name to show. I did as Bruce suggested with the SQL he gave
me and
it does show what I want.

What Bruce suggested should work fine.

There is something a little odd though. I have only input 6
employee
names using frmEmployees just so I can check to see if things are
working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final
entry
shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not
entering
directly into the tables and I use a drop-down menu.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should
exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

tblEmployees

tblSiteEmps

tblTitles

tblClassifications

tblTitlesEmps

--
_________

Sean Bailey


:

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post
before I
sent off my last one. Boy, do I need to make some changes to what I
did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had
it in
his code but I thought I was supposed to put the fields I was
interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just
declared.
In this case you are telling it to assign a copy of the recordset
(table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName
don't
exist in any of your other tables. Your query looks fine, unless
you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both
first
and last name to show. I did as Bruce suggested with the SQL he gave
me and
it does show what I want.

The bookmarks are only valid for the period of time that the form
is >open. If you close and re-open the form, those same records may
have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

There is something a little odd though. I have only input 6
employee >>names using frmEmployees just so I can check to see if
things are >>working. When I opened the table, 5 of the employees
have a
title description (Admin., teacher, etc.). The sixth and final
entry >>shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not
entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to
be
printed and usually look like *&%! when you do. You should create a
report and then use a command button on your form to print (or
print >preview) it.

I did print the form and understand what you're saying. I didn't
really care
for the way it looked. The only reports I have right now are
relationship
diagrams. I don't have any saved macros either. How hard would it be
for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and
then under
comments (?) list the above?
--
Aria W.


:

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill
level. I am
certainly willing to try, if you don't mind the questions that
are sure to
come. I'll try not to inundate you with a thousand and one
questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

The difference is that in the first case rs is used as a sort
of
abbreviation for Me.RecordsetClone. Thereafter you use rs in
place of
Me.RecordsetClone. Dim rs as Object declares rs as an object
variable.

2. Well, maybe I don't need to know, but I was wondering if
Access already
knows that rs is an abbreviation for Me.RecordsetClone because
the = sign
isn't used until the second statement. If it's more than I need
to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what
*type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just
declared.
In this case you are telling it to assign a copy of the recordset
(table) that
your form is based on.

You can declare anything you want, as long as you declare it
properly so
that Access knows what it is. In other words, you could
(theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less
universally
recognized (by application designers and such) as representing a
recordset
of some type.

3. Me.Bookmark--I've seen that before and have been dying to ask.
What does
that do? I think of a bookmark as a placeholder, but if the value
isn't
stored...?

It is a placeholder of sorts, but it has nothing to do with the
actual data
that
is stored in your table. Everytime you open a bound form, a unique
bookmark
is created for each record in that form's recordset. The bookmarks
are only
valid for the period of time that the form is open. If you close
and re-open
the form, those same records may have a different bookmark
assigned.

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in
frmEmployees
with subform SiteEmps, so the table you want me to look in is
tblEmployees?

You have to look in tblEmployees, because FirstName and LastName
don't
exist in any of your other tables. Your query looks fine, unless
you want to

show both names in the combo when it is not expanded (dopped down).
A combo box will only display the first visible column in its
unexpanded
state, so in that case you would need to concantenate the names in
your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees

 

[Aria]

You know I had never been to this section of my database before. Originally,
I had no intention of putting in any programming code. I tried doing as you
suggested but when I go to Customize...Options...I don't see what you are
describing. What I see in the Options tab is Personalized Toolbar and Menu
but it's greyed. The only button you can push is Reset my Usage Data (?).
Maybe I'm in the wrong place.

I wanted to return to the 2nd half of the db. I still need to work on tables
but I have questions (naturally!) Do you mind?
--
Aria W.

I should have said 'Still in the VBA editor, click Debug >> Compile." This
is in the menu bar, not in the Tools >> Options dialog. I like to have that
command readily available, so I added it to the toolbar. To do that, right
click on the toolbar, click Customize, click the Options tab, click Debug on
the left, and drag Compile from the right side to the toolbar.

BTW, you could sort the row source for the Employee combo box by the
concatenated (LastFirst) field. If you sort on LastName you should sort on
FirstName next, in case two people have the same last name. You don't need
to show the LastName and FirstName fields.

Guess what? It works...it works! I'm trying real hard to hold it together,
but I am so happy. <sniff> Thank you *so* much! OK, so let me tell you
what
happened. I'm going to combine parts of both your posts.

Bruce M wrote:
If you view the SQL in datasheet view you will see that the first column
is
EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is the
same as EmpID in the combo box."

Beetle wrote:
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

Aria writes:
It was the combination of both your posts that allowed me to follow along
closely. You explained what was happening and then said this is what it
will
look like.

Bruce M wrote:
Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code. After you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option Explicit
are under Option Compare Database. Add them if they are not. If they are
not, in the VBA editor click Tools > Options. Click the Editor tab, and
check the box Require Variable Declaration.
Still in the editor, click Debug > Compile. This will highlight any typos
and other such errors in the code.

Aria writes:
Step by step instructions...how could I ask for anything better than that.
The Require Variable Declaration box wasn't checked. I didn't find the
Debug

Compile box but it did have Auto Syntax Check box. When I ran it I got a

Compile error (Error 461). I used Help for this part. When I was finished,
I
noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted in
yellow; highlighted in blue was cbo StaffLookup. I didn't notice the
underscore with cboStaff_Lookup before. That did it.

Bruce M wrote:
I don't know what is happening with the sort order, but let's not get too
many things cooking on a Friday afternoon.

<lol> Well, on my end it may be a little late for that. One more
thing...the
sort order is working as it should. I can't believe my book said it could
be
done w/o code. I can't thank you both enough. Still trying to hold it
together. Have a great weekend!
--

Aria W.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should
exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in making
sure
everything is OK. Every time you have reservations, there's usually
something
amiss. I just want to say, before I post the table structure, that some
facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes
(temporary).

2. Each employee can only have 1 classification (Admin., Certificated
(teacher et. al), Classified and Substitutes). There are many employees
who
have the same classification. tblClassifications 1:M tblEmployees,
correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be assigned
to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be
here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want it)
or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so much!
--
Aria W.


:

I accidentally hit post before I was done with my last response. Here
is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want both
first
and last name to show. I did as Bruce suggested with the SQL he gave
me and
it does show what I want.

What Bruce suggested should work fine.

There is something a little odd though. I have only input 6
employee
names using frmEmployees just so I can check to see if things are
working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final
entry
shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not
entering
directly into the tables and I use a drop-down menu.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should
exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

tblEmployees

tblSiteEmps

tblTitles

tblClassifications

tblTitlesEmps

--
_________

Sean Bailey


:

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post
before I
sent off my last one. Boy, do I need to make some changes to what I
did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had
it in
his code but I thought I was supposed to put the fields I was
interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just
declared.
In this case you are telling it to assign a copy of the recordset
(table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName
don't
exist in any of your other tables. Your query looks fine, unless
you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both
first
and last name to show. I did as Bruce suggested with the SQL he gave
me and
it does show what I want.

The bookmarks are only valid for the period of time that the form
is >open. If you close and re-open the form, those same records may
have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

There is something a little odd though. I have only input 6

 

[BruceM]

I think that Macros may be considered to be something other than code, but
I'm not sure. If you use the wizard the code is added automatically. You
can view it, but you may not know it was added. In any case, unless you use
macros, which are rather limited, you pretty much need VBA code for anything
other than a very simple database.
To find the Customize option I mentioned, open your database. In the
database window, click the Forms tab. Click View >> Code, or click the Code
icon on the toolbar. What you see is the VBA editor. Right click a blank
spot on the toolbar or menu bar. You should see Customize, probably as the
last item on the list. There are of course other ways to open the VBA
editor, so choose another method if you prefer. The way you open it is not
important (although I wouldn't try to customize while debugging).

I will take a look at your questions, but you will need to sort of start
over with your descriptions, as I have not been following very closely of
late. When you do so, list only the essential fields. For instance, for
tblEmployees:

EmployeeID (PK - Number)
LastName
FirstName
etc.

may be enough. For tblSiteEmps, list the PK/FK field, the relationship
type, and a few fields that will give the idea of how the table is used:

tblSiteEmps (1:1 tblEmployees)
EmpID (PK/FK)
Home Phone-txt
Address-txt
etc.

This is enough for our purposes. Give yourself a break from typing all of
the details, unless for instance the fact there is a cell phone number is
relevant to the problem at hand, and make it easier for me or another
responder to read. Limit your description to a few typical fields, or to
fields that are part of your code or that are involved in relationships.

You know I had never been to this section of my database before.
Originally,
I had no intention of putting in any programming code. I tried doing as
you
suggested but when I go to Customize...Options...I don't see what you are
describing. What I see in the Options tab is Personalized Toolbar and Menu
but it's greyed. The only button you can push is Reset my Usage Data (?).
Maybe I'm in the wrong place.

I wanted to return to the 2nd half of the db. I still need to work on
tables
but I have questions (naturally!) Do you mind?
--
Aria W.

I should have said 'Still in the VBA editor, click Debug >> Compile."
This
is in the menu bar, not in the Tools >> Options dialog. I like to have
that
command readily available, so I added it to the toolbar. To do that,
right
click on the toolbar, click Customize, click the Options tab, click Debug
on
the left, and drag Compile from the right side to the toolbar.

BTW, you could sort the row source for the Employee combo box by the
concatenated (LastFirst) field. If you sort on LastName you should sort
on
FirstName next, in case two people have the same last name. You don't
need
to show the LastName and FirstName fields.

Guess what? It works...it works! I'm trying real hard to hold it
together,
but I am so happy. <sniff> Thank you *so* much! OK, so let me tell you
what
happened. I'm going to combine parts of both your posts.

Bruce M wrote:
If you view the SQL in datasheet view you will see that the first
column
is
EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is
the
same as EmpID in the combo box."

Beetle wrote:
A combo box will only display the first visible column in its
unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

Aria writes:
It was the combination of both your posts that allowed me to follow
along
closely. You explained what was happening and then said this is what it
will
look like.

Bruce M wrote:
Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code. After
you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create
some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option
Explicit
are under Option Compare Database. Add them if they are not. If they
are
not, in the VBA editor click Tools > Options. Click the Editor tab, and
check the box Require Variable Declaration.
Still in the editor, click Debug > Compile. This will highlight any
typos
and other such errors in the code.

Aria writes:
Step by step instructions...how could I ask for anything better than
that.
The Require Variable Declaration box wasn't checked. I didn't find the
Debug
Compile box but it did have Auto Syntax Check box. When I ran it I got
a
Compile error (Error 461). I used Help for this part. When I was
finished,
I
noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted
in
yellow; highlighted in blue was cbo StaffLookup. I didn't notice the
underscore with cboStaff_Lookup before. That did it.

Bruce M wrote:
I don't know what is happening with the sort order, but let's not get
too
many things cooking on a Friday afternoon.

<lol> Well, on my end it may be a little late for that. One more
thing...the
sort order is working as it should. I can't believe my book said it
could
be
done w/o code. I can't thank you both enough. Still trying to hold it
together. Have a great weekend!
--

Aria W.


:

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should
exist
is in tblTitles. Can you post your current structure for the
following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in
making
sure
everything is OK. Every time you have reservations, there's usually
something
amiss. I just want to say, before I post the table structure, that
some
facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes
(temporary).

2. Each employee can only have 1 classification (Admin., Certificated
(teacher et. al), Classified and Substitutes). There are many
employees
who
have the same classification. tblClassifications 1:M tblEmployees,
correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be
assigned
to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because
of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be
here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want
it)
or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so much!
--
Aria W.


:

I accidentally hit post before I was done with my last response.
Here
is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of
whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want
both
first
and last name to show. I did as Bruce suggested with the SQL he
gave
me and
it does show what I want.

What Bruce suggested should work fine.

There is something a little odd though. I have only input 6
employee
names using frmEmployees just so I can check to see if things
are
working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final
entry
shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not
entering
directly into the tables and I use a drop-down menu.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should
exist
is in tblTitles. Can you post your current structure for the
following
tables (hopefully I have the table names right)?

tblEmployees

tblSiteEmps

tblTitles

tblClassifications

tblTitlesEmps

--
_________

Sean Bailey


:

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post
before I
sent off my last one. Boy, do I need to make some changes to what
I
did.

Yes, but you may need to correct the naming. For example, I
believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce
had
it in
his code but I thought I was supposed to put the fields I was
interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you
just
declared.
In this case you are telling it to assign a copy of the
recordset
(table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName
don't
exist in any of your other tables. Your query looks fine, unless
you want to
show both names in the combo when it is not expanded (dopped
down).

Ok, I looked in tblEmployees. I didn't see anything. I do want
both
first
and last name to show. I did as Bruce suggested with the SQL he
gave
me and
it does show what I want.

The bookmarks are only valid for the period of time that the
form
is >open. If you close and re-open the form, those same records
may
have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

There is something a little odd though. I have only input 6

 

[Aria]

Other than the fact that TitleDescription doesn't belong here, my other

question is about ClassDescription. It *is* correct to have a field in this
table
as a FK to tblClassifications, but it should be ClassID. The odd thing is that
you say it is a long integer number, so maybe you have it correctly
related to ClassID in tblClassifications. It may be correct, it's just hard
to tell
because you named it ClassDescription. It should be a long integer (which
you say it is) and it should be related to ClassID in tblClassifications.

No, this isn't correct. Once again, you're correct. I don't know if I meant
to put ClassID and somehow it got switched or what. I don't remember. I'll
change it. I really appreciate your questioning what I post. It is *not*
helpful if I post the wrong information. How can I then get the help I need.
I'm sorry.

No, that is correct. You need that subform to enter titles.

Ok, do I need to change the info in the combo box to accomodate the change?
Because I had linked via ClassID through EmpsClass to tblClassifications...

In this table EmpID should be a FK to tblEmployees and the PK for this table
should be a combination of *both* the EmpID and TitleID fields. Again,
maybe you already have it that way and you just didn't specify in your post.

Ok, let's stop here. I owe you the biggest apology. I am not posting the
correct information. This is a two field composite key. I think my porblem is
that I have not been sleeping well and had spent 16 hours looking for answers
and trying to resolve the staff look-up problem. But still; in order not to
send us all on a wild goose chase I need to get it right. Please forgive me.
I'll try to do much better.

I'm going to stop working on forms and such and go back to the second half
of the db; locks, keys, locations and phones. I hope your week off is
peaceful.
--
Aria W.

Looks good for the most part with just a few comments;

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be here.)
LN
FN
MI

Other than the fact that TitleDescription doesn't belong here, my other
question is about ClassDescription. It *is* correct to have a field in this
table
as a FK to tblClassifications, but it should be ClassID. The odd thing is that
you say it is a long integer number, so maybe you have it correctly
related to ClassID in tblClassifications. It may be correct, it's just hard
to tell
because you named it ClassDescription. It should be a long integer (which
you say it is) and it should be related to ClassID in tblClassifications.

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want it) or
do we need to do something else?

No, that is correct. You need that subform to enter titles.

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)

In this table EmpID should be a FK to tblEmployees and the PK for this table
should be a combination of *both* the EmpID and TitleID fields. Again,
maybe you already have it that way and you just didn't specify in your post.

Other than that, it looks good as far as I can tell. I won't be around this
week
so hopefully all will go well

Good Luck
--
_________

Sean Bailey

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in making sure
everything is OK. Every time you have reservations, there's usually something
amiss. I just want to say, before I post the table structure, that some facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes (temporary).

2. Each employee can only have 1 classification (Admin., Certificated
(teacher et. al), Classified and Substitutes). There are many employees who
have the same classification. tblClassifications 1:M tblEmployees, correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be assigned to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want it) or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so much!
--
Aria W.

I accidentally hit post before I was done with my last response. Here is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

What Bruce suggested should work fine.

There is something a little odd though. I have only input 6 employee
names using frmEmployees just so I can check to see if things are
working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry
shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should exist
is in tblTitles. Can you post your current structure for the following
tables (hopefully I have the table names right)?

tblEmployees

tblSiteEmps

tblTitles

tblClassifications

tblTitlesEmps

--
_________

Sean Bailey


:

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post before I
sent off my last one. Boy, do I need to make some changes to what I did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in
his code but I thought I was supposed to put the fields I was interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

The bookmarks are only valid for the period of time that the form is >open. If you close and re-open the form, those same records may have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

There is something a little odd though. I have only input 6 employee >>names using frmEmployees just so I can check to see if things are >>working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry >>shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a >report and then use a command button on your form to print (or print >preview) it.

I did print the form and understand what you're saying. I didn't really care
for the way it looked. The only reports I have right now are relationship
diagrams. I don't have any saved macros either. How hard would it be for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and then under
comments (?) list the above?
--
Aria W.


:

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what *type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) that
your form is based on.

You can declare anything you want, as long as you declare it properly so
that Access knows what it is. In other words, you could (theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less universally

 

[Aria]

I found the compile button. It was under commands so I added it to the
toolbar. Thanks.

I will take a look at your questions, but you will need to sort of start
over with your descriptions, as I have not been following very closely of
late. When you do so, list only the essential fields.

Not quite ready to list the table structures for this part yet...still
stinging from the last time when they were all wrong. I just basically want
to think on post. Ask a few questions and have you weigh in on where I'm
going wrong. Are you Ok with that? I completely understand what you mean as
far as having lost track. I have all my notes and posts and I still have to
read up on what's going on and what transpired.

Questions unrelated to 2nd half of db:
1. When I input the programmng code for the AfterUpdate event, I input
cboStaff_Lookup. The line highlighted in yellow said, "Private Sub
cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final underscore
after Lookup?

2.Ever since this started taking off in earnest, I have often thought that
this db seems to be complicated; maybe that's just beginner's woe. I don't
know because I don't have a point of reference. Both you and Beetle stated in
your posts to each other in the beginning that it wasn't simple for a first
app. My question is how did you know? We hadn't even really gotten into it
past the original 6 tables. How does one determine complexity?

Hoping this is not too much for you in one post...

We know:
1. This is a staff database for a school setting.
2. We encompass 2 campuses.
3.Db emphasis is on keys because they have been problematic.
4. We have already suffered through a re-key of an entire campus, including
the stadium, gyms and gates.
5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage Rms., etc.)
6. Master keys allow general access for a single campus.
7. Master keys are unique, employee specific and their allocation is
severely restricted.
8. Storage, Stadium, Food Service and Gate Masters are location specific
*and* follow rule #7.
9. Wing masters will only open all rooms for a specific section of the site.
10. Key assignments are based on job title, room assignment and
extracurricular duties.

Where we left off:
1. We had 4 tables relating to keys or location: tblRooms, tblKeysEmployees
(junction), tblKeys and tblKeysRequests.

2. There was a lot of discussion involving home, personal cell, district
cell and room phone #s. The last decision was that home/personal cell is part
of tblEmployees; district cell and room phone #s are part of tblphones.

Suggestions still on the table:
1. Create tblLocations to replace tblRooms.
2. Create tblLocks to define the relationsip between keys and locks. There
should be a 2 field PK involving Key ID and LocationID.
3. Create Master Key table to account for the special attributes of Master
Keys.
4. Create additional look-up tables: tblCampuses, tblWings and
tblLocationTypes.

If your eyes haven't glazed over... You don't have to answer today; I know
it's a lot.

Questions/Comments:
1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key
for KeyID in tblKeys. Your statement about a possible re-key and our history
of such finally made me realize that this was a *lousy* idea from the
beginning. I'm switching to autonumber.

2. Master keys--This is a sub-type of key, correct? You can have one key and
many masters or is it many sub-types (Gate, Stadium, etc.) But you can also
have one specific master that has many keys assigned. While the master is
employee specific, it *is* essentially the same key that is assigned to every
employee who has clearnace. It's just coded so we know in advance who we gave
it to. This is a 1:M relationship? You can't have a M:M relationship with
sub-types can you? Either way it will have its own PK. This is where I'm
confusing myself and going around in circles.

I'll stop here for now. Thanks!

--
Aria W.

I think that Macros may be considered to be something other than code, but
I'm not sure. If you use the wizard the code is added automatically. You
can view it, but you may not know it was added. In any case, unless you use
macros, which are rather limited, you pretty much need VBA code for anything
other than a very simple database.
To find the Customize option I mentioned, open your database. In the
database window, click the Forms tab. Click View >> Code, or click the Code
icon on the toolbar. What you see is the VBA editor. Right click a blank
spot on the toolbar or menu bar. You should see Customize, probably as the
last item on the list. There are of course other ways to open the VBA
editor, so choose another method if you prefer. The way you open it is not
important (although I wouldn't try to customize while debugging).

I will take a look at your questions, but you will need to sort of start
over with your descriptions, as I have not been following very closely of
late. When you do so, list only the essential fields. For instance, for
tblEmployees:

EmployeeID (PK - Number)
LastName
FirstName
etc.

may be enough. For tblSiteEmps, list the PK/FK field, the relationship
type, and a few fields that will give the idea of how the table is used:

tblSiteEmps (1:1 tblEmployees)
EmpID (PK/FK)
Home Phone-txt
Address-txt
etc.

This is enough for our purposes. Give yourself a break from typing all of
the details, unless for instance the fact there is a cell phone number is
relevant to the problem at hand, and make it easier for me or another
responder to read. Limit your description to a few typical fields, or to
fields that are part of your code or that are involved in relationships.

You know I had never been to this section of my database before.
Originally,
I had no intention of putting in any programming code. I tried doing as
you
suggested but when I go to Customize...Options...I don't see what you are
describing. What I see in the Options tab is Personalized Toolbar and Menu
but it's greyed. The only button you can push is Reset my Usage Data (?).
Maybe I'm in the wrong place.

I wanted to return to the 2nd half of the db. I still need to work on
tables
but I have questions (naturally!) Do you mind?
--
Aria W.

I should have said 'Still in the VBA editor, click Debug >> Compile."
This
is in the menu bar, not in the Tools >> Options dialog. I like to have
that
command readily available, so I added it to the toolbar. To do that,
right
click on the toolbar, click Customize, click the Options tab, click Debug
on
the left, and drag Compile from the right side to the toolbar.

BTW, you could sort the row source for the Employee combo box by the
concatenated (LastFirst) field. If you sort on LastName you should sort
on
FirstName next, in case two people have the same last name. You don't
need
to show the LastName and FirstName fields.

Guess what? It works...it works! I'm trying real hard to hold it
together,
but I am so happy. <sniff> Thank you *so* much! OK, so let me tell you
what
happened. I'm going to combine parts of both your posts.

Bruce M wrote:
If you view the SQL in datasheet view you will see that the first
column
is
EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is
the
same as EmpID in the combo box."

Beetle wrote:
A combo box will only display the first visible column in its
unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

Aria writes:
It was the combination of both your posts that allowed me to follow
along
closely. You explained what was happening and then said this is what it
will
look like.

Bruce M wrote:
Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code. After
you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create
some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option
Explicit
are under Option Compare Database. Add them if they are not. If they
are
not, in the VBA editor click Tools > Options. Click the Editor tab, and
check the box Require Variable Declaration.
Still in the editor, click Debug > Compile. This will highlight any
typos
and other such errors in the code.

Aria writes:
Step by step instructions...how could I ask for anything better than
that.
The Require Variable Declaration box wasn't checked. I didn't find the
Debug
Compile box but it did have Auto Syntax Check box. When I ran it I got
a
Compile error (Error 461). I used Help for this part. When I was
finished,
I
noticed that Private Sub cboStaff_Lookup_AfterUpdate () was highlighted
in
yellow; highlighted in blue was cbo StaffLookup. I didn't notice the
underscore with cboStaff_Lookup before. That did it.

Bruce M wrote:
I don't know what is happening with the sort order, but let's not get
too
many things cooking on a Friday afternoon.

<lol> Well, on my end it may be a little late for that. One more
thing...the
sort order is working as it should. I can't believe my book said it
could
be
done w/o code. I can't thank you both enough. Still trying to hold it
together. Have a great weekend!
--

Aria W.


:

Something's not right here. tblEmployees should not have a field for
TitleDescription. The only place the TitleDescription field should
exist
is in tblTitles. Can you post your current structure for the
following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in
making
sure
everything is OK. Every time you have reservations, there's usually
something
amiss. I just want to say, before I post the table structure, that
some
facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes
(temporary).

2. Each employee can only have 1 classification (Admin., Certificated
(teacher et. al), Classified and Substitutes). There are many
employees
who
have the same classification. tblClassifications 1:M tblEmployees,
correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be
assigned
to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now because
of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be
here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I want
it)
or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so much!
--
Aria W.


:

I accidentally hit post before I was done with my last response.
Here
is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of
whatever
the recordset is. You don't need to tell it the table or query name.

Ok, I looked in tblEmployees. I didn't see anything. I do want
both
first

 

[BruceM]

I don't have much time to reply today, but I have noted a few things inline.

I found the compile button. It was under commands so I added it to the
toolbar. Thanks.


Not quite ready to list the table structures for this part yet...still
stinging from the last time when they were all wrong. I just basically
want
to think on post. Ask a few questions and have you weigh in on where I'm
going wrong. Are you Ok with that? I completely understand what you mean
as
far as having lost track. I have all my notes and posts and I still have
to
read up on what's going on and what transpired.

Questions unrelated to 2nd half of db:
1. When I input the programmng code for the AfterUpdate event, I input
cboStaff_Lookup. The line highlighted in yellow said, "Private Sub
cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final
underscore
after Lookup?

If you select the combo box, open the Property Sheet (with the tabs for
Format, Data, etc.), select an event, click the three dots, click Code
Builder, and click OK the name of the control will be filled in for you in
the code window. If you type it yourself you need the underscore. I think
that is so there are no spaces in the procedure name.

2.Ever since this started taking off in earnest, I have often thought that
this db seems to be complicated; maybe that's just beginner's woe. I
don't
know because I don't have a point of reference. Both you and Beetle stated
in
your posts to each other in the beginning that it wasn't simple for a
first
app. My question is how did you know? We hadn't even really gotten into it
past the original 6 tables. How does one determine complexity?

There's no definitive answer for that, but anything that involves junction
tables is a lot to get your mind around if you are just starting. Before
long, if it hasn't already happened, the need for junction tables, and how
to use them, will be clear in your mind. In your case there were a number
of junction tables, and a situation that involved keys, locks, people to
whom the keys were issued, locations of the locks, campuses, and a number of
other entities, with a variety of relationships between the entities.

Hoping this is not too much for you in one post...

We know:
1. This is a staff database for a school setting.
2. We encompass 2 campuses.
3.Db emphasis is on keys because they have been problematic.
4. We have already suffered through a re-key of an entire campus,
including
the stadium, gyms and gates.
5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage Rms.,
etc.)
6. Master keys allow general access for a single campus.
7. Master keys are unique, employee specific and their allocation is
severely restricted.
8. Storage, Stadium, Food Service and Gate Masters are location specific
*and* follow rule #7.
9. Wing masters will only open all rooms for a specific section of the
site.
10. Key assignments are based on job title, room assignment and
extracurricular duties.

Where we left off:
1. We had 4 tables relating to keys or location: tblRooms,
tblKeysEmployees
(junction), tblKeys and tblKeysRequests.

What became of tblLocks? If one lock may have several keys, locks are the
top level.

2. There was a lot of discussion involving home, personal cell, district
cell and room phone #s. The last decision was that home/personal cell is
part
of tblEmployees; district cell and room phone #s are part of tblphones.

Suggestions still on the table:
1. Create tblLocations to replace tblRooms.
2. Create tblLocks to define the relationsip between keys and locks. There
should be a 2 field PK involving Key ID and LocationID.
3. Create Master Key table to account for the special attributes of Master
Keys.
4. Create additional look-up tables: tblCampuses, tblWings and
tblLocationTypes.

If your eyes haven't glazed over... You don't have to answer today; I know
it's a lot.

Questions/Comments:
1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key
for KeyID in tblKeys. Your statement about a possible re-key and our
history
of such finally made me realize that this was a *lousy* idea from the
beginning. I'm switching to autonumber.

There's nothing wrong with a natural key, depending on what it is. With a
surrogate key such as autonumber the main thing is that the record needs to
be unique for reasons apart from the autonumber field. If two records are
identical except for an artificial identifier then they are not really
unique. The autonumber is a convenience, but cannot by itself enforce what
is known as a unique constraint.

2. Master keys--This is a sub-type of key, correct? You can have one key
and
many masters or is it many sub-types (Gate, Stadium, etc.) But you can
also
have one specific master that has many keys assigned. While the master is
employee specific, it *is* essentially the same key that is assigned to
every
employee who has clearnace. It's just coded so we know in advance who we
gave
it to. This is a 1:M relationship? You can't have a M:M relationship with
sub-types can you? Either way it will have its own PK. This is where I'm
confusing myself and going around in circles.

I'll have to ponder this one later.

I'll stop here for now. Thanks!

--
Aria W.

I think that Macros may be considered to be something other than code,
but
I'm not sure. If you use the wizard the code is added automatically.
You
can view it, but you may not know it was added. In any case, unless you
use
macros, which are rather limited, you pretty much need VBA code for
anything
other than a very simple database.
To find the Customize option I mentioned, open your database. In the
database window, click the Forms tab. Click View >> Code, or click the
Code
icon on the toolbar. What you see is the VBA editor. Right click a
blank
spot on the toolbar or menu bar. You should see Customize, probably as
the
last item on the list. There are of course other ways to open the VBA
editor, so choose another method if you prefer. The way you open it is
not
important (although I wouldn't try to customize while debugging).

I will take a look at your questions, but you will need to sort of start
over with your descriptions, as I have not been following very closely of
late. When you do so, list only the essential fields. For instance, for
tblEmployees:

EmployeeID (PK - Number)
LastName
FirstName
etc.

may be enough. For tblSiteEmps, list the PK/FK field, the relationship
type, and a few fields that will give the idea of how the table is used:

tblSiteEmps (1:1 tblEmployees)
EmpID (PK/FK)
Home Phone-txt
Address-txt
etc.

This is enough for our purposes. Give yourself a break from typing all
of
the details, unless for instance the fact there is a cell phone number is
relevant to the problem at hand, and make it easier for me or another
responder to read. Limit your description to a few typical fields, or to
fields that are part of your code or that are involved in relationships.

You know I had never been to this section of my database before.
Originally,
I had no intention of putting in any programming code. I tried doing as
you
suggested but when I go to Customize...Options...I don't see what you
are
describing. What I see in the Options tab is Personalized Toolbar and
Menu
but it's greyed. The only button you can push is Reset my Usage Data
(?).
Maybe I'm in the wrong place.

I wanted to return to the 2nd half of the db. I still need to work on
tables
but I have questions (naturally!) Do you mind?
--
Aria W.


:

I should have said 'Still in the VBA editor, click Debug >> Compile."
This
is in the menu bar, not in the Tools >> Options dialog. I like to
have
that
command readily available, so I added it to the toolbar. To do that,
right
click on the toolbar, click Customize, click the Options tab, click
Debug
on
the left, and drag Compile from the right side to the toolbar.

BTW, you could sort the row source for the Employee combo box by the
concatenated (LastFirst) field. If you sort on LastName you should
sort
on
FirstName next, in case two people have the same last name. You don't
need
to show the LastName and FirstName fields.

Guess what? It works...it works! I'm trying real hard to hold it
together,
but I am so happy. <sniff> Thank you *so* much! OK, so let me tell
you
what
happened. I'm going to combine parts of both your posts.

Bruce M wrote:
If you view the SQL in datasheet view you will see that the first
column
is
EmpID. Column 1 should be the bound (hidden) column in
cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is
the
same as EmpID in the combo box."

Beetle wrote:
A combo box will only display the first visible column in its
unexpanded
state, so in that case you would need to concantenate the names in
your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

Aria writes:
It was the combination of both your posts that allowed me to follow
along
closely. You explained what was happening and then said this is what
it
will
look like.

Bruce M wrote:
Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code.
After
you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create
some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing
the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option
Explicit
are under Option Compare Database. Add them if they are not. If they
are
not, in the VBA editor click Tools > Options. Click the Editor tab,
and
check the box Require Variable Declaration.
Still in the editor, click Debug > Compile. This will highlight any
typos
and other such errors in the code.

Aria writes:
Step by step instructions...how could I ask for anything better than
that.
The Require Variable Declaration box wasn't checked. I didn't find
the
Debug
Compile box but it did have Auto Syntax Check box. When I ran it I
got
a
Compile error (Error 461). I used Help for this part. When I was
finished,
I
noticed that Private Sub cboStaff_Lookup_AfterUpdate () was
highlighted
in
yellow; highlighted in blue was cbo StaffLookup. I didn't notice the
underscore with cboStaff_Lookup before. That did it.

Bruce M wrote:
I don't know what is happening with the sort order, but let's not
get
too
many things cooking on a Friday afternoon.

<lol> Well, on my end it may be a little late for that. One more
thing...the
sort order is working as it should. I can't believe my book said it
could
be
done w/o code. I can't thank you both enough. Still trying to hold
it
together. Have a great weekend!
--

Aria W.


:

Something's not right here. tblEmployees should not have a field
for
TitleDescription. The only place the TitleDescription field
should
exist
is in tblTitles. Can you post your current structure for the
following
tables (hopefully I have the table names right)?

Yes, you have the names right. I appreciate your thoroughness in
making
sure
everything is OK. Every time you have reservations, there's usually
something
amiss. I just want to say, before I post the table structure, that
some
facts
concerning our situation may have been forgotten since our original
discussions. Please allow me to refresh our memories about
employees,
classifications and titles.
1. Our school employs both site staff (permanent) and substitutes
(temporary).

2. Each employee can only have 1 classification (Admin.,
Certificated
(teacher et. al), Classified and Substitutes). There are many
employees
who
have the same classification. tblClassifications 1:M tblEmployees,
correct?
For our purposes Admin. are strictly Admin.

3. Each employee can have one or many titles. Each title can be
assigned
to
many employees. tblTitles M:M tblTitlesEmps

The structure is as follows:

tblEmployees
Inactive Yes/No
EmpID PK Autonumber, long integer
ClassDescription FK to tblClassifications (number, long integer)
(This is what it *should* be. It's kind of messed up right now
because
of
tblEmpsClass which should be deleted.)
TitleDescription
(<gasp!>Illumination... I see what you're saying. This shouldn't be
here.)
LN
FN
MI

Let's go back to TitleDescription. We made that a subform within
tblEmployees. Do we keep it (now that I finally have it where I
want
it)
or
do we need to do something else?


tblSiteEmps 1:1 tblEmployees
EmpID (PK/FK)
Home Phone-txt
Cell Phone-txt
Address-txt
City-txt
State-txt
ZipCode-txt
EmerContactLN -txt
EmerContactFN- txt
EmerContactPhone - txt
PlaceofEmployment - txt
FamilyDr - txt
MedInsurance - txt
HospitalPref - txt
HealthIssues - txt
Medications- txt
Allergies - txt
DateCreated Date/Time
DateModified Date/Time

tblTitles
TitleID PK
TitleDescription

tblTitlesEmps
EmpID PK
TitleID number, l.i.(FK to tblTitles)


tblClassifications
ClassID PK
ClassDescriptions - txt (FK to tblClassifications)



Hopefully, the rest of it is OK. Good looking out...thank you so
much!
--
Aria W.


:

I accidentally hit post before I was done with my last response.
Here
is the
complete response.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

No. It should be Set rs = Me.RecordsetClone

What you're doing here is telling Access to create a copy of
whatever
the recordset is. You don't need to tell it the table or query
name.

Ok, I looked in tblEmployees. I didn't see anything. I do want
both
first

 

[Aria]

I don't have much time to reply today, but I have noted a few things inline.

Please, don't give it a second thought. I appreciate all of the support you
have both given me. I understand.

If you select the combo box, open the Property Sheet (with the tabs for
Format, Data, etc.), select an event, click the three dots, click Code
Builder, and click OK the name of the control will be filled in for you in
the code window. If you type it yourself you need the underscore. I think
that is so there are no spaces in the procedure name.

I'll check on that.

There's no definitive answer for that, but anything that involves junction
tables is a lot to get your mind around if you are just starting. Before
long, if it hasn't already happened, the need for junction tables, and how
to use them, will be clear in your mind. In your case there were a number
of junction tables, and a situation that involved keys, locks, people to
whom the keys were issued, locations of the locks, campuses, and a number of
other entities, with a variety of relationships between the entities.

Yeah, no kidding. I started thinking about that because I was trying to
avoid adding yet another junction table. I recall a previous discussion where
we decided that the room phone could go into tblLocations. That would leave
district cell phone orphaned and I could not figure out where to put it. It
doesn't belong in tblLocations and doesn't belong in tblEmployees so I
figured why am I drawing the line at this junction table when there are 5
others just like it. Oh well...c'est la vie! I keep looking at the
relationship diagram and I 'm concerned how I will manage all of this. In
particular, what kind of form to design and whether I should have another
subform for tbKleysRequests on frmEmployees. I'm trying not to borrow
trouble; one thing at a time.

What became of tblLocks? If one lock may have several keys, locks are the
top level.

"A lock can have several keys". There have been a number of turning points
in this journey. You both have dropped statements that on the surface seemed
inconsequential ,but in reality had a major impact. That is one thing I am
*never* going to forget. You made 3 on the mark statements in that post.
tblLocks is still here. It*is* a junction table, correct? It sure looks like
one.

There's nothing wrong with a natural key, depending on what it is. With a
surrogate key such as autonumber the main thing is that the record needs to
be unique for reasons apart from the autonumber field. If two records are
identical except for an artificial identifier then they are not really
unique. The autonumber is a convenience, but cannot by itself enforce what
is known as a unique constraint.

Won't this cause a problem? With the re-key, they not only changed the lock,
they changed the lock identifier. So if the key was previously, let's say,
SA-2 , it may have been changed to XJ-3A after the re-key. If I used this as
my primary key, I would have thought this would be a nightmarish situation.
No? If it happened once, there's always the possibility that it *could*
happen again. Keys are lost pretty much on a weekly basis. What do you thinK?
Do I need an inactive button for keys? I don't know exactly *how* this would
work.

Let me know what you think about that and the master keys. Don't concern
yourself if you don't have time. I have problems in the db that I need to
straighten out. Thanks so much for your time Bruce.

--
Aria W.

I don't have much time to reply today, but I have noted a few things inline.

I found the compile button. It was under commands so I added it to the
toolbar. Thanks.


Not quite ready to list the table structures for this part yet...still
stinging from the last time when they were all wrong. I just basically
want
to think on post. Ask a few questions and have you weigh in on where I'm
going wrong. Are you Ok with that? I completely understand what you mean
as
far as having lost track. I have all my notes and posts and I still have
to
read up on what's going on and what transpired.

Questions unrelated to 2nd half of db:
1. When I input the programmng code for the AfterUpdate event, I input
cboStaff_Lookup. The line highlighted in yellow said, "Private Sub
cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final
underscore
after Lookup?

If you select the combo box, open the Property Sheet (with the tabs for
Format, Data, etc.), select an event, click the three dots, click Code
Builder, and click OK the name of the control will be filled in for you in
the code window. If you type it yourself you need the underscore. I think
that is so there are no spaces in the procedure name.

2.Ever since this started taking off in earnest, I have often thought that
this db seems to be complicated; maybe that's just beginner's woe. I
don't
know because I don't have a point of reference. Both you and Beetle stated
in
your posts to each other in the beginning that it wasn't simple for a
first
app. My question is how did you know? We hadn't even really gotten into it
past the original 6 tables. How does one determine complexity?

There's no definitive answer for that, but anything that involves junction
tables is a lot to get your mind around if you are just starting. Before
long, if it hasn't already happened, the need for junction tables, and how
to use them, will be clear in your mind. In your case there were a number
of junction tables, and a situation that involved keys, locks, people to
whom the keys were issued, locations of the locks, campuses, and a number of
other entities, with a variety of relationships between the entities.

Hoping this is not too much for you in one post...

We know:
1. This is a staff database for a school setting.
2. We encompass 2 campuses.
3.Db emphasis is on keys because they have been problematic.
4. We have already suffered through a re-key of an entire campus,
including
the stadium, gyms and gates.
5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage Rms.,
etc.)
6. Master keys allow general access for a single campus.
7. Master keys are unique, employee specific and their allocation is
severely restricted.
8. Storage, Stadium, Food Service and Gate Masters are location specific
*and* follow rule #7.
9. Wing masters will only open all rooms for a specific section of the
site.
10. Key assignments are based on job title, room assignment and
extracurricular duties.

Where we left off:
1. We had 4 tables relating to keys or location: tblRooms,
tblKeysEmployees
(junction), tblKeys and tblKeysRequests.

What became of tblLocks? If one lock may have several keys, locks are the
top level.

2. There was a lot of discussion involving home, personal cell, district
cell and room phone #s. The last decision was that home/personal cell is
part
of tblEmployees; district cell and room phone #s are part of tblphones.

Suggestions still on the table:
1. Create tblLocations to replace tblRooms.
2. Create tblLocks to define the relationsip between keys and locks. There
should be a 2 field PK involving Key ID and LocationID.
3. Create Master Key table to account for the special attributes of Master
Keys.
4. Create additional look-up tables: tblCampuses, tblWings and
tblLocationTypes.

If your eyes haven't glazed over... You don't have to answer today; I know
it's a lot.

Questions/Comments:
1.Decision--Unbeknowst to you and Beetle, I was going to use a natural key
for KeyID in tblKeys. Your statement about a possible re-key and our
history
of such finally made me realize that this was a *lousy* idea from the
beginning. I'm switching to autonumber.

There's nothing wrong with a natural key, depending on what it is. With a
surrogate key such as autonumber the main thing is that the record needs to
be unique for reasons apart from the autonumber field. If two records are
identical except for an artificial identifier then they are not really
unique. The autonumber is a convenience, but cannot by itself enforce what
is known as a unique constraint.

2. Master keys--This is a sub-type of key, correct? You can have one key
and
many masters or is it many sub-types (Gate, Stadium, etc.) But you can
also
have one specific master that has many keys assigned. While the master is
employee specific, it *is* essentially the same key that is assigned to
every
employee who has clearnace. It's just coded so we know in advance who we
gave
it to. This is a 1:M relationship? You can't have a M:M relationship with
sub-types can you? Either way it will have its own PK. This is where I'm
confusing myself and going around in circles.

I'll have to ponder this one later.

I'll stop here for now. Thanks!

--
Aria W.

I think that Macros may be considered to be something other than code,
but
I'm not sure. If you use the wizard the code is added automatically.
You
can view it, but you may not know it was added. In any case, unless you
use
macros, which are rather limited, you pretty much need VBA code for
anything
other than a very simple database.
To find the Customize option I mentioned, open your database. In the
database window, click the Forms tab. Click View >> Code, or click the
Code
icon on the toolbar. What you see is the VBA editor. Right click a
blank
spot on the toolbar or menu bar. You should see Customize, probably as
the
last item on the list. There are of course other ways to open the VBA
editor, so choose another method if you prefer. The way you open it is
not
important (although I wouldn't try to customize while debugging).

I will take a look at your questions, but you will need to sort of start
over with your descriptions, as I have not been following very closely of
late. When you do so, list only the essential fields. For instance, for
tblEmployees:

EmployeeID (PK - Number)
LastName
FirstName
etc.

may be enough. For tblSiteEmps, list the PK/FK field, the relationship
type, and a few fields that will give the idea of how the table is used:

tblSiteEmps (1:1 tblEmployees)
EmpID (PK/FK)
Home Phone-txt
Address-txt
etc.

This is enough for our purposes. Give yourself a break from typing all
of
the details, unless for instance the fact there is a cell phone number is
relevant to the problem at hand, and make it easier for me or another
responder to read. Limit your description to a few typical fields, or to
fields that are part of your code or that are involved in relationships.


You know I had never been to this section of my database before.
Originally,
I had no intention of putting in any programming code. I tried doing as
you
suggested but when I go to Customize...Options...I don't see what you
are
describing. What I see in the Options tab is Personalized Toolbar and
Menu
but it's greyed. The only button you can push is Reset my Usage Data
(?).
Maybe I'm in the wrong place.

I wanted to return to the 2nd half of the db. I still need to work on
tables
but I have questions (naturally!) Do you mind?
--
Aria W.


:

I should have said 'Still in the VBA editor, click Debug >> Compile."
This
is in the menu bar, not in the Tools >> Options dialog. I like to
have
that
command readily available, so I added it to the toolbar. To do that,
right
click on the toolbar, click Customize, click the Options tab, click
Debug
on
the left, and drag Compile from the right side to the toolbar.

BTW, you could sort the row source for the Employee combo box by the
concatenated (LastFirst) field. If you sort on LastName you should
sort
on
FirstName next, in case two people have the same last name. You don't
need
to show the LastName and FirstName fields.

Guess what? It works...it works! I'm trying real hard to hold it
together,
but I am so happy. <sniff> Thank you *so* much! OK, so let me tell
you
what
happened. I'm going to combine parts of both your posts.

Bruce M wrote:
If you view the SQL in datasheet view you will see that the first
column
is
EmpID. Column 1 should be the bound (hidden) column in
cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is
the
same as EmpID in the combo box."

Beetle wrote:
A combo box will only display the first visible column in its
unexpanded
state, so in that case you would need to concantenate the names in
your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

Aria writes:
It was the combination of both your posts that allowed me to follow
along
closely. You explained what was happening and then said this is what
it
will
look like.

Bruce M wrote:
Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code.
After
you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create
some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing
the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option
Explicit
are under Option Compare Database. Add them if they are not. If they

 

[Aria]

I think I may have a problem that I need to work through. We can discuss this
when you have time. I just wanted to get this down before I lose track and
start dealing with other issues. I think we are going to need to re-visit an
issue from the very first post: vault keys. Originally, we decided that the
situation was unclear and dropped it. In looking through some of the data
that I copied to help map my direction, I just noticed something that I
probably saw before but did not grab my attention as it has now. During the
re-key, among the many replacement keys made, one key was important enough to
be "assigned" to the vault. There are probably others, but that happened
before my time so I am not aware of the previous history. This does change
things a bit. I'm going to have to account for not only that key, but all of
the vault keys.

I have two trains of thought right now:

The first is that the vault key is a sub type key that will need its own
table. I don't know about this because it doesn't seem to fit the model of a
sub type (at least not as far as I can see). It's not like the masters which
are actually a different type of key. Their attributes are significantly
different. Not so with the vault key; there's nothing special or different
about them.

The second thought is that this is really a location type and will need to
be included with tbllocations or is it tbllocationtypes. I'm still working on
this because I'm unsure right now. It does seem to fit the "where" model
though.

Just throwing it out there...

 

[BruceM]

Is the vault key just like any other key for a particular lock, except that
it is in the vault? If so, is the vault copy essentially a clean copy to be
used as the master for additional copies? If so, you can identify the valut
key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or you
can "assign" the key to the vault just as you would assign it to a person.
To do this you could add (Vault) to the top of the drop-down list of
Employees to whom keys are issued, similar to the way some drop-down lists
show (All) at the top. I won't go too far down that road until I hear back
from you.

Regarding the Locks table, I don't think it would be a junction table. A
lock is an entity with certain characteristics such as brand, location, date
installed, and so forth. One Lock can have many keys. Iff Master Keys are
included in the thinking, one Key can be for many Locks, but in this case
the junction table would be tblKeyLock or something like that.

Regarding tblKey, I expect there should be a Lost field or something like
that. There is no need to keep an active listing of keys nobody can find.
Regarding the question of natural key or surrogate key (any "artificial
identifier", including autonumber), it really doesn't matter as long as the
"natural" number such as SA-2 is used once for one physical (metal) key, and
never used again. If you are not sure this is the case, autonumber would be
a simpler choice as the PK. The same idea comes into play with the Lock
identifier, I expect. If they change the lock in such a way that the old
keys can be used you will need to update the FK of those keys so that they
are associated with the new lock.

Suggestions still on the table:
1. Create tblLocations to replace tblRooms

Sounds like a good idea
..

2. Create tblLocks to define the relationsip between keys and locks. There
should be a 2 field PK involving Key ID and LocationID.

Already discussed. Let me know if something is unclear, or if I am missing
something

3. Create Master Key table to account for the special attributes of Master
Keys.

One possibility is to see the situation as One Lock > Many Keys and One Key

Many Locks, in which tblKeyLock is needed to resolve the relationship for

all locks and all keys. However, I think a separate table for MasterKeys
and a junction table tblKeyLock would be simpler to manage. If you are
looking at a Lock record you would have a subform listing the ordinary keys
and the person to whom they are assigned (some may not be assigned at all, I
expect). One Lock > Many Keys, so there is a 1:M between tblLock and
tblKey, and the subform is based on tblKey. Another subform based on
tblKeyLock could list the master key holders.
One lock could have keys assigned to many people, and each person could be
assigned keys. Another consideration is that a Lock record should have a
listing of available keys. If it was me I think I would have an AssignedTo
field in tblKeys:

tblKeys
KeyID (PK)
LockID (FK to tblLocks)
KeyCode (S2-A, etc., or whatever)
AssignedTo (FK to tblEmployees)
AssignedDate
Retired (Yes/No)

If I wanted to list people and the keys they hold I would use a query.

This is not necessarily the best design in that AssignedTo and AssignedDate
are not really attributes of keys. The complexity here is that one person
may be assigned many keys, but a key can be assigned to only one person.
Similarly, one lock may have many keys, but other than master keys a key may
open only one lock. On the other hand, one person may open many locks, and
a lock may be opened by many people. By the way, it just occurred to me
that if there may be several locks keyed to accept the same key you may need
to add a LockLocation table related to tblLocks to take care of this detail.
One solution here may be to have a LockPerson junction table (one person >>
many locks and vice versa). This would be the source for a subform on the
Locks form. A list box could contain a listing of unassigned keys for that
lock. The list box row source would have to be built as you go, since the
available keys are always changing. When somebody needs to be assigned a
key you would go to the Lock form, see the listing of available keys, and
created a new LockPerson record that stores the KeyID, EmployeeID, and maybe
AssignedDate and other details.
It may be worth your while to start a new thread on this specific topic. All
you would need to say is that you have:
A Locks table containing the LockId, LockLocation, etc.
A Keys table containing KeyID (PK), LockID (FK), KeyCode, etc.
An Employee table

Explain that you understand a Lock may have many keys, and that a person may
be assigned many keys. However, you are unsure how to store the AssignedTo
information for Keys. Should it be in the Keys table? If not, how is that
relationship modeled.

I suggest this because frankly I am unsure how best to proceed on this
point, and in a new thread you would attract the attention of very
experienced designers.

4. Create additional look-up tables: tblCampuses, tblWings and
tblLocationTypes.

Sounds good.

**********

 

[Aria]

First, thank you for your honesty. I smiled reading your post. That is the
same thing that happens to me. I think, "hmmm...it could be like this...but
then what about that...oh, and I forgot to account for this...but then I have
to switch that...and the next thing you know, I'm right back where I started.
<g> I don't think you go through quite the same thing because you have a
*lot* more experience but it *is* confusing.
While keys are what drove my desire to create this db in the first place,
this is also the section I have been dreading. I reviewed all of our posts
and took notes on what was suggested and any decisions that were made. When I
refer to a statement as from a post in the beginning, that's what I mean. So,
let's see if we can make any progress on some of these areas.

Is the vault key just like any other key for a particular lock, except that it is in the >vault?
Yes.

If so, is the vault copy essentially a clean copy to be used as the master for >additional copies?

No, although at times they may come and borrow a key to make a quick copy if
they are already on campus and I just need one or two. It's rare. The
district has its own locksmith who are permanent staff. They have the
originals or blanks or whatever they call them.

If so, you can identify the valut key either by an extra field (maybe a Yes/No Vault >field) in tblKeys, or you can "assign" the key to the vault just as you would assign >it to a person.

Ok, what happened here? We have apparently switched our origianl positions.
I suggested assigning the vault key like an employee key in the very first
post, but after learning about table attributes from the two of you ( I will
never forget tblSubs), I no longer hold that position. You two are absolutely
right. It isn't an employee attribute.
I don't think it should be a yes/no field because of the sheer number of
keys we're talking about...approx. 400. Am I going to run into problems with
that many yes/no boxes? You may be right about this. I'm not sure because
again, I don't have the experience behind me. So where does that leave us?

Are you oppossed to the vault table or vault as a location in
tbllocations(now that I think about it, somethings wrong with this line of
thinking but I'm not sure what)?

Regarding the Locks table, I don't think it would be a junction table.

I 'm now confused about the purpose and fields in this table. You had posted
in the beginning that it should include lookup tables for campus, wing and
roomtype. I by no means expect you to remember this because I didn't either.
There was a lot of discussion about a great many things. I'm only mentioning
it now because you asked me to post my table structure for this part of the
db. I was unsure about some of the structure and wanted to make sure I had it
right before I posted. It was a confusing mess that I had to keep looking at.
I couldn't make heads nor tails of what it should be. I had to live with it
awhile. I had to sit down and go through all the posts suggestions and
advice, move fields from one place to another, change table names and add
fields and lookup tables. When I finished the only fields left in tblLocker
were KeyID and LocationID. I originally had campus and wing in this section
but once we changed tblRooms to tblLocations, it seemed better suited in
tblLocations. Since I was now left with only 2 fields in tblLocks, I started
wondering if it was a junction table.

There is no need to keep an active listing of keys nobody can find.

<lol> ...good one! No, let's not.

I'm going to post my table structure later today, even though it still needs
work. I know there are mistakes and that there is plenty of room for
improvement, but you have posted additional comments that I believe (if I'm
not mistaken) may have been addressed within the structure.

 

[BruceM]

First, thank you for your honesty. I smiled reading your post. That is the
same thing that happens to me. I think, "hmmm...it could be like
this...but
then what about that...oh, and I forgot to account for this...but then I
have
to switch that...and the next thing you know, I'm right back where I
started.
<g> I don't think you go through quite the same thing because you have a
*lot* more experience but it *is* confusing.
While keys are what drove my desire to create this db in the first place,
this is also the section I have been dreading. I reviewed all of our posts
and took notes on what was suggested and any decisions that were made.
When I
refer to a statement as from a post in the beginning, that's what I mean.
So,
let's see if we can make any progress on some of these areas.


No, although at times they may come and borrow a key to make a quick copy
if
they are already on campus and I just need one or two. It's rare. The
district has its own locksmith who are permanent staff. They have the
originals or blanks or whatever they call them.


Ok, what happened here? We have apparently switched our origianl
positions.
I suggested assigning the vault key like an employee key in the very first
post, but after learning about table attributes from the two of you ( I
will
never forget tblSubs), I no longer hold that position. You two are
absolutely
right. It isn't an employee attribute.
I don't think it should be a yes/no field because of the sheer number of
keys we're talking about...approx. 400. Am I going to run into problems
with
that many yes/no boxes? You may be right about this. I'm not sure because
again, I don't have the experience behind me. So where does that leave us?

I don't know if it is a switch or not, but the fact is you are assigning the
key to the vault. Take a look at this:
http://www.mvps.org/access/forms/frm0043.htm

It explains how to produce a list such as this:

(Vault)
Adams, John
Adams, John Quincy
Jefferson, Thomas
Madison, James
Monroe, James
Washington, George

When you select (Vault) you can have the AssignedTo field be null or 0. In
a query you could have something like this:
AssignedEntity: IIf([AssignedTo] Is Null,"Vault",[LastName] & ", " &
[FirstName])

The code in the link I provided (use the part at the top of the page, not
the part that talks about a Value List) adds a sort of artificial record to
the Employees listing, but it *does not* create an employee record.

Are you oppossed to the vault table or vault as a location in
tbllocations(now that I think about it, somethings wrong with this line of
thinking but I'm not sure what)?

The vault is issued a key. We can modify the part about keys being assigned
to people to say that keys are assigned to entities including people. The
vault is in a location, but it is not a location. If the vault has a keyed
lock there is a corresponding record in tblLocks, including Location
information.

I 'm now confused about the purpose and fields in this table. You had
posted
in the beginning that it should include lookup tables for campus, wing and
roomtype. I by no means expect you to remember this because I didn't
either.
There was a lot of discussion about a great many things. I'm only
mentioning
it now because you asked me to post my table structure for this part of
the
db. I was unsure about some of the structure and wanted to make sure I had
it
right before I posted. It was a confusing mess that I had to keep looking
at.
I couldn't make heads nor tails of what it should be. I had to live with
it
awhile. I had to sit down and go through all the posts suggestions and
advice, move fields from one place to another, change table names and add
fields and lookup tables. When I finished the only fields left in
tblLocker
were KeyID and LocationID. I originally had campus and wing in this
section
but once we changed tblRooms to tblLocations, it seemed better suited in
tblLocations. Since I was now left with only 2 fields in tblLocks, I
started
wondering if it was a junction table.

Let's say Room 222 of Building A on the South campus has a lock with Serial
Number 12345. Your tblLocks would be something like this:

tblLocks
LockID LNumber LCampus LBuilding LLocation
111 12345 South Building A Room 222

You can store numbers instead of text for LBuilding, etc. If somebody
changes the building name the keys will remain the same, and all records
will reflect the new name. For now just assume the text value is stored.

One Lock can have many keys. Therefore tblKeys is something like this:

tblKeys
KeyID (PK)
LockID (FK)
KeyNumber (SA-12 or whatever)
Retired (Yes/No)

The Key records for Lock 111 could be something like this:

KeyID LockID KeyNumber
1 111 SA-1
2 111 SA-2
3 111 SA-3

Note that each key record is associated with a Lock record. KeyID is not a
part of the lock record, but rather the other way around. The lock has
keys. The key does not have locks.

You will note I have used several location fields (Campus, Building, and
Location). If several buildings have a Room 222, or several campuses have a
Maintenance Shed, or something like that, then you will need all three
location types to identify where you are in at least some cases. After
selecting South Campus the Building combo box could be limited to just
buildings on the South Campus. Similarly, the Room combo box could limit
Room Numbers in the selected building. More on this later, if you like, but
you can ignore it for now.

<lol> ...good one! No, let's not.

There are more such listings than you may imagine.

I'm going to post my table structure later today, even though it still
needs
work. I know there are mistakes and that there is plenty of room for
improvement, but you have posted additional comments that I believe (if
I'm
not mistaken) may have been addressed within the structure.

I will wait to see what you post, and will add some comments tomorrow,
including (if I can) a strategy for the junction table for storing
KeyAssignment information, but after that I will be away for a week. I
guess Sean will be back, so I hope I'm not at cross purposes with him to too
great an extent.

 

[Aria]

Here's the structure, mistakes and all:

tblKeys 1:M tblMasterKeys
KeyID PK (Autonumber)
KeyName
MstrKeyID (FK, number long integer to tblMasterKeys)

tblMasterKeys
MstrKeyID PK (Autonumber)
MstrKeyName

tblKeysEmployees M:M tblEmployees
KeyID (1/2 PK, FK to tblKeys, number, l.i.)
EmpID (1/2 PK, FK to tblEmployees, #, l.i.)
AllowedtoRetain Yes/No
Approvedby
DateIssued Date/Time
DateLost Date/Time
DateRtrnd Date/Time

tblKeysRequests 1:M tblKeysEmployees
RequestID PK (Autonumber)
KeyID FK, #, l.i. to tblKeysEmployees
QtyRqstd
DateRqstd Date/Time
DateIssued Date/Time
DateIssued (Hmmm...I just noticed that this is the same as tblKeysEmployees)
Funny how you can gloss right over things.
Comments

tblLocks (I thought this was a junction table but now I don't know)
KeyID PK/FK
LocationID PK/FK

tblLocations 1:M tblLocks
LocationID PK (Autonumber)
LocationTypeID FK to tblLocationTypes
LocationName
Remarks

tblLocationTypes 1:M tblLocations
LocationTypeID PK (Autonumber)
LocationType

tblCampuses 1:M tblLocations
CampusID PK (Autonumber)
CampusName

tblWings 1:M tblLocations
WingsID PK (Autonumber)
WingName

So I think tblKeysEmployees defines the relationship between keys and
employees.

Regarding other parts of your previous post:

The complexity here is that one person may be assigned many keys, but a key can be assigned to only one person.

No, this is not quite true. One key can be assigned to one or many people.

One solution here may be to have a LockPerson junction table (one person many locks and vice versa).

....*another* junction table?! Please...is there any way around this?

This would be the source for a subform on the Locks form. A list box could contain >a listing of unassigned keys for that lock. The list box row source would have to be >built as you go, since the available keys are always changing. When somebody >needs to be assigned a key you would go to the Lock form, see the listing of >available keys, and created a new LockPerson record that stores the KeyID, >EmployeeID, and maybe AssignedDate and other details.

It would be nice to have a list of unassigned keys but...it's a little
overwhelming right now. This just keeps growing and growing. I'm already onto
page 2 for printing the relationship diagram. Let me live with this for
awhile. I know yesterday I posted, "Oh well, c'est la vie" as far as junction
tables but today I can't deal with another junction table.

BTW:

tblKeys
KeyID (PK)
LockID (FK to tblLocks)
KeyCode (S2-A, etc., or whatever)
AssignedTo (FK to tblEmployees)
AssignedDate
Retired (Yes/No)

I like the field name KeyCode. I'm not so sure about the Retired (Yes/No)
field although that does have to be taken into account. I will probably end
up doing it that way. Let me think about it some more.

Let me know if this structure resolves *any* of the issues we have.