Count Table Records

[samato]

I have a table of records and want to display the number of records in the table in the same form that enters data into the tbale. I would like this number to increase, obviously, every time I key a new record. Your help is appreciated

 

[Blorf]

If you make an unbound text box, call it Rcount, and make the following the default value

=dcount ("AnyField", "YourTableName", "")

Then, on the forms on current event, and a macro that reads

(Command) requery (Object Name) "Rcount"

You should be in business.

Just out of curriosity though, isn't the record count displayed in the bottom left corner of your form, with the record selectors?

ChaZ



Ascii dumb question, get a dumb Ansi

 

[samato]

This is a great start. I also want to write that record number to the table at the same time. Sorry I want' perfectly clear on this.

 

[Blorf]

You want to include that number in a field on the form? Using the same method I listed above, make the control bound to the field have a default value of the same thing.

Further, using an autonumber field would seem to do the trick.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[willir]

ChaZ has given you the correct answer BUT tell me.

If a user deletes a record, do you want to adjust all the record numbers downwards after the deletion? If the record number is 5 out of 1000 records, are you implying that you want to reduce by one the record number for 995 records?

Most tend to focus on using unique keys. Number of records, and the position within the record set can be calcualted if and when needed.

Ricahrd

 

[samato]

Yes, If I delete a record I want to renumber the table beginning with the next highest number. Thanks

 

[Blorf]

So if you have records 1 - 10, and you delete record 4, you want records 5-10 to be renumberd to 4-9?

What is the application? If it is for reporting or what not, reports can provide line numbers and what not, so I am not sure what you would use this for.

Further, I don't think you can renumber all these records with out writing some VBA code, which I would be happy to help with, but would like to understand more.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[samato]

It is for fantasy football and after about six beers and after deleting several records I need to know where I am. You are correct in your assumption of what I need.

 

[willir]

This would mean you have to renumber update all records. And the updates would have to be done a record at a time. Next, is there a specific sort order to use, or you don't really care about the order. Then, are you okay when the end user retrieves the date, the record number may not be in synch with the order presented?

Dim rst as DAO.recordset, dbs as DAO.database
Dim lngCount as long, lngX as long
Dim strSQL as string

set dbs = currentdb()
'here is the trick, if you want a specific order, the 
'SQL statement must reflect the order
'strSQL = "Your select statement with order by"

'set rst = dbs.openrecordset(strSQL)

'or reference the table, if you are not concerned 
'about the order...

set rst = dbs.openrecordset ("YourTableName")

lngX = 0

with rst

   rst.movelast
   lngCount = rst.recordcount
   'now you have the number of records...
   'similar to the DCount... statment

   rst.movefirst

   while not .eof
      lngX = lngX + 1
      .edit
      !YourRecordNumbebr = lngX
      .update
   loop
end with

rst.close
dbs.close

In order to preserve the record number sequence, you will now have to use the ORDER BY YourRecordNumber field.

 

[Blorf]

The code works, but after thinking about it, you could do it using a query, so long as the number field is indexed with no duplicates allowed.

If after each delete, you run the following update query

UPDATE Table1 SET Table1.[Number] = [number]-1
WHERE (((Table1.Number)>1));


This will reduce each number by one, except where the number-1 already exists.

ChaZ

Ascii dumb question, get a dumb Ansi