Pass form name to function

[moishy]

I'm using the following function to count the number of selected records in a datasheet or continuous form view.

Function CountSelectedRecords()
    Dim i As Integer
    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 = Forms!MyForm
    Set RS = F.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
    
    Exit Function
Err_Catch:
    CountSelectedRecords = Null
    
End Function

It works as expected.

Now I'm trying to make it more versatile by passing a form name to the function instead of hard-coding the form in the function.

I tried using a string ("Forms!" & FormName) instead of MyForm in this line

Set F = Forms!MyForm

But it doesn't work.

All help will be appreciated!

 

[jdraw]

I'm not sure how your original worked. You don't open a recordset.

I tried your code with one of my forms that I opened prior to running the code.

It didn't check anything, just went to the err_catch routine.

I think you would have to look at open forms; iterate through the names till you match the one you want; run some sql based on the record source of the form....
just a guess.

 

[mdlueck]

Do the forms have some sort of record filtration capabilities that it would be necessary to count up the records in the form (current view of the form) rather than issue a simple SQL COUNT(*)?

 

[vbaInet]

And all you need to count the selected records in a form that's in Datasheet view or as a Continuous Form is SelHeight. Nothing else.

 

[boblarson]

This is the way I would do it to make it reusable and easy

Function GetRecCount(frm As Form) As Long
    GetRecCount = frm.Recordset.RecordCount
End Function

The form has to be open though.

 

[boblarson]

And all you need to count the selected records in a form that's in Datasheet view or as a Continuous Form is SelHeight. Nothing else.

???? Can you elaborate?

 

[vbaInet]

???? Can you elaborate?

I think the OP is counting the number of rows highlighted or selected in a Datasheet or Continuous form. I don't think it's a record count he/she is after.

So to get the number of highlighted/selected records in that view, one would do:

Msgbox Me.SelHeight

 

[boblarson]

I think the OP is counting the number of rows highlighted or selected in a Datasheet or Continuous form. I don't think it's a record count he/she is after.

So to get the number of highlighted/selected records in that view, one would do:

Msgbox Me.SelHeight

Ah, that makes sense. Thanks.

 

[moishy]

@jdraw
I set the recordset to a clone of the forms recordset, that should suffice.
I don't know what went wrong with your test, rem the on err line and see what happens.

@mdlueck AND boblarson
As VbaInet pointed out, I am after the count of selected records not the recordcount.

@VbaInet
I tried your suggestion as a control source for a text box: =Me.SelHeight. It produces a #Name? error.

 

[boblarson]

@VbaInet
I tried your suggestion as a control source for a text box: =Me.SelHeight. It produces a #Name? error.

The control of the text box isn't where it should go. ME is ONLY available for use in VBA (VBA Window). It would go there. So put it in the Form's On Current Event in the VBA Window, not in the Event PROPERTY.

 

[moishy]

Ok my mistake.

It still doesn't work, numbers are shown but they don't have anything to do with reality (never will it show less that 2, usually it shows numbers a lot higher).

 

[moishy]

It has to go on the form's on timer event.

 

[vbaInet]

If you want it in the Control Source you use =Form!SelHeight, then requery the textbox when needed. You can use a combination of the Click and Mouse Up events of the form to do this.

 

[moishy]

Can't get ti to work. It produces a #Name? error.

 

[vbaInet]

Did you type Form or Forms? I've just tested it and it works for me.

 

[moishy]

where exactly to I requery the textbox, so it shows the correct number of selected records?

 

[vbaInet]

As I mentioned in my last post, it would be a combination of the form's Click and Mouse Down events. I think you're better off doing the calculation in code instead of relying on requerying the textbox. And you can still use the Click and Mouse Down events to get the appropriate responses instead of using a Timer. Some aircode:

Declared as a global variable in form module:

private blIsHighlighting as boolean

Code in the Click event of form:

blIsHighlighting = true

Code in MouseDown event of form:

if blIsHighlighting then me.txtbox.value = me.selheight
blIsHighlighting = false

 

[moishy]

Tried your suggestion. It doesn't do a thing (never goes beyond 1).

 

[vbaInet]

* Are you highlighting in code or manually?
* What view are you opening the form in?

 

[vbaInet]

Ah sorry, I meant Mouse Up not Down.