record count last VISIBLE record

[Jaye7]

I can use the following to return the last record count of the subform records, but I want the last record count of the VISIBLE records only.

So if records 1-15 are visible then it returns 15

If records 34-49 are visible then it returns 49

RecordsetClone.recordcount

 

[Galaxiom]

Unlikely to be possible.

Why would you want it anyway?

 

[Jaye7]

Because my boss wants the textbox to read as showing items 1-5 of 50, however if you click on record 2 it should still read showing items 2-5 of 50 whereas at the moment it reads showing items 2-6 of 50 which is incorrect.

 

[jdraw]

Please show us some code related to the calculation/identification of the records involved.
What is the relationship of your Form and subForm? What is the recordsource of each?

 

[Jaye7]

This is the code that I am using in the form event to populate the results, I can use this in any form regardless of the form recordsource as it just counts the total records

Private Sub Form_Current()

If Me.txtTest = "enabled" Then
Dim S1 As String, S2 As String, S3 As String, s4 As String
S1 = CurrentRecord
S2 = CurrentRecord + 13
S3 = RecordsetClone.recordcount

s4 = Eval(S2 - S3)
If s4 > 0 Then S2 = S3

Me.txtCurrent.Value = "Items - showing " & CurrentRecord & "-" & S2 & "  of  " & RecordsetClone.recordcount & "  items"
Forms!BOMPO.txtTest.Value = Me.txtCurrent
Me.txtTest = Null
End If
End Sub

Then in the form open event I use

DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst

 

[jdraw]

What is Forms!BOMPO?
Where did the 13 come from?
Again, what is the relationship between Form and subForm?

What is the purpose of S1, S2 ...?
What is this really doing? S2 = CurrentRecord + 13

Is S2 suppose to be storing a record number or count?

NOTE: This is from M$oft

The following example shows how to use the CurrentRecord property to determine the record number of the current record being displayed. The general-purpose CurrentFormRecord procedure assigns the value of the current record to the variable lngrecordnum.

Sub CurrentFormRecord(frm As Form)
Dim lngrecordnum As Long
<<<<--- note dimmed as LONG
lngrecordnum = frm.CurrentRecord
End Sub

 

[Jaye7]

Forms!BOMPO is the main form
13 is the amount of records visible in the subform (actually 14 as current + 13)
The relationship between the form and subform is the subform just lists records and then the main form can be used to print reports etc.... based on the subform data, they are not linked.
S1 advises the current record that is selected ie. 20
S2 is S1 + 13 records as that is the number of visible records.

Currentrecord is system based, you just add it in without having to declare it.

 

[jdraw]

Right. But you are defining/dimming S1, S2, S3 .. as strings not numbers (long).

Just curious , if your numbers are off by 1, did you try using 12 instead of 13?
Did you put a breakpoint in your code and step through the code line by line checking the values in your S1, S2 etc?

 

[Jaye7]

The numbers are not off by 1, the problem is that when you select the next record in the subform manually it recalculates based on the record you have selected, I have stopped this from happening by using the script

If Me.txtTest = "enabled" Then

That way they can only run the update script by actually clicking on one of the up/down buttons.

What I actually want is just a script to return the last VISIBLE record in the subform that way it will always be correct, so if someone was to select record 3 of the 14 visible then the last record count would be 14, if they selected record 20000 and 20005 was the last visible record then it would return that number.

The rest of my script isn't important, it was just to show what I am currently using, all I want is code for the last VISIBLE subform record NOT the last subform record, which as a previous poster has said, they don't think that it is possible.

 

[jdraw]

I tend to agree. I'm not sure what last Visible really means.
I wouldn't Dim a variable as String and use it for arithmetic.
You know the structure of your data and subform and readers do not. It seems to me your use of subform is not typical. Usually you have a form and subform where the related tables are in a 1 to Many relationship.

You might get more focused answers if you could post a copy of your database with private info removed, and just enough records to show the problem.

I don't understand how the

If Me.txtTest = "enabled" Then

solves anything. There must be more in the vba that changes or does something.

How are you determining there are 14 records visible. What if only 10 records were available? Again, readers don't know your data.

 

[Jaye7]

The subform height only allows for 14 records to be visible at any given time.
Thanks for your help anyway.

 

[essaytee]

Just throwing this out there, have you considered a paging approach.

Only return the records you want, in your case 14 records. On form, clicking the NEXT button, populates the subform with the next 14 records. I'm initially thinking that your WHERE clause would be based on the first record of each batch and also incorporate the TOP predicate.

Anyway, food for thought.

Steve.

 

[Jaye7]

Hi Steve,
That would be an option but we want to know how many total records there are and the recordsource is set manually/VBA not through a query as my boss likes how much quicker vba is compared to queries, we tested one form and vba took 30 seconds, whereas a query took 35, which seems strange as they are both basically queries, but I couldn't deny the evidence.
5 seconds may not seem like much but it adds up when running the same process 50 times.
We also wanted the users to be able to arrow down through the records rather than having to click the buttons if they wanted to.

 

[essaytee]

That would be an option but we want to know how many total records there are

This is easy to work out, you know how to to that already.

how much quicker vba is compared to queries

Can't comment on this.

We also wanted the users to be able to arrow down through the records rather than having to click the buttons if they wanted to

Make your buttons look like arrows.

I haven't got time at the moment but I'll try and post some pseudo code later.

Steve.

 

[essaytee]

You want to use VBA, well this method, the paging method (my terminology) is VBA driven.

1. Get the total number of records (ie 1000).

2. Divide total records by visible records (ie 15).

3. We now know the total pages involved (1000/15 = 67 (round up)).

4. Every 15th record store the WHERE clause info into an array, will be 67 items.
(this is the starting point for each page's SQL statement)
(this could be the tricky or awkward bit)

5. Each page SQL is created as needed based on the WHERE clause ">=" and the TOP 15 predicate.

OR

4. Create a temporary table in the frontend, transfer all records, (either all fields or just primary key), additional field to be created, PageNumber.
Assign the correct Page number to each record (in temporary table)

5. Create SQL statement based on temporary table with the WHERE clause related to the Page Number.


6. Configure your text boxes as required ie. goto record n, goto page n, Record n of n.

7. Configure buttons accordingly.

8. On exit, if temporary tables utilised, delete them.


I've never implemented such a system before but if I were I would start with the above. The more I think about it, I think the temporary table version would be easier to implement.

Steve.

 

[jdraw]

I think Steve's approach would be a good start. Haven't had anything quite like it either.
It gets a little clearer as you tell us more about WHAT you are trying to achieve. I still think that posting a database with no confidential info, and enough records to highlight the issue would be quickest approach to get some options.

I would question structures and code if it really is taking 30-35 seconds to get a record count. That's a lot of time to get a count.

 

[Jaye7]

Thanks for your help everyone,

The reason why the script takes 30 seconds is because of the massive amount of data and numerous queries to sum, count etc... also there are 5-6 queries within a query and each table has anywhere from 100,000 to 600,000 records.

 

[Galaxiom]

Thirty seconds is still a long time to run a query. That isn't a particularly large number of records.

Have you got appropriate indexes on the tables?

Summing from multiple queries makes me wonder about your data structure.