Re: Still Struggling...

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
> > 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
>
>
> "Aria" wrote:
>
> > 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.
> >
> >
> > "Beetle" wrote:
> >
> > > 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

.