Search/Find Record dlookup

G

Guest

I am using the FIND code below and it works well on all but the "requestedby"
(which is a combo box which is viewed as a name but stored as a number.)
SELECT DISTINCT Overseer.OverseerID, Overseer.LastName, Overseer.FirstName,
Overseer.Department FROM Overseer WHERE (((Overseer.LastName) Is Not Null))
ORDER BY Overseer.LastName, Overseer.Department;

I want to be able to search in this code by the name. I tried the following
after the first error handler. I don't know where it should go (I get error
2001, previous ... xceled)

'lookup value for "requestedby"
RequestedBy = DLookup("[LastName]", "Overseer", "[OverseerID]='" &
Forms!OrdersWDetails!RequestedBy & "'")

Thanks in advance!
lmv

'--------FIND CODE---------
Private Sub FindRecordLike(strFindMode As String)
'Copyright 2003 J Street Technology, Inc.
'www.JStreetTech.com
'May be used and distributed without permission if these 3 lines are included.
On Error GoTo Error_Handler

'PO number shows up in search additional fields show up as ID numbers
Call jst_FindRecord(frmCallingForm:=Me, _
ctlFindFirst:=Me!cmdFirst, _
ctlFindNext:=Me!cmdNext, _
ctlSearchText:=Me!txtFind, _
ctlSearchOption:=Me!optFind, _
strFindMode:=strFindMode, _
strField1:="ProjectID", _
strField2:="PurchaseOrderNumber", _
strField3:="RequestedBy")

Exit_Procedure:
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Sub
 
D

Dirk Goldgar

lmv said:
I am using the FIND code below and it works well on all but the
"requestedby" (which is a combo box which is viewed as a name but
stored as a number.) SELECT DISTINCT Overseer.OverseerID,
Overseer.LastName, Overseer.FirstName, Overseer.Department FROM
Overseer WHERE (((Overseer.LastName) Is Not Null)) ORDER BY
Overseer.LastName, Overseer.Department;

I want to be able to search in this code by the name. I tried the
following after the first error handler. I don't know where it should
go (I get error 2001, previous ... xceled)

'lookup value for "requestedby"
RequestedBy = DLookup("[LastName]", "Overseer", "[OverseerID]='" &
Forms!OrdersWDetails!RequestedBy & "'")

Thanks in advance!
lmv

'--------FIND CODE---------
Private Sub FindRecordLike(strFindMode As String)
'Copyright 2003 J Street Technology, Inc.
'www.JStreetTech.com
'May be used and distributed without permission if these 3 lines are
included. On Error GoTo Error_Handler

'PO number shows up in search additional fields show up as ID
numbers Call jst_FindRecord(frmCallingForm:=Me, _
ctlFindFirst:=Me!cmdFirst, _
ctlFindNext:=Me!cmdNext, _
ctlSearchText:=Me!txtFind, _
ctlSearchOption:=Me!optFind, _
strFindMode:=strFindMode, _
strField1:="ProjectID", _
strField2:="PurchaseOrderNumber", _
strField3:="RequestedBy")

Exit_Procedure:
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Sub
Click to expand...

I can't make much of the FindRecordLike() and (unposted)
jst_FindRecord() routines, but if your OverseerID field is a number, and
the combo box RequestedBy has a numeric ID field as its bound column,
then your DLookup should drop the quotes around the value:

RequestedBy = DLookup("[LastName]", "Overseer", _
"[OverseerID]=" & Forms!OrdersWDetails!RequestedBy)
 
G

Guest

Thank you for the response. Would you be able to tell me WHERE to place it in
the code? I am not proficient enough with VBA... I just put it at the
beginning but it doesn't work that way. Thanks

Dirk Goldgar said:
lmv said:
I am using the FIND code below and it works well on all but the
"requestedby" (which is a combo box which is viewed as a name but
stored as a number.) SELECT DISTINCT Overseer.OverseerID,
Overseer.LastName, Overseer.FirstName, Overseer.Department FROM
Overseer WHERE (((Overseer.LastName) Is Not Null)) ORDER BY
Overseer.LastName, Overseer.Department;

