Counting of records in a continuous form

[mvorous]

Hello, I have a form that has its default view set to continuous form.
I would like to have a textbox store the record number with each new record that is created by the user. The first record of course, would be "1", the second would be "2", etc. What property of the recordset stores this value please?
Thank you, mvorous

 

[RuralGuy]

RecordCount

 

[wiklendt]

mvorus, in your table, you should create a field with autonumber as it's value. access will then automatically make a new incremental value with each new record.

 

[RuralGuy]

mvorus, in your table, you should create a field with autonumber as it's value. access will then automatically make a new incremental value with each new record.

Just so you are aware that an AutoNumber is *not* guaranteed to be an unbroken, contunuous sequence.

 

[Mike Vytal]

I agree with Rural Guy. With the autonumber field, if someone creates a record then deletes it, it will throw off the count.

 

[wiklendt]

yes, but you don't want the record number to be reused, even if a record has been deleted. and its a quick and easy way to get unique numbers that appear automatically... and what happens if you do it a different way, and then delete one record - do you want to go back and change all the other record numebrs???? surely not!!

 

[mvorous]

I do want the record to be reused. If it is deleted "which is done every 6 hrs" then I still want the count to be sequential. I can't seem to find to much on recordcount, if I may, an example please. And thanks to all.
mvorous

 

[wiklendt]

what if you have 1500 records, and you delete record #2? then you will need to have a setup where you renumber 1499 records such that each is sequential... and you'll have issues linking relative data. the numbers will still be sequential, just not consecutive.

can you tell us more about what this number will represent and how the data is changed? i'm not sure i understand.

 

[georgedwilkinson]

This is a moderately advanced topic since getting the record count is not wizard based. I assume you know VB or some advanced language. There is a nice writeup on recordcount here: http://www.adopenstatic.com/faq/recordcountalternatives.asp.

Not sure why you think you need it but be advised: It's not for the faint of heart.

 

[mvorous]

This may help, in a form at the bottom, if "navigation buttons" are allowed, you have "Record, 1 of 50 & 2 of 50. I need to tap into that. Jeez, I know VB allows this but does Access? I sure appreciate the help, mvorous.

 

[RuralGuy]

Here's a link that you may find useful:
http://www.access-programmers.co.uk/forums/showthread.php?t=97789

 

[dallr]

There are a number of ways to do this. You can simply place the Dcount function in the following events on your continuous form. Then let the value returned by the Dcount be passed to your unbound form control (YourFormControl). The table or query used in the dcount should be the same one that is bound to your form "Record Source"

Private Sub Form_AfterDelConfirm(Status As Integer)
    'So when the user deletes a record the control is updated
    Me.YourFormControl = DCount("*", "yourtable")
End Sub

Private Sub Form_AfterInsert()
    'So when the user adds are new record the control is updated.
    Me.YourFormControl = DCount("*", "yourtable")
End Sub

Private Sub Form_Open(Cancel As Integer)
    'Show the number of records when the form is open.
    Me.YourFormControl = DCount("*", "yourtable")
End Sub

Dallr

 

[peekaboo_01]

This may help, in a form at the bottom, if "navigation buttons" are allowed, you have "Record, 1 of 50 & 2 of 50. I need to tap into that. Jeez, I know VB allows this but does Access? I sure appreciate the help, mvorous.

You are all getting very close to a problem that I have been having with my own form!! I have a form that is auto-numbered. What happens is that someone opens the form the autonumber will provide the next number is sequence (let's say #6) but the navigation button on the bottom will say Record 1 of 1. I have been trying to figure out how to make the number on the bottom match the record number.

I also wondered if I could use the same form to "query" and pull up the data set that belongs to #6 and edit as appropriate.....suggestions?

p.s.-I am an Access newbie. Altho I'm learning quickly, I struggle with Visual Basic as I have had no classes and have difficulty finding info online. This forum has been the best help ever!!!

 

[dallr]

Why do you want the autonumber to match the number on the record selectors?

Autonumbers are really for internal database use and they should not be seen or used by the end user as a general rule.

Dallr

 

[mvorous]

I don't want the auto #. I have users assign large profiles to specific people, rather than use the peoples names, I have it set up for the assigner to simple pick a number. Person 1 would equal record 1, etc. It is very fast this way. I do it in VB but here in Access, it is becoming a nightmare. I can't seem to get the RecordCount to work for me. If a user goes in and changes the "lineup", it throws off the count - It should always be sequential with no missing numbers 1 2 3 4 5 6 7, etc.

 

[dallr]

My last post was for peekaboo and not your Mvorous.

Mvorous
It is my error, I misunderstood your question so discredit my first post.
My question to you now is. Please explain in detail what it is you are trying to do. .....expand on the following because I am not understanding this fully.

I have users assign large profiles to specific people, rather than use the peoples names, I have it set up for the assigner to simple pick a number. Person 1 would equal record 1, etc.

 

[peekaboo_01]

I don't want the auto #. I have users assign large profiles to specific people, rather than use the peoples names, I have it set up for the assigner to simple pick a number. Person 1 would equal record 1, etc. It is very fast this way. I do it in VB but here in Access, it is becoming a nightmare. I can't seem to get the RecordCount to work for me. If a user goes in and changes the "lineup", it throws off the count - It should always be sequential with no missing numbers 1 2 3 4 5 6 7, etc.

Sorry Mvorous...I guess the reply was for my post on your forum. Your question is a good one and I'm beginning to rethink it. We currently have an "incident" and it is numbered sequentially. When time allows, it is entered into our database and the number on the incident should be the next number in the table that my form is related to. This is our double check to see if numbers are missing. Previously we manually numbered the table as we input the data. I am in the process of recreating the database and using a "form" instead of a table to input. ERGO, when you open the form it gives you the next number (via auto number) and then you put the information in that matches that number. Clear as mud?

Should I start another thread for this??

 

[mvorous]

Hey, I remembered an old method, probably not too sophisticated but... it works.
Private Sub ApplyCount_Click()
Dim myAdder
Dim mycounter
DoCmd.GoToRecord , , acFirst
myRecordIndex = 1
mycounter = Me.RecordsetClone.RecordCount
Do While myRecordIndex < mycounter + 1
DoCmd.GoToRecord , , acNext
myRecordIndex = myRecordIndex + 1
Loop
End Sub

 

[wiklendt]

again, mvorous, i can't see the logic in re-assigning numbers if they are constantly deleted. if these numbers are supposed to be representing people, then i would get bloody confused if the number "3" was a different person every day.

to quote you on a previou post in this thread, mvorous:

if it is deleted "which is done every 6 hrs"

the only application i can see for this is as a hospital room number for patients (where "3" would be the patient in room three), but again, this would be ideally done a different way.

i would much easier remember people by their names. was this specifically asked of you by your boss?? not good. not good.

 

[wiklendt]

not to mention that if you delete person "3", then person "4" becomes "3" and 5 becomes 4 (to keep the numbers consecutive as you've stated is one of your 'musts')..... i just don't get how this is at all useful - can someone please explain this to me, as i find it rediculous....