Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9773

Re: DLookup (subform) & GoToRecord (main form)

Show all headers | View raw

"croy" <croy@invalid.net> wrote in message 
news:k7bku9dr5hpk7n1lhpn37r8t5lr3jlvstj@4ax.com...
> I got it!  Thanks to your help  ;-)
>
> Here's what I ended up with:
>
> *****
>    Dim strWhere As String
>
>    strWhere = DLookup("DE1Id", "tblDE1Det", "[HTnum1] = " &
> Chr(34) & Forms!frmDE1.Form!txtHTDE1LU & Chr(34))
>
>    With Me.RecordsetClone
>        .FindFirst "DE1Id =" & strWhere
>        If .NoMatch Then
>            MsgBox "not found"
>        Else
>            Me.Bookmark = .Bookmark
>        End If
>    End With
> *****
>
> Life is good.
>
> Thanks again for all your help!
>
> -- 
> croy

I got tired worrying about quotes in dlookup calls so I modified Allen 
Browne's eLookup function to use parameter queries.

strWhere = pLookup("PARAMETERS LookupID Text; Select tblDE1Det.DE1Id from 
tblDE1Det  WHERE [HTnum1] = LookupID;", vbNullstring, vbNullString, 
Forms!frmDE1.Form!txtHTDE1LU )

Call will work with any value in Forms!frmDE1.Form!txtHTDE1LU  except null

Public Function pLookup(strExpr As String, strDomain As String, strCriteria 
As String, _
                        strOrderClause As String, ParamArray vaParameters() 
As Variant) As Variant

    On Error GoTo Err_pLookup
    'Purpose:   Modify elookup require parameters in the query
    'Arguments: strExpr         Column to return
    '           strDomain       Query
    '           strCriteria     Extra search criteria
    '           strOrderClause  Extra order by, to return specific if more 
than 1 record matches
    '           ParamArray      List of query parameter values
    'Return:    Value of the Expr if found, else Null or #Error.

    'Example:
    '1. To search the saved parameter query
    '  pLookup("[ID]", "qryContactLookup", "","", "JOHN'S SERVICES")

    '2. Search with built up query,
    '   note: strCriteria and strOrderClause will be ignored if PARAMETERS 
is at start of strDomain
    '  pLookup("[ID]", "PARAMETERS ContactName Text ( 255 );
    '                   SELECT TOP 1 tblContact.ID FROM tblContact
    '                   WHERE tblContact.Name1=[ContactName];", 
vbNullString, vbNullString, "JOHN'S SERVICES")
    'Note:      Requires a reference to the DAO library.

    Dim db As Database
    Dim rs As Recordset
    Dim qryMyQuery As QueryDef
    Dim SQLStg As String
    Dim i As Integer

    If UCase(Left$(strDomain, 10)) = "PARAMETERS" Then
        SQLStg = strDomain
    Else
        'Build the SQL string.
        SQLStg = "SELECT TOP 1 " & strExpr & " FROM " & strDomain

        If strCriteria <> vbNullString Then

            SQLStg = SQLStg & " WHERE " & strCriteria

        End If
        If strOrderClause <> vbNullString Then

            SQLStg = SQLStg & " ORDER BY " & strOrderClause

        End If
        SQLStg = SQLStg & ";"
    End If

    'Lookup the value.
    Set db = dbLocal()
    Set qryMyQuery = db.CreateQueryDef("", SQLStg) ' Create a temp query 
from SQL string
    For i = 0 To UBound(vaParameters())
        qryMyQuery.Parameters(i) = vaParameters(i)
    Next

    Set rs = qryMyQuery.OpenRecordset(dbOpenForwardOnly)

    If rs.RecordCount = 0 Then
        pLookup = Null
    Else
        pLookup = rs(0)
    End If
    rs.Close

Exit_pLookup:
    Set rs = Nothing
    Set db = Nothing
    Set qryMyQuery = Nothing
    Exit Function

Err_pLookup:

    Debug.Print  "modzDownloadAllenBrowne.pLookup error " & Err.Number & ", 
" & Err.Description
    On Error Resume Next
    Debug.Print   "strExpr = " & strExpr
    Debug.Print   "strDomain = " & strDomain
    Debug.Print   "strCriteria = " & strCriteria
    Debug.Print   "strOrderClause = " & strOrderClause
    For i = 0 To UBound(vaParameters())
        Debug.Print   "vaParameters(" & i & ") = " & vaParameters(i)
    Next

    pLookup = Null ' CVErr(5)
    Resume Exit_pLookup

End Function 

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-06 08:13 -0700
  Re: DLookup (subform) & GoToRecord (main form) JB Guernsey <jeff@angelsystems.co.uk> - 2014-08-06 09:37 -0700
    Re: DLookup (subform) & GoToRecord (main form) "Phil" <phil@stantonfamily.co.uk> - 2014-08-06 21:23 +0100
  Re: DLookup (subform) & GoToRecord (main form) JB Guernsey <jeff@angelsystems.co.uk> - 2014-08-06 14:09 -0700
    Re: DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-11 06:30 -0700
      Re: DLookup (subform) & GoToRecord (main form) "Phil" <phil@stantonfamily.co.uk> - 2014-08-11 16:23 +0100
        Re: DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-11 11:07 -0700
          Re: DLookup (subform) & GoToRecord (main form) "Phil" <phil@stantonfamily.co.uk> - 2014-08-11 22:39 +0100
            Re: DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-12 06:30 -0700
              Re: DLookup (subform) & GoToRecord (main form) croy <croy@invalid.net> - 2014-08-12 08:07 -0700
                Re: DLookup (subform) & GoToRecord (main form) "Ron Paii" <None@none.com> - 2014-08-13 08:17 -0500

csiph-web