By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

dlookup help

P: n/a
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.ListCount - 1

If emp_form.ItemData(x) = DLookup("STATUS_ID", "EMP_STATUS",
"EMP_ID = " & Me![emp id]) Then Me!lststatus.Selected(x) = True

Next x

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

Thanks!
Joe

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQeLq/IechKqOuFEgEQL57wCffozddV3BccVd03EF2X45ySdknrMAoKo +
3d1vRYForBx71du3pTEFnQDG
=/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.ListCount - 1

If emp_form.ItemData(x) = DLookup("STATUS_ID", "EMP_STATUS",
"EMP_ID = " & Me![emp id]) Then Me!lststatus.Selected(x) = True

Next x

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

Nov 13 '05 #2

P: n/a
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.

Nov 13 '05 #3

P: n/a
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.openrecordset("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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQeRVwYechKqOuFEgEQKcmgCcCh8Xu/RZBd3TDzmu9ie4fhmioFwAnAs/
NWP+zf1LPM6n0TNpk9ZqI7WT
=wKlu
-----END PGP SIGNATURE-----
Nov 13 '05 #4

P: n/a
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.ItemData(x) = DLookup("STATUS_ID", "EMP_STATUS", "EMP_ID =
" & Me![emp id] & " AND STATUS_ID = emp_form.ItemData(x)) Then

Me!lststatus.Selected(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.openrecordset("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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQeRVwYechKqOuFEgEQKcmgCcCh8Xu/RZBd3TDzmu9ie4fhmioFwAnAs/
NWP+zf1LPM6n0TNpk9ZqI7WT
=wKlu
-----END PGP SIGNATURE-----


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.