Access 2007 vba - how do I count number of records in one of several tables and show total on form so it updates automatically? -

first of all, apologies - i'm beginner , third vba form.

i'm having problems counting number of records field not null.

we have records have been read in machine dbo_measquestimport, form allows user add batch name records on date, several different criteria - "checked x", "checked y" , records added manually "no paperwork".

i show total number of records transferred table - called me.numrecordstorcards number on form - frmrcards2.

as dbo_measquestimport growing new records being read in, want count records batch name not null...

the batch name added operator begins rec, if try count number of records start rec doesn't work….

the following don't work - please advise doing wrong? i'm doubtless doing stupid…!

tried:

me.numrecordstorcards = dcount("recno", "dbo_measquestimport", ["batch"] = "rec*") 

also:

if dbo_measquestimport.checked "x" or "y" if dbo_measquestimport.batch not null forms!frmrcards2.numrecordstorecards = dcount("[recno]", "dbo_measquestimport") 

also:

me.numrecordstorcards = count(dbo_measquestimport.batch) 

also (in desperation!):

me.numrecordstorcards = sum (dcount("recno", dbo_measquestimport") - dcount ("recno",   "dbo_measquestimport", "batch null")) 

please advise, thank you!!

when have issue of kind:

• first make sure frame problem trying solve. never find solution if problem isn't clear you.
  read, question bit confusing: give both information , not enough person understand how data structured.
  instance, tell name of form, talk transferring data table, information useless problem @ hand, fail tell structure of table , field contain 'batch' data.
  long it's confusing you, have no chance explain others.

• second, make simple tests: simplest test understand , solve problem?
  create separate database if necessary few data necessary test assumptions.

i'll assume following structure table:

table dbo_measquestimport -  recno:   autoincrement primary key -  batch:   text, containing batch name -  checked: text, containing information checked 

if problem is: count number of non-null batch, indeed, dcount() function may looking for.

if that's case, following need:

dcount("batch", "dbo_measquestimport")  

from documentation, return number of records in dbo_measquestimport table column batch not null.

to display information on form, add textbox , set control source =dcount("batch", "dbo_measquestimport") , should enough.