=[CurrentRecord] returns wrong number??

[BadSikander]

Hello.

I'm using an unbound text box with a datasource set to =[CurrentRecord] to show the number of the record currently being shown on my form. This has worked on all my forms in the past, but I am having difficulty with it on one particular form.

There are 25 records in the underlying table and query.

The form displays all the records correctly and in the exact order they are held in the query the form is based on.

However, my unbound text box for the first record shows this as record 2.

The rest of the records then follow this sequentially up to the 24th record (i.e. they are numbered 2-25 in the unbound text box). The real 25th record is then also numbered as 25.

So, according to my unbound text box, I have no record number 1, and two record number 25s!

Would anyone be able to suggest why this is happening?

Thanks in advance for any help.

 

[missinglinq]

I've never had this problem, but I have heard that if you do experience it, it sometimes help to go to the last record before using CurrentRecord. So something like:

Private Sub Form_Load()
  DoCmd.GoToRecord , , acLast
  DoCmd.GoToRecord , , acFirst
End Sub

Good Luck!

 

[BadSikander]

I've never had this problem, but I have heard that if you do experience it, it sometimes help to go to the last record before using CurrentRecord. So something like:

Private Sub Form_Load()
  DoCmd.GoToRecord , , acLast
  DoCmd.GoToRecord , , acFirst
End Sub

Good Luck!

Hello.

Thanks very much for the suggestion. Unfortunately, the code didn't make a difference in this instance.

However, after trying the code out, I did happen to switch my text box from the Page Footer to the Detail part of the form where it mysteriously started working properly. Your suggestion indirectly led to me discovering that, so many thanks!

 

[missinglinq]

Glad you got it to work, but still confused as to why it doesn't work in the footer! Does in the demo I ran up! Oh, well, Access can be fickle at times!

 

[stopher]

After you mentioned footer I was able to replicate what your problem. My meagre effort at explanation is this. I reckon that because the [currentrecord] is in the footer, by the time the report reaches this point, the record counter has already been incremented by 1 (because we have exited the detail section in system terms and it has moved to the next record). When it gets to the last record there is no next record and therefore cannot increment the count (hence 25 twice). I know it’s loose but hey

If you are using the page section on forms then I can only assume you are printing the form. Personally I would create a report rather than try to print the form. While this might seem extra effort, the ability to format the output the way you want is considerably better in reports than it is in forms imo. You dedicate your form to being a user interface and the report to being output. Marrying the two is hard work (much like real marriage ).

At the end of the day if it’s now working, I guess that’s what counts!

Chris

 

[BadSikander]

After you mentioned footer I was able to replicate what your problem. My meagre effort at explanation is this. I reckon that because the [currentrecord] is in the footer, by the time the report reaches this point, the record counter has already been incremented by 1 (because we have exited the detail section in system terms and it has moved to the next record). When it gets to the last record there is no next record and therefore cannot increment the count (hence 25 twice). I know it’s loose but hey

If you are using the page section on forms then I can only assume you are printing the form. Personally I would create a report rather than try to print the form. While this might seem extra effort, the ability to format the output the way you want is considerably better in reports than it is in forms imo. You dedicate your form to being a user interface and the report to being output. Marrying the two is hard work (much like real marriage ).

At the end of the day if it’s now working, I guess that’s what counts!

Chris

Thanks for the explanation. Seems to make sense.

I do normally use reports for printing, but in this case I had painted myself into a corner and needed the form itself to print out (I needed huge numbers of fields that exceeded the maximum for a report).

Thanks for the help.