Ok, first thing, it would be possible to have more than one employee meet
the criteria when using just last name, but let's go without worry about
that for now.
To do this, you will need a date field that tells when they were assigned
each position or a sequential ID field where the higher number would be the
newer position in order to tell which position is the current one. You could
also use a yes/no field to indicate that they have left a position, but the
date option is probably best because you can interpolate this fact from the
date and you have the date for future reference if you should need it.
Next, to go to this record, you could then use your FindFirst call with
multiple criteria attached or use DLookup to get the value of the ID field
of the record and then go to that record.
Me.Recordset.FindFirst "[LNAME] = """ & Me![Combo61] & """ And
[DateField]=#" & DMax("[DateField]", "[TableName]", "[LNAME]=""" &
Me![Combo61]""") & "#"
This will find the first occurrence of the last name in Combo61 where the
date field mentioned above is the latest date in the table for that last
name. The # signs are date delimiters, just as you use " for strings.
--
Wayne Morgan
Microsoft Access MVP
Melanie O said:
Wayne,
Thanks for getting back to me. By most current record I just mean the
employee record that does not have a leave date (i.e. they still work for
us). I was hoping that setting the MIS_Leavedate to null would mean it
was the 'most current record.' I'm looking for the employee record on the
form that matches the last name of the combo box and that has no leave
date (code below) because some employees will have two records, one for
their first position and the second for their promotion or something, i.e.
most current record. Sorry this is getting so difficult... Thanks for
the help.
Melanie
:
Melanie,
Sorry for this being so late.
To pull the "most current record" you will need a date/time field to
determine which is the most current. Do you want the most current of any
employee or the most current for the selected employee? Which date are
you
wanting to base this currency on, the Hire_Date or do you have a
different
date that you need to use?
--
Wayne Morgan
Microsoft Access MVP
The form's recordset has these fields:
ID
Emp_ID
Fname
Lname
Band
Title
Hire_Date
MIS_Leavedate
BU_ID
The combo box's row source is:
SELECT tbl_EMPLOYEE_FULL.LNAME, tbl_EMPLOYEE_FULL.FNAME FROM
tbl_EMPLOYEE_FULL WHERE MIS_LEAVEDATE IS NULL ORDER BY
tbl_EMPLOYEE_FULL.LNAME;
:
Yes, the FindFirst does exactly that, it finds the first instance of
the
last name. There is nothing wrong with the code, kind of like having
"their"
when you meant "there", spell check won't catch it because they're
both
valid words.
To pull the most current employee record, you will need a date field
or
some
other field that will tell you which is the most current. What fields
are
in
the form's recordset and in the combo box's row source?
--
Wayne Morgan
MS Access MVP
Wayne,
Thanks for the quick response. I tried your first two steps without
getting any errors. The combo box I have set up is unbound, with 2
columns
and the bound column being 1. I don't think I'm having problems with
the
combo box itself. I also changed the last line of code to read:
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
However, after all these changes, the code still won't work. Is the
line
rs.FindFirst "[LNAME] = """ & Me![Combo61] & """" finding the first
record
with a matching last name from the table and using that information to
populate the form? If so, that's incorrect. I actually need the
query
behind the combo box to pull the most current employee record rather
than
the first instance of the last name. Let me know what else you may
need,
and thanks for the help.
Melanie
