On a form, how do I count the number of fields in one recored that are not empty

markzaal

Registered User.
Local time
Today, 14:06
Joined
Jan 15, 2013
Messages
50
(Access 2010)

Hi,

I have a table with 22 fields:

- 1 ID (autonumber)
- 1 Date (date/time)
- 10 x reportnumber (text)
- 10 x description (text)

I also have a form to populate this table.

I would like an unbound textbox on the form to tell me the number of reportnumbers I have entered on the form (maybe On Got Focus or something like that).

How would I go about this!?

Many thanks!
 
Hello markzaal, I am quiet sure that you might want to consider restructuring your table, the current structure is a bit messy.. You might be able to achieve this by using (splitting the current table into) two tables..

userTbl:
ID - AutoNumber (PK)
subDate - Date/Time

relatedUserTbl:
rID - AutoNumber (PK)
uID - Number (FK)
reportNum - Text
reportDesc - Text

userTbl (1)-(*) relatedUserTbl 'this is the relationship

Research on Normalization, it might be the right place to start.. The reason behind this is, if you have this structure.. The info you wish to have can be accomplished by a very simple DCount..
 
Hello Paul,

Would I still be able to have 10 fields for reportnumbers and 10 for the accompanying description on a single form this way?
I understand the advantage of normalization but I would rather not change the entire stucture anymore...

I found the following code that should do what would like to have it done, but it does not seem to work completely:

Code:

1- You have textbox controls on your Form that are named Dep1, Dep2,
Dep3......Dep19

2- Create an unbound textbox control named "txtDepositCount". This control
will have the result of the number of deposits counted

3- With the Form open in design view select the menu option View > Code

4- Copy paste the following code within the VBA module

Function CountNumberOfDeposits()
' i & j are used as counters
Dim i As Integer, j As Integer

' set j = 0 initially
j = 0

' loop thru ALL the controls on the Form
' you never know you made more deposits in the future
' like Dep20, Dep22, Dep23.... plus it less lines of code
For i = 0 To Me.Count - 1
' Dep1, Dep2,etc.. are Textbox controls so test to see if
' control is a textbox
If TypeOf Me(i) Is TextBox Then
' check to make sure the 1st three characters
' of the textbox control is named "Dep"
If Left$(Me(i),3) = "Dep" Then
' check to see if "Dep##" is not null
' don't want to count a control that is Null
If Not IsNull(Me(i)) Then
' start your counter
j = j + 1
End If
End If
If
Next i

txtDepositCount = j

End Function
' xxxxxx end of code xxxxxxxx

5- Now to run the above code on your Form you can do it a couple of ways.
You can place the following:
=CountNumberOfDeposits()

in the OnClick event of command button on your Form. When the user wants
to see the updated Deposit count appear in the textbox control
"txtDepositCount" they would click the button

OR

you can place the same in the AfterUpdate event for each of the textbox
controls Dep1, Dep2, Dep3, etc..... So that has the user enters a value in
one the textbox controls the function will run returning the value to the
textbox control "txtDepositCount"


Any idea on how to get this to work maybe?

Thanks,

Mark
 
No, Based on the table structure I have provided your sample data will be like..
Code:
[U][B]userTbl:[/B][/U]
ID    subDate
1    03/01/2012
2    05/01/2012
3    06/01/2012

[U][B]relatedUserTbl:[/B][/U]
rID  uID    reportNum    reportDesc
1    1    Aug2012        August Part
2    1    Oct2012        October Part
3    1    Jan2013        January Part
4    2    Aug2012        August Part
5    3    Aug2012        August Part
6    3    Oct2012        October Part
7    3    Jan2013        January Part
8    2    Oct2012        October Part
9    1    Dec2012        December Part
So when you are using the Code you will have something in the unbound text box as..
Code:
=DCount("*", "relatedUserTbl", "uID = " & Me.ID)
I am just expanding on what you have placed before me.. I feel this is a very simple and straight forward approach..

See what others would say about this..
 
Okay I changed the structure. The "DCount: code however does not work...
 
What exactly does "does not work" mean?

Error message, wrong value????
 
It gives me a "0" value in the unbound textbox.

I have one table (SafetyEvents) where users will register the amount of safety events per day. For each event, a reportnumber must be created that looks something like XXX-XXXXX-001.
In my table SafetyIACT (a particular event), to register the reportnumbers I have the following fields:

ReportID - autonumber
DPRDate - Date/time
Projectnumber - text
reportnumber (PK) - text
description - text

I want each new reportnumber to have some info (about the project XXX-XXXXX) and an automated number in the form of 3 digits, so 001 to 999 (I will never have more than 100 reports in a certain project, but just to be sure). This number depends on the projectnumber. So each time a new projectnumber is added, it should start counting from 001 and increase by 1 for each added reportnumber in that project.
I would like to put the necessary code in the "got focus" event of the "reportnumber" field so it fills in automatically, but can be changed when needed.
I would also like to count the number of reportnumbers on a certain DPRDate and have just this number to fill in the SafetyEvents form (which tells me the number of reports per day.

Any thoughts?

Thank you very much in advance!

Mark
 
I recommend you make ReportId (autonumber) the PK. You can manipulate the ReportNumber text as you wish for your purposes
Code:
 reportnumber must be created that looks something like XXX-XXXXX-001

My first reaction is that fields should be as atomic as possible/necessary and you shouldn't concoct codes, that you could assemble and report as necessary from atomic fields.

You may wish to consider the BeforeUpdate event as a spot to do edit/validation and "prefill".

Good luck with your project.
 
Thanks.

I still don't know how to count the number of reportnumbers in the current project or the current DPRDate and how to create the appropriate string for each new reportnumber...
 
Please post a jpg of your tables and relationships.

Do you have a table for Project, and another for ProjectReports?
 
Like this?
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    26.9 KB · Views: 113
  • Capture2.JPG
    Capture2.JPG
    26.3 KB · Views: 99
Can you post a copy of your database - no confidential info?
 

Users who are viewing this thread

Back
Top Bottom