How can I send a form as a parameter to a function

How can I send a form as a parameter to a function. for example:

Function Test(ByVal frm as Form)

    Set RS = frm.RecordsetClone

Open in new window

LVL 28
MacroShadowAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You cannot call it from the Immediate window.  You need to call it from within a Form.

Well ... you COULD ... call it from the  Immediate window like this:

?CountSelectedRecords(Forms!SomeFormName.Form)

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Call YourFunctionName(me)

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Call Test (me)


This will pass the Form object you are currently in.

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MacroShadowAuthor Commented:
I'm not looking to call the function from elsewhere, I want to provide a parameter for the function itself.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I got it.  Your Function can be where ever.  What I will posted will pass the Form object to the function.

mx
0
 
MacroShadowAuthor Commented:
This is my function:
Function CountSelectedRecords(ByRef frm As Form) As Integer
    Dim i As Long
    Dim F As Form
    Dim RS As Recordset
    Dim intSelRec As Integer

On Error GoTo Err_Catch
    ' Get the form and its recordset.
'    Set F = frm
    Set RS = frm.RecordsetClone

    ' Move to the first record in the recordset.
    RS.MoveFirst

    ' Move to the first selected record.
    RS.Move F.SelTop - 1

    ' Enumerate the list of selected records
    For i = 1 To F.SelHeight
        intSelRec = intSelRec + 1
        RS.MoveNext
    Next i
    
    CountSelectedRecords = intSelRec
'    MsgBox CountSelectedRecords
    
    Exit Function
    
Err_Catch:
    CountSelectedRecords = 0
    
End Function

Open in new window

When I call it from the immediate window:
?CountSelectedRecords(SubDetails)

Open in new window

I get a compile error: ByRef argument type mismatch.
0
 
Gustav BrockCIOCommented:
Let me guess: SubDetails is the name of a subform control.
If so:

intRecords = CountSelectedRecords(SubDetails.Form)

By the way, I believe your function could be reduced to:
Function CountSelectedRecords(ByRef frm As Form) As Integer
  
    CountSelectedRecords = frm.SelHeight

End Function 

Open in new window

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Guess this has changed  quite a bit from the test function ... :-(

mx
0
 
MacroShadowAuthor Commented:
Thanks to both of you.

DatabaseMX: it works.

cactus_data: you're right!

How can I use it to display the result in a textbox, re-querying each time a record is selected/deselected (without using the timer)?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yikes .... scope creep, lol ....
Gustav ... take over.  You're just up and I'm zzzzzzz ...
later.mx
0
 
Gustav BrockCIOCommented:
This works 98%. You can fool by pressing tha tab key:
Private Sub Form_Current()

    Me!txtSelected.Value = Me.SelHeight
    
End Sub

Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
    
    Me!txtSelected.Value = Me.SelHeight

End Sub

Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

    If Me.NewRecord = True Then
        Me!txtSelected.Value = 0
    Else
        Me!txtSelected.Value = Me.SelHeight
    End If
    
End Sub

Open in new window

/gustav
0
 
MacroShadowAuthor Commented:
I get a runtime error 2448: You can't assign a value to this object.
0
 
Gustav BrockCIOCommented:
Textbox txtSelected must be unbound.

/gustav
0
 
MacroShadowAuthor Commented:
Right, I just figured that out. Tried to edit the post but you beat me to it.
0
 
MacroShadowAuthor Commented:
This works 98%. You can fool by pressing the tab key:
In what case will it not work?
0
 
Gustav BrockCIOCommented:
Well, the last 1% is when you select a record and move to a field on the same record.
You could cut that by adding some code to the OnEnter events of the text/combo/check boxes of the fields.

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW,

  [Form] works too (in place of Me)

  Just in case you run over that somewhere else...

Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Just for kicks, I thought I would note that ... the original Q was answered in the first post :-)
Just sayin '......

mx
0
 
MacroShadowAuthor Commented:
I hope the grading was fair.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
no problem.  

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.