Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Search/Find Record dlookup

Discussion in 'Information Technology' started by lmv, Jul 28, 2006.

  1. lmv

    lmv
    Expand Collapse
    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
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "lmv" <lmv@discussions.microsoft.com> 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,
    > 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)

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

    (please reply to the newsgroup)
     
  4. lmv

    lmv
    Expand Collapse
    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" wrote:

    > "lmv" <lmv@discussions.microsoft.com> 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,
    > > 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)
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "lmv" <lmv@discussions.microsoft.com> 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" <lmv@discussions.microsoft.com> 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,
    >>> 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)
    >>


    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)
     

Share This Page