Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Cedar Falls, Iowa, USA
    Thanked 0 Times in 0 Posts

    creating function with arguments (A2003)

    This is my first function and I'm trying to make it portable by making the form name, control and primary key as arguments to a Lookup function but don't know how to do that or the right syntax to call it. Anyone with time to teach a newbie?

    Public Sub LookupGrtProj()

    Dim objRs As Object
    Dim frm As Form
    Dim strSearch As String

    Set frm = Forms!frmMainInd!fsubGrtProj.Form

    strSearch = "[lngGrtProjID] = " & Str(Nz(frm!cboFind, 0))

    frm.Detail.Visible = True

    Set objRs = frm.Recordset.Clone
    objRs.FindFirst strSearch
    If Not objRs.EOF Then
    frm.Bookmark = objRs.Bookmark
    End If

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: creating function with arguments (A2003)

    The approach in the original post is more standard for Access 2000 and later.

  3. #3
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Los Angeles, California, USA
    Thanked 2 Times in 2 Posts

    Re: creating function with arguments (A2003)

    I'm no Access guru, but give this a try. You'll note that I changed Recordset.Clone to RecordsetClone. Either will work, but I believe the latter is the more common approach when you're dealing with the recordset behind an Access form. I also updated the Str() function to the CStr() function.
    <pre>Public Sub LookupGrtProj(frm As Access.Form, _
    strField As String, ctlX As Access.Control)

    Dim rstX As DAO.Recordset
    Dim strSearch As String

    strSearch = "[" & strField & "] = " & CStr(Nz(ctlX, 0))

    frm.Detail.Visible = True

    Set rstX = frm.RecordsetClone
    rstX.FindFirst strSearch
    If Not rstX.EOF Then
    frm.Bookmark = rstX.Bookmark
    End If

    Set rstX = Nothing

    End Sub

    Public Sub TestLookupGrtProj()

    Call LookupGrtProj(frm:=Forms!frmMainInd!fsubGrtProj.Fo rm, _
    strField:="lngGrtProjID", _

    End Sub</pre>

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: creating function with arguments (A2003)

    Here is a late reply, the sample code below serves similar purpose only first opens form, then (optionally) searches for a record based on specified key field and value:

    <code>Public Sub OpenFormEx(ByRef sFormName As String, _</code>
    <code> Optional sOpenArgs As String = "", _</code>
    <code> Optional sKeyFld As String = "", _</code>
    <code> Optional KeyVal As Variant, _</code>
    <code> Optional bClearFilter As Boolean = False)</code>
    <code> On Error GoTo Err_Handler</code>

    <code> Dim frm As Form</code>
    <code> Dim rst As DAO.Recordset</code>
    <code> Dim strMsg As String</code>
    <code> ' sKeyFld = name of Primary Key in table, KeyVal = value to locate</code>

    <code> If Len(sOpenArgs) = 0 Then</code>
    <code> DoCmd.OpenForm sFormName</code>
    <code> Else</code>
    <code> DoCmd.OpenForm sFormName, , , , , , sOpenArgs</code>
    <code> End If</code>

    <code> If (Len(sKeyFld) > 0) And (Not IsNull(KeyVal)) Then</code>
    <code> Set frm = Forms(sFormName)</code>
    <code> </code>
    <code> If bClearFilter = True Then</code>
    <code> If frm.FilterOn = True Then frm.FilterOn = False</code>
    <code> End If</code>
    <code> </code>
    <code> Set rst = frm.RecordsetClone</code>
    <code> </code>
    <code> Select Case TypeName(KeyVal)</code>
    <code> Case "String"</code>
    <code> rst.FindFirst "<!t>[" & sKeyFld & "]<!/t> = '" & KeyVal & "'"</code>
    <code> Case "Integer", "Long", "Single", "Double"</code>
    <code> rst.FindFirst "<!t>[" & sKeyFld & "]<!/t> =" & KeyVal</code>
    <code> Case "Date"</code>
    <code> rst.FindFirst "<!t>[" & sKeyFld & "]<!/t> = #" & KeyVal & "#"</code>
    <code> End Select</code>
    <code> </code>
    <code> If Not rst.NoMatch Then</code>
    <code> frm.Bookmark = rst.Bookmark</code>
    <code> Else</code>
    <code> strMsg = "Record not found."</code>
    <code> MsgBox strMsg, vbExclamation, "NOT FOUND"</code>
    <code> End If</code>
    <code> Else</code>
    <code> ' take no action</code>
    <code> End If</code>

    <code> Set frm = Nothing</code>
    <code> Set rst = Nothing</code>
    <code> Exit Sub</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> MsgBox strMsg, vbExclamation, "OPEN FORM ERROR MSG"</code>
    <code> Resume Exit_Sub</code>
    <code>End Sub</code>

    Note that the VBA TypeName function is used to determine the datatype (as string) of the Variant value, and delimits (or doesn't delimit) the key value as required for search string. I devised this sub because found myself repeating almost identical code over & over to open a form & navigate to specific record. If optional parameters omitted the form simply opens. You may be able to adapt this for a "generic" locate-record function.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts