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.
Aria said:
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