Combo box code pulling incorrect data

[Guest]

I have code behind a form for my combo box to select an employee based on last name:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[LNAME] = '" & Me![Combo61] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The problem is that it is showing the incorrect data. I have multiple records for employees that have transferred departments, so I think the FindFirst is pulling the first record with a matching last name, even though the record source for the combo box says:

SELECT test.LNAME, test.FNAME FROM test WHERE test.MIS_LEAVEDATE Is Null ORDER BY test.LNAME;

which means it should exclude transferred employees and only pull up their current record. Can I replace the 'FindFirst' with something more applicable? Or is there better code suited for my request?

Thanks,
Melanie

 

[Wayne Morgan]

Instead of Dim rs As Object, try Dim rs As DAO.Recordset. If this won't
compile, go to Tools|References in the code editor and make sure there is a
check mark next to "Microsoft DAO 3.6 Reference Library" (Access 2000 or
newer) or the newest version that is available.

rs.FindFirst "[LNAME] = '" & Me![Combo61] & "'"

This should work as long as the person doesn't have an apostrophe in their
last name, such as O'Hare or O'Reilly. Try changing this to

rs.FindFirst "[LNAME] = """ & Me![Combo61] & """"

How is your combo box set up (i.e. number of columns, bound column)? If the
bound column is a hidden ID column, then this is the value being returned by
Me!Combo61. If so, you need to specify which column to use (i.e. the one
that has the name in it).

Example:
Me!Combo61.Column(1)

The column number is zero based, so 1 would be the second column.

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

EOF (End Of File) is used in order to detect the end of a recordset when
stepping through a recordset one record at a time. In this case, what you
want is rs.NoMatch.

--
Wayne Morgan
Microsoft Access MVP

I have code behind a form for my combo box to select an employee based on
last name:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[LNAME] = '" & Me![Combo61] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The problem is that it is showing the incorrect data. I have multiple
records for employees that have transferred departments, so I think the
FindFirst is pulling the first record with a matching last name, even
though the record source for the combo box says:

SELECT test.LNAME, test.FNAME FROM test WHERE test.MIS_LEAVEDATE Is Null
ORDER BY test.LNAME;

which means it should exclude transferred employees and only pull up their
current record. Can I replace the 'FindFirst' with something more
applicable? Or is there better code suited for my request?

Thanks,
Melanie

 

[Wayne Morgan]

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.

 

[Wayne Morgan]

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

 

[Wayne Morgan]

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

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

 

[Wayne Morgan]

Sorry, I should have put a space in to make it easier to read, the = runs
into the #.

'DateField #'
should be
DateField = #

For DateField as Null:

Me.Recordset.FindFirst "[LNAME] = """ & Me![Combo61] & """ And [DateField]
Is Null"

--
Wayne Morgan
MS Access MVP

Wayne,

For some reason, Access doesn't like the 'DateField #', but that aside,

what would the code be if I just wanted the MIS_Leavedate to be null
(instead of using a DMax)?

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

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

 

[Guest]

Wayne,

I think I almost got it. However, I am now getting an "Object required" error. Here is my code:

Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[LNAME] = """ & Me![Combo61] & """ And [MIS_Leavedate]" Is Null
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

I was getting an "Expected: end of statement" error with the last " after the 'is null' so I took it out.
Thanks again for the help.

Melanie

Sorry, I should have put a space in to make it easier to read, the = runs
into the #.

'DateField #'
should be
DateField = #

For DateField as Null:

Me.Recordset.FindFirst "[LNAME] = """ & Me![Combo61] & """ And [DateField]
Is Null"

--
Wayne Morgan
MS Access MVP

Wayne,

For some reason, Access doesn't like the 'DateField #', but that aside,

what would the code be if I just wanted the MIS_Leavedate to be null
(instead of using a DMax)?

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


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

 

[Wayne Morgan]

rs.FindFirst "[LNAME] = """ & Me![Combo61] & """ And [MIS_Leavedate]" Is

Null

Did you have another " at the end? If so, that would have generated an
error. Actually, the last " should be moved to the end.

rs.FindFirst "[LNAME] = """ & Me![Combo61] & """ And [MIS_Leavedate] Is
Null"

--
Wayne Morgan
Microsoft Access MVP

Wayne,

I think I almost got it. However, I am now getting an "Object required"
error. Here is my code:

Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[LNAME] = """ & Me![Combo61] & """ And [MIS_Leavedate]" Is
Null
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

I was getting an "Expected: end of statement" error with the last " after
the 'is null' so I took it out.
Thanks again for the help.

Melanie

Sorry, I should have put a space in to make it easier to read, the = runs
into the #.

'DateField #'
should be
DateField = #

For DateField as Null:

Me.Recordset.FindFirst "[LNAME] = """ & Me![Combo61] & """ And
[DateField]
Is Null"

--
Wayne Morgan
MS Access MVP

Wayne,

For some reason, Access doesn't like the 'DateField #', but that aside,

what would the code be if I just wanted the MIS_Leavedate to be null
(instead of using a DMax)?

:

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


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


message
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