CODEWITHSUNDEEP
vbams-access-2007
KateThatcham
KateThatcham

Reputation: 11

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 a beginner really and this is only my third VBA form.

I'm having problems counting the number of records where a field is not null.

We have records which have been read in by machine to dbo_measQuestImport, then my form allows the user to add a batch name for all the records on that date, with several different criteria - "checked x", "checked y" and also records added manually with "no paperwork".

I would like to show the total number of records to be transferred to another table - called Me.NumRecordsToRcards as a number on the form - frmRcards2.

As dbo_measQuestImport is growing with new records being read in, I only want to count the records where the batch name is not null...

The batch name added by the operator always begins with REC, but even if I try to count the number of records which start with REC that doesn't work….

The following don't work - please advise what am I doing wrong? I'm doubtless doing something stupid…!

Tried:

Me.NumRecordsToRcards = DCount("RecNo", "dbo_measQuestImport", ["batch"] = "REC*")

also:

If dbo_measQuestImport.Checked Like "x" Or "y" Then
If dbo_measQuestImport.batch Is Not Null Then
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 is null"))

Please advise, Thank you!!

Upvotes: 0

Views: 1562

Answers (1)

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

When you have an issue of that kind:

  • First make sure you completely frame the problem you are trying to solve. You will never find a solution if the problem isn't perfectly clear to you.
    From what I read, your question is a bit confusing: you give both too much information and not enough for another person to really understand how your data is structured.
    For instance, you tell us the name of the form, talk about transferring data to another table, information that is useless to the problem at hand, but fail to tell us the structure of the table and the field that contain the 'batch' data.
    As long as it's confusing to you, you have no chance to explain it clearly to others.

  • Second, make simple tests: what is the simplest test that would help you understand and solve your problem?
    Create a separate database if necessary with as few data as necessary to test your assumptions.

I'll assume the following structure for your table:

Table dbo_measQuestImport
-  RecNo:   AutoIncrement Primary Key
-  batch:   Text, containing the batch name
-  Checked: Text, containing some information about what was checked

If your problem is: count the number of non-null batch, then indeed, the DCount() function may be what you are looking for.

If that's the case, then the following is all you need:

DCount("batch", "dbo_measQuestImport")

From the documentation, this will return the number of records in the dbo_measQuestImport table where the column batch is not null.

To display that information on a form, add a textbox and simply set its Control Source to =DCount("batch", "dbo_measQuestImport") and that should be enough.

Upvotes: 1

Related Questions