Search/Find Record dlookup

#1 (**permalink**) 28-Jul-2006, 08:13 AM

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10461-search-find-record-dlookup.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10461
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

#2 (**permalink**) 28-Jul-2006, 08:13 AM

"lmv" wrote in message
news:58230150-E262-45D4-9276-FE4BBC463F73@microsoft.com
> 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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10461
> 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", _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10461
> strField3:="RequestedBy")
>
> Exit_Procedure:
> Exit Sub
> Error_Handler:
> MsgBox Err.Number & ", " & Err.Description
> Resume Exit_Procedure
> Resume
> End Sub

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)

#3 (**permalink**) 28-Jul-2006, 08:13 AM

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" wrote:

> "lmv" wrote in message
> news:58230150-E262-45D4-9276-FE4BBC463F73@microsoft.com
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10461
> > 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
>
> 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)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10461
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

#4 (**permalink**) 28-Jul-2006, 08:13 AM

"lmv" wrote in message
news:454E686C-DCCF-430E-A1EE-555D0831C274@microsoft.com
> 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" wrote:
>
>> "lmv" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10461
>> news:58230150-E262-45D4-9276-FE4BBC463F73@microsoft.com
>>> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10461
>>> 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
>>
>> 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)
>>

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.

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

(please reply to the newsgroup)