CODEWITHSUNDEEP
ms-accessvba
knot22
knot22

Reputation: 2768

Me. in generic requery function fails

In an MS Access form that has several buttons (used to add/edit/delete data) with VBA code behind them the code at the end of each button's Subroutine refreshes the screen so the user sees the last record they were on prior to clicking the button. Here is a sample of requery code for one of the buttons:

Dim rst As Recordset, RecordToView As String
Set rst = Me.RecordsetClone
RecordToView = CStr(ProducerID)
Me.Requery
rst.FindFirst "ProducerID = " & RecordToView
Me.Bookmark = rst.Bookmark
rst.Close

This code, or a similar version, is currently repeated for all buttons on the form. Here is an attempt to create a generic function so this redundant code can be eliminated:

Public Function RefreshScreen(Field As String, ByVal RecordToView As String)
    Dim rst As Recordset
    Set rst = Me.RecordsetClone
    Me.Requery
    rst.FindFirst Field & " = " & RecordToView
    Me.Bookmark = rst.Bookmark
    rst.Close
End Function

And called like so:

Call RefreshScreen("ProducerID", ProducerID)

However, it gives a "Compile error: Invalid use of Me keyword" message when it hits Me.RecordsetClone in the generic function. What modifications need to be made to the generic function to get it to work?

Upvotes: 1

Views: 135

Answers (2)

HansUp
HansUp

Reputation: 97131

Change the procedure's declaration to expect a reference to the calling form.

Public Function RefreshScreen(ByRef frm As Form, ByVal fld As String, ByVal RecordToView As String)
    Dim rst As DAO.Recordset
    Set rst = frm.RecordsetClone
    frm.Requery
    rst.FindFirst fld & " = " & RecordToView
    frm.Bookmark = rst.Bookmark
    rst.Close
End Function

(As a side note, since you're not returning a value from that function, it could be a subroutine instead. But this should not matter here.)

Then include Me when you call the procedure.

Call RefreshScreen(Me, "ProducerID", ProducerID)

I was surprised by the comment which states that Call() is obsolete in VBA. I'd never heard that before, but you can call the procedure without it:

RefreshScreen Me, "ProducerID", ProducerID

Upvotes: 4

Gustav
Gustav

Reputation: 55961

You could perhaps do like this:

Public Function RefreshScreen( _
    ByRef FormObject As Form, _
    Byval FieldName As String, _
    ByVal RecordToView As Long)

    Dim rst As Recordset

    FormObject.Requery
    Set rst = FormObject.RecordsetClone
    If rst.RecordCount > 0 Then
        rst.FindFirst FieldName & " = " & RecordToView & ""
        If Not rst.NoMatch Then
            FormObject.Bookmark = rst.Bookmark
        End If
    End If
    rst.Close
    Set rst = Nothing

End Function

Then:

RefreshScreen Me, "ProducerID", ProducerID

Upvotes: 0

Related Questions