SQL Code behind a button on a tabpage subform

  • Thread starter Thread starter Mr. Moxie
  • Start date Start date
M

Mr. Moxie

I am new to access.

I need to have a button on my tab page, that when clicked will do an SQL of
a table (in the same .mdb) and count the number of records in that database
(Select Count(*) from Employee;) I need to have this value available as an
integer in the button script. I will then need to set a field in the
existing record to that value.

How do I code the Select inside of the button, and how do I get at the
returned value as an integer?

Thank you.
Darrel
 
You can use this SQL for counting a record from your employee table:
SELECT Count(Employee.ID) AS CountOfID FROM Employee;
You can not count * in the employees table.
 
Hi Darrel,

It's usually a bad idea to store something like this in a table, because
the stored count may be invalidated at any time by a change in the other
table. Instead, normal practice is to calculate counts and totals (and
derived values of any kind) as and when needed.

The simplest way to get the count of records in a table into VBA code is
to use the DCount() function, e.g.

Dim lngEmployeeCount As Long
lngEmployeeCount = DCount("ID", "Employee")
 
Back
Top