Search/Find Record dlookup

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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)
 
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...
 
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.
 
Back
Top