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