Re: Still Struggling...

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

"Aria" <Aria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BC0C0F83-14A9-4830-88EC-BB2EBF73DA6C@xxxxxxxxxxxxxxxx
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?

If you select the combo box, open the Property *** (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.


"BruceM" wrote:

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.


"Aria" <Aria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FB41E620-FF52-47A8-96EE-DC3BD5C40C3B@xxxxxxxxxxxxxxxx
> 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.
>
>
> "BruceM" wrote:
>
>> 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.
>>
>> "Aria" <Aria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:6C59B93D-6C8B-4B95-AE1E-244514E23ECD@xxxxxxxxxxxxxxxx
>> > 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 data*** 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.
>> >
>> >
>> > "Aria" wrote:
>> >
>> >> > 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.
>> >>
>> >>
>> >> "Beetle" wrote:
>> >>
>> >> > 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

.

Loading