Display Current Record On Form 6

[buee04]

Hi,

I'm using Access 2000 to develop my first db. I'm just learning my way through the program, slowly. I want to be able to show the Current Record Number on top of my form. I turned off the record navigation so that it doesn't show at the bottom of the form. Is there a way to accomplish this with a minimal amount of coding because I don't have any experience with vbscripts.

Thanks much!!

 

[MMSantiago]

I would just create a COUNTER field in the main table for that form. Then you just put that field in the form itself and you have your unique number. By default the counter will start at 1 and work its way in order, so they would match your record number until you delete one. At that point, the system won't re-number the records in that field but just keep adding up.

While this is not EXACTLY what you're asking for, it would work as long as you don't delete record and/or have a specific need to go to the 'real' record number. I mean, they would still be UNIQUE numbers and would work the same in most respects. Hope this helps.

 

[krsherm]

buee04,

Please note that the Current Record Number is not a meaningful number... it is only relative. That is, it only identifies the relative position that the record occupies.

Imagine a table (tblCustomers) with only one field (CustomerName). If you have only two records in the table:

ABC Company
XYZ Company

and you open a form based on this recordset, ABC Company will be record 1 and XYZ will be record 2. If you subsequently add a record, AAA Company (and then compact/repair the db... which you should do frequently)...

AAA Company will be record 1
ABC Company will be record 2 'No longer record 1!
XYZ Company will be record 3 'No longer record 2!

You should have a primary key (CustomerID), and that is the value that you would want to display at the top of the form. In order to do that, just place a bound text box at the top of the form, with its controlsource set to CustomerID. Then, each time you move from one record to the next, the CustomerID will be at the top of the form.

The record number should only be used for RELATIVE numbering... its value lies in the fact that it has no value (if that makes any sense).

Let me know if I am off-track. If I am, please clarify your concern and I'll do my best to get you the right answer.

Rock ON!

Kevin

 

[Rolliee]

Do an 'on current event' to your form and let your Me.txtBox1 = me.currentrecord

Rollie

email me if you are interested. I do not manuever well on the forum. I love it but do not do forum's well.

Rollie@bwsys.net

 

[buee04]

Thanks very much to the responses fellas. I think the counter field will work because there won't be any deleting of the records. I didn't even think of doing it that way, and I think I know how to do that

Kevin, I think it'd be a cool feature to add even though it's not very useful, hehe. I wanna be able to show the user when he/she is at the beginning or the end of the records so that they don't try to keep on clicking forward when they're at the end of the records and nothing happens =) Kev's idea's like MM's, and I think I understand how to do this now.

Rolliee, whew. You're talking in a different language here. I actually tried to do something like that reading help files but it didn't work. Lemme email you for the sake of learning.

THANKS AGAIN GUYS!!

 

[Rolliee]

never got your email, did I?

I am rollie@bwsys.net

 

[FoxProProgrammer]

buee04,

If you opened a recordset, you can see if you are at the beginning of file or end of file with recordset.BOF and recordset.EOF respectively. Just thought that I'd toss that out in case you want to incorporate it in your app. You could do this for example. This isn't a full code example...it only illustrates how to use the BOF and EOF properties of a recordset.

Dim rs as Recordset

If rs.BOF Then
MsgBox "You are at the first record.", 64
End If

If rs.EOF Then
MsgBox "End of records.", 64
End If dz

 

[buee04]

Hey Rollie, we've exchanged emails several times already. Jon? Thanks again for all of your help, now I'm displaying the current record along with the total record numbers on my form.

Dz, I did something like that:

Err_Go_Previous_Button_Click:
MsgBox "You are at the first record"
Resume Exit_Go_Previous_Button_Click

Err_Go_to_Next__Button_Click:
MsgBox "No more records"

But where would you put your code? I have the code above in my previous button click and next button click. I guess I can do the same method for go to last button click and go to first button click but your way has less coding.

THANK YOU!

 

[FoxProProgrammer]

Hi buee04,

You could place the code in the OnClick event of each button. If you do it that way, you would only test for BOF in the Previous button event and EOF in the Next button event. Alternatively you could place the code in the Form's OnCurrent event, but you would have to test for both BOF and EOF if you do it there. I haven't done it that way, but don't see why it wouldn't work. The real issue is the scope of the recordset. If the scope is only set to the Sub level, you will have to test BOF and EOF in the Sub. If the scope is set to the Form, then you can test BOF and EOF in any event of the form, including the On Current. I'm not sure if you just paraphrased your code, but you don't have enough there to work.

Best,
dz

 

[buee04]

I probably paraphrased the code accidentally because it works in the form when i reach the beginning/end of records and try to decrement/increment.
But how do you set the scope? I'm still not able to piece together the structure of vb yet. It seems like it's just a bunch of subs and no main function.

Thanks,
j

 

[FoxProProgrammer]

J,

You can set the scope of variables to the form by placing the Dim statement at the top of the VB module, just under Option Compare Database. Anything declared there is scoped to the form. If you want a recordset to live for the life of the form, rather than a Sub, you have to Dim it there. dz

 

[buee04]

Ohh i see. Thanks dz!

 

[LonnieJohnson]

Thanks Rolliie,

The current record thing is simple and gives the position of the record's position. Thanks again. prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.