Row count of Table into VB variable

[Rookie Roger]

Would someone help this Access neophyte with a trivial question? From VB in
Access, how do I grab the number of rows in a data table stored in the same
Access database.

I know a dozen ways to do rowcounts, I just can't figure out how VB requires
it to be done. Utlimately, all I'm doing is creating a unique set of numbers
to that will be stored in a field in each record; it functions as a way to
report names in a non-recognizable way, and I found no encrypt, so now I'm
just puttling in numbers that I generate - yes it could be easily cracked,
that's fine as long as it's not intuitive to a first time, one time reader.

I apologize for asking such a simple question, frustrating.

 

[Al Campagna]

Roger,

I know a dozen ways to do rowcounts, I just can't figure out how VB
requires
it to be done.
...it functions as a way to
report names in a non-recognizable way...

Not sure I understand... sounds like you want some unique value for
each record, stored in the table, that you could use instead of personal
information on a report.

But... each record you have in your table should already have a unique
key identifier field.
An example would be tblEmployees with each employee having a unique
EmpID.
That EmpID could be used to generate a report without the actual Names.


Using A calculated text control...
= DCount("*","tblYourTable")

Using VB...
Public Sub CountRecords()
Dim RecCount as Long
RecCount = DCount("*","tblYourTable")
End Sub

Please clarify firther (using examples) if the above doesn't help...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Rookie Roger]

Thank you Al. That took care of my initial question, very simple indeed. I
do have unique data in each record, it's the person's name, I need the report
to be traceable back to this person but cannot put the persons name, ID or
other easily identifiable jazz in it. This is for an employee review board
which will give accomdation to the top decile and warnings to the bottom
decile. It can't just be encrypted, it needs to be printable, but shouldn't
allow the reviewer to intuit who it is. No patient data, no financial data,
no FRPA, HIPAA or regulations involved, just hiding the persons identity
until the administrators go to send letters.

Maybe I should ask, I have a query to populate this table inserting the name
and a other things...if I could think of a simple function that generates a
unique number or set of letters I'd just plop that into the insert
statements. If needs to be more than just a simple sequence thought, can't
just be random because it must assure uniqueness. So not being able to come
up with that, I'm taking the second approach which is to add code to the VB
script that generates that data and will generate what I need. Thanks, Roger

 

[Rookie Roger]

I'll answer myself now...with no code, the Access AutoNumber Random claims to
produce a unique random number, this is perfect for me. This is
counterintuive to me (Random should mean random and not gaurantee
uniqueness). If anyone knows that the table definition for a field or
AutoNumber Random does not always generate a unique number, please let me
know. Thank you, Roger

 

[Al Campagna]

Roger,
You wrote...

That took care of my initial question, very simple indeed. I
do have unique data in each record, it's the person's name.

Even though your names are unique now, they may not be in the future,
and I would strongly recommend you reconsider your table design.

Create a new empty table, with the same fields as your original table...
but... add and EmpID autonumber field.
Now take your old table and append it completely to the new table.
The autonumber field will generate a unique number on each record, and
that will be your key field. Every new record will have a unique
identifier, and that same field would be used to other empoloyee based
tables (like training, or promotions, or salary history, etc.. etc..)

Also, a simple number would be the easiest lookup from the reports. ex.
EmpID = 126 could be easily cross referenced for the report user, to
represent "Bob Smith."
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."