I want to be able to search in this code by the name. I tried the
following after the first error handler. I don't know where it should
go (I get error 2001, previous ... xceled)

'lookup value for "requestedby"
RequestedBy = DLookup("[LastName]", "Overseer", "[OverseerID]='" &
Forms!OrdersWDetails!RequestedBy & "'")

Thanks in advance!
lmv

'--------FIND CODE---------
Private Sub FindRecordLike(strFindMode As String)
'Copyright 2003 J Street Technology, Inc.
'www.JStreetTech.com
'May be used and distributed without permission if these 3 lines are
included. On Error GoTo Error_Handler

'PO number shows up in search additional fields show up as ID
numbers Call jst_FindRecord(frmCallingForm:=Me, _
ctlFindFirst:=Me!cmdFirst, _
ctlFindNext:=Me!cmdNext, _
ctlSearchText:=Me!txtFind, _
ctlSearchOption:=Me!optFind, _
strFindMode:=strFindMode, _
strField1:="ProjectID", _
strField2:="PurchaseOrderNumber", _
strField3:="RequestedBy")

Exit_Procedure:
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Sub
Click to expand...

I can't make much of the FindRecordLike() and (unposted)
jst_FindRecord() routines, but if your OverseerID field is a number, and
the combo box RequestedBy has a numeric ID field as its bound column,
then your DLookup should drop the quotes around the value:

RequestedBy = DLookup("[LastName]", "Overseer", _
"[OverseerID]=" & Forms!OrdersWDetails!RequestedBy)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

lmv said:
Thank you for the response. Would you be able to tell me WHERE to
place it in the code? I am not proficient enough with VBA... I just
put it at the beginning but it doesn't work that way. Thanks

Dirk Goldgar said:
lmv said:
I am using the FIND code below and it works well on all but the
"requestedby" (which is a combo box which is viewed as a name but
stored as a number.) SELECT DISTINCT Overseer.OverseerID,
Overseer.LastName, Overseer.FirstName, Overseer.Department FROM
Overseer WHERE (((Overseer.LastName) Is Not Null)) ORDER BY
Overseer.LastName, Overseer.Department;

I want to be able to search in this code by the name. I tried the
following after the first error handler. I don't know where it
should go (I get error 2001, previous ... xceled)

'lookup value for "requestedby"
RequestedBy = DLookup("[LastName]", "Overseer",
"[OverseerID]='" & Forms!OrdersWDetails!RequestedBy & "'")

Thanks in advance!
lmv

'--------FIND CODE---------
Private Sub FindRecordLike(strFindMode As String)
'Copyright 2003 J Street Technology, Inc.
'www.JStreetTech.com
'May be used and distributed without permission if these 3 lines are
included. On Error GoTo Error_Handler

'PO number shows up in search additional fields show up as
ID numbers Call jst_FindRecord(frmCallingForm:=Me, _
ctlFindFirst:=Me!cmdFirst, _
ctlFindNext:=Me!cmdNext, _
ctlSearchText:=Me!txtFind, _
ctlSearchOption:=Me!optFind, _
strFindMode:=strFindMode, _
strField1:="ProjectID", _
strField2:="PurchaseOrderNumber", _
strField3:="RequestedBy")

Exit_Procedure:
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Sub
Click to expand...

I can't make much of the FindRecordLike() and (unposted)
jst_FindRecord() routines, but if your OverseerID field is a number,
and the combo box RequestedBy has a numeric ID field as its bound
column, then your DLookup should drop the quotes around the value:

RequestedBy = DLookup("[LastName]", "Overseer", _
"[OverseerID]=" & Forms!OrdersWDetails!RequestedBy)
Click to expand...
Click to expand...

There's not enough information in what you posted to allow me to help
you. I don't know the details of your form, I don't have the definition
of the function jst_FindRecord(), and I have only the foggiest notion of
what you're trying to do. Maybe if you post a lot more information, I
or someone here will be able to help, but you must understand we don't
know your database structure, we can't see your form, and we can't guess
at your code.
 
Top