dlookup help

[Joe]

I have a table with an sequence (PK) and 2 fields, employee id and
status id. Each employee can have multiple records in this table due
to multiple status ids assigned.

I have a multi-list box on a form which displays all possible statuses.

I want to write code to highlight all the statuses associated with the
employees id.

I currently have tried dlookup but cannot get it to work correctly.

'Populate list box with saved values (if any exist)
For x = 0 To Me!lststatus.Li stCount - 1

If emp_form.ItemDa ta(x) = DLookup("STATUS _ID", "EMP_STATUS ",
"EMP_ID = " & Me![emp id]) Then Me!lststatus.Se lected(x) = True

Next x

Any suggestions on what I am doing wrong? Will Dlookup return more
than one record if they exist?

Thanks!
Joe

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this loop instead:

With Me!lstStatus
For each row = 0 to .ListCount - 1

.Selected(x) = .Column(col_n, x) =
DLookup("Status _ID", "Emp_Status ", "Emp_ID=" & Me![emp id])

next x
End With

The "col_n" in .Column(col_n, x) should be changed to the column number
(zero based) that holds the Status ID.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeLq/IechKqOuFEgEQL5 7wCffozddV3BccV d03EF2X45ySdknr MAoKo+
3d1vRYForBx71du 3pTEFnQDG
=/rJU
-----END PGP SIGNATURE-----
Joe wrote:

I have a table with an sequence (PK) and 2 fields, employee id and
status id. Each employee can have multiple records in this table due
to multiple status ids assigned.

I have a multi-list box on a form which displays all possible statuses.

I want to write code to highlight all the statuses associated with the
employees id.

I currently have tried dlookup but cannot get it to work correctly.

'Populate list box with saved values (if any exist)
For x = 0 To Me!lststatus.Li stCount - 1

If emp_form.ItemDa ta(x) = DLookup("STATUS _ID", "EMP_STATUS ",
"EMP_ID = " & Me![emp id]) Then Me!lststatus.Se lected(x) = True

Next x

Any suggestions on what I am doing wrong? Will Dlookup return more
than one record if they exist?

[Joe]

I still have not been able to get this working. Dlookup only seems to
be returning the 1st record it finds and it still isn't highlighting
that one on the form.

[MGFoster]

Joe wrote:

I still have not been able to get this working. Dlookup only seems to
be returning the 1st record it finds and it still isn't highlighting
that one on the form.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well I screwed up the For...Next loop, and didn't think the thing thru.
Try this (stupidly, since I just did the same thing in my last project,
but don't have the code):

' Get the list of Statuses for the emp_id
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordse t("SELECT Status_ID FROM Emp_status " & _
"WHERE emp_id = " & me![emp id])

With Me!lstStatus

' iterate thru rs & mark items in list box
do while not rs.eof
For x = 0 to .ListCount - 1
if .column(col_n, x) = rs(0) then
.selected(x) = true
exit for
end if
next x
rs.movenext
loop

End With

set rs = nothing
set db = nothing

The "col_n" in .Column(col_n, x) should be changed to the column number
(zero based) that holds the Status ID in the list box lstStatus.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeRVwYechKq OuFEgEQKcmgCcCh 8Xu/RZBd3TDzmu9ie4f hmioFwAnAs/
NWP+zf1LPM6n0TN pk9ZqI7WT
=wKlu
-----END PGP SIGNATURE-----

[Mike]

Hi,

Your problem is that, as you've written it, the DLookup always accesses
the first STATUS_ID for the EMP_ID. You should specify both EMP_ID and
STATUS_ID in the Where part of the DLookup:

....

If emp_form.ItemDa ta(x) = DLookup("STATUS _ID", "EMP_STATUS ", "EMP_ID =
" & Me![emp id] & " AND STATUS_ID = emp_form.ItemDa ta(x)) Then

Me!lststatus.Se lected(x) = True

End If

....

Try and let me know.
Mike Watson

MGFoster wrote:

Joe wrote:

I still have not been able to get this working. Dlookup only seems to be returning the 1st record it finds and it still isn't highlighting that one on the form.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well I screwed up the For...Next loop, and didn't think the thing

thru. Try this (stupidly, since I just did the same thing in my last project, but don't have the code):

' Get the list of Statuses for the emp_id
dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordse t("SELECT Status_ID FROM Emp_status " & _
"WHERE emp_id = " & me![emp id])

With Me!lstStatus

' iterate thru rs & mark items in list box
do while not rs.eof
For x = 0 to .ListCount - 1
if .column(col_n, x) = rs(0) then
.selected(x) = true
exit for
end if
next x
rs.movenext
loop

End With

set rs = nothing
set db = nothing

The "col_n" in .Column(col_n, x) should be changed to the column number (zero based) that holds the Status ID in the list box lstStatus.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQeRVwYechKq OuFEgEQKcmgCcCh 8Xu/RZBd3TDzmu9ie4f hmioFwAnAs/
NWP+zf1LPM6n0TN pk9ZqI7WT
=wKlu
-----END PGP SIGNATURE-----