Repeating Code

[Randy]

Hi there...I have some code that I would like to run through all records. I
do not know how to make the code repeat for each record. It does the first
one but that's it...I have placed a counter in my form that counts all the
records, but again do not know how to get the code to repeat the number of
times stated on the counter. Here is the part of code I need repeated:

' Move to Family Size Field
DoCmd.GoToControl "Family_Size"
' Copy Family Size
DoCmd.RunCommand acCmdCopy
' Paste Family Size
DoCmd.RunCommand acCmdPaste
DoCmd.GoToRecord acForm, "Public_Income_Update_Form", acNext

Any ideas of suggestion would be greatly appreciated. Shoud there be any
questions or more info needed please let me know. Thanks in advance for your
time and assistance.

 

[Klatuu]

That is not the correct use of a form. You should be using an Update query
(I think) to accomplish you goal.

What I don't quite understand is what you are doing. It appears you are
copying the value in the control Family_Size and pasting it right back in the
same control.

Since I do not and have not ever used runcommand, I am not sure what you are
doing. If you can explain it to me, I will be happy to offer a suggestion.

 

[Randy]

Well...it's kind of complicated...there are several things tied into this but
I'll give it a shot....on the main data entry from there are 3 fields (Annual
Income, Family Size & Qualified Pct.)...when the annual income and family
size are entered it automatically calculates the qualified pct. The qual pct
is based on a separate table (income table). The income table is updated one
a year, meaning the annual incomes are raised. When the income table gets
updated, all the records need to be updated a well, which could change the
Qualfied Pct of each record.

The code I included is only part of the income update process. I do actually
have it going through a separate query but cannot get it to repeat for each
record.

Hope I did'nt confuse you more...

 

[John W. Vinson]

Hi there...I have some code that I would like to run through all records. I
do not know how to make the code repeat for each record. It does the first
one but that's it...I have placed a counter in my form that counts all the
records, but again do not know how to get the code to repeat the number of
times stated on the counter. Here is the part of code I need repeated:

' Move to Family Size Field
DoCmd.GoToControl "Family_Size"
' Copy Family Size
DoCmd.RunCommand acCmdCopy
' Paste Family Size
DoCmd.RunCommand acCmdPaste
DoCmd.GoToRecord acForm, "Public_Income_Update_Form", acNext

Any ideas of suggestion would be greatly appreciated. Shoud there be any
questions or more info needed please let me know. Thanks in advance for your
time and assistance.

If you want to update the Family_Size field in a table, just *use an update
query to do so*. If it doesn't have any parameters it will update all records
in the table.

If (as appears elsethread) you're storing calculated values, you now have a
clear object lesson in why you should NOT store calculated values! Here's my
blurb on the subject:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

 

[Randy]

Wow....well thank you all for your responses...however...an update query is
not as simple as it may seem....unfortunately in the control (family size)
there is a function that call the auto-calculation of the Qual Pct...how this
works is in the Income Table there are many rows and columns containing
Family Size, Maximum Annual Income & Qualifying Percent;

Household_Size Income Pct
1 16490 35
1 20630 45
1 26840 60
1 35150 80
2 18590 35
2 25000 45
2 30440 60
2 39900 80
3 24000 35
3 25985 45
3 33980 60
3 44600 80
4 22720 35
4 28640 45
4 37520 60
4 49350 80

Users enter the income and household size and the qualifying percent is
automatically entered based on the table above. Now, once a year the income
levels in the above chart increase. Part of the code I have will go through
each record and update the qualifying percent by basically re-populating the
family size and triggering the auto-calculate function mentioned above. This
is why I am looking for some kind of repeat command to make sure if goes
through all the records. Yes this process may take a while depending on the
number of records in the db. Either way the process will have to go to each
record and re-calculate the qualifying percentage.

 

[John W. Vinson]

Users enter the income and household size and the qualifying percent is
automatically entered based on the table above.

Then... again... the Qual Pct field *should simply not exist in your table*.

If it can be calculated on the basis of income and household size, then it
SHOULD be calculated - anew, as needed - from the income and household size.

 

[Ken Sheridan]

Randy:

You are missing the fundamental point made by John Vinson, which is that
there is not only no need to store the Qualifying Percent in a column in the
table, but it contravenes one of the basic principles of the relational
database model to do so, because it undermines the integrity of the database
by leaving the table open to inconsistent data. There is nothing to stop the
Qualifying Percent value in a row being changed so that it is inconsistent
with the values in the other columns from which it is computed, or vice
versa. No matter what you do through the interface to try and keep them
consistent, this is not an adequate safeguard against bad data. Consistent
data is achieved by the design of the underlying logical model, i.e. the
tables and the columns in them, along with the relationships between tables,
and there are formal rules governing this.

The Qualifying Percent should be returned in a computed column in a query's
result table, not in the base table. The value in the column will be
computed from the Household_Size and Income columns, along with a value from
the separate Income table, using essentially the same function which you are
using at present. The function may well differ from your current one in that
it will take the Household_Size and Income and a value from the separate
Income table as its arguments and will return a value, rather than assigning
the value to the Pct column in the base table, but the basis of the
computation in the function should be exactly the same.

By using a computed column in a query in this way the Qualifying Percent
values returned in the computed column will always correctly reflect the
values in the Household_Size and Income columns whatever changes are made to
those values.

Alternatively the Qualifying Percent could be returned in a computed control
in a form or report, using the same function to return the value.

If you are unsure how to create and use a function to return the Qualifying
Percent value, post your current function here.

Ken Sheridan
Stafford, England

 

[Randy]

Actually that is exactly what I have....the income table is a separate table.
The qualifying pct is calculated by a separate query...there are two parts in
determining the qualifying percent based on the income table provided;

Step 1 (query)
SELECT Income_Table.Household_Size, Income_Table.Pct
FROM Income_Table
WHERE
(((Income_Table.Household_Size)=[Forms]![Public_Data_Entry_Form]![Family_Size])
AND
((Income_Table.Income)>=[Forms]![Public_Data_Entry_Form]![Annual_Income]));


Step 2 (query)
SELECT Min(Public_Find_House_Size.Pct) AS MinOfPct
FROM Public_Find_House_Size;

This all happens when the family size is entered (after the annual
income)...the need to store the qualifying percent in the table is that there
are multiple locations that all have different qualifying percentages and
annual income limits.

 

[John W. Vinson]

This all happens when the family size is entered (after the annual
income)...the need to store the qualifying percent in the table is that there
are multiple locations that all have different qualifying percentages and
annual income limits.

Could you have a VBA function which pulls together all of the inputs to the
calculation? If you can do it on the form, you can do it in a function...

 

[Randy]

Already have that...but this only happens when in a particular record. What I
am having trouble with is when users go to update the annual income limits in
the income limit table (once a year) I need to be able to update all the
records. At this time the records can only be updated by going into each
record and retyping the family size in order to have the qualifying percent
re-calculate. I have created a button in the income table form to update all
records, however, I can only get it to update the first record...I need it to
repeat for all records...thus the following needs to repeat and go through
all the records...I have created a counter that counts all the records, just
can't figure out how to get the funtion to repeat the number of time
determined by the counter...

' Move to Family Size Field
DoCmd.GoToControl "Family_Size"
' Copy Family Size
DoCmd.RunCommand acCmdCopy
' Paste Family Size
DoCmd.RunCommand acCmdPaste
DoCmd.GoToRecord acForm, "Public_Income_Update_Form", acNext

This was the only way I could think of where it would go through all the
records and re-calculate the qualifying percentages...basically it goes
through each record, copy's and paste's the family size back into the family
size field thus triggering the calculation process...if anyone has a better
idea of doing this I am open for suggesstions...

Thanks again to everyone for your assistance!

 

[Ken Sheridan]

You are still missing the crucial point, which is you don't store the results
of the computation, i.e. the qualifying percentages, but the basis of the
computation. The basis of the computation will be the variables, i.e. the
family size and income limit, possibly along with any other variables you
haven't mentioned, and the operations applied to those variables. For this
you would have a function which accepts the variables as arguments, and the
operations applied to them will be in the code within the function. The
function's return value will be the qualifying percentage.

Perhaps it might help if I illustrate this with a simple scenario using a
different but analogous set of data. Lets say you have a table of employee's
annual salaries with columns EmployeeID, GrossSalary, TaxAllowance, TaxRate.
You want to know how much tax each employee has to pay, so you write a
function:

Function TaxDue(curGrossSal As Currency, curTaxAllowance as Currency,
dblTaxRate As Double) As Currency

Dim curTaxable as Currency

' subtract tax allowance from gross salary
' to get taxable amount
curTaxable = curGrossSal – curTaxAllowance

' compute tax due by multiplying taxable
' amount by tax rate (which is expressed as
' a fractional value, e.g. 0.25 for 25 per cent)
TaxDue = curTaxable * dblTaxRate

End Function

You'd then call this in a query like so:

SELECT EmployeeID, GrossSalary, TaxAllowance, TaxRate,
TaxDue(GrossSalary, TaxAllowance, TaxRate) AS TaxPayable
FROM EmployeeSalaries;

This is a highly unrealistic and oversimplified example of course as payroll
systems are vastly more complex, but it does serve to illustrate the
principle which applies in your case, that a value which can be computed from
other values should not be stored. If for example someone is paid 30,000 GBP
pa, has tax free allowance of 10,000GBP and is taxed uniformly on the balance
at 25 per cent then the result of:

TaxDue(30000,10000,0.25)

is 5,000 GBP

In the query's result set this is guaranteed to reflect the salary, tax free
allowance and tax rate whatever changes are made to any of those values,
whereas if the same function were used to assign values to a TaxPayable
column in the base table, any of the three values could be changed without
the TaxPayable amount changing, resulting in inconsistent data. Your
scenario is in principle directly analogous to this.

Ken Sheridan
Stafford, England

 

[Randy]

ok...first let me apologize for all the confusion I may have caused....in
order to try to keep things somewhat simplified maybe someone can help with
this...I found a way to get what I am looking for by creating a loop (see
below)...however now I just need it to be able to stop/end on the last
record....is there a way I can have it loop the number of times indicated by
my counter?

Do
' Move to Family Size Field
DoCmd.GoToControl "Family_Size"
' Copy Family Size
DoCmd.RunCommand acCmdCopy
' Paste Family Size
DoCmd.RunCommand acCmdPaste
DoCmd.GoToRecord acForm, "Public_Income_Update_Form", acNext
Loop

 

[Ken Sheridan]

I can see you are determined to go down this road, but take my word for it
Randy, its not a good way of doing things and I'm not doing you any favours
by suggesting the following. Anyway:

1 You can loop n times:

Dim n as Long
Dim lngEnd as Long

lngEnd = <get last number from somewhere>

For n= 1 to lngEnd
<your code>
Next n

2. Or you can trap the error when you try to move beyond the form's final
record:

Const NOMORERECORDS = 2105

Do
<your code>
On Error Resume Next
DoCmd.GoToRecord acForm, "Public_Income_Update_Form", acNext
Select Case Err.Number
Case 0
' no error
Case NOMORERECORDS
' exit loop
Exit Do
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select
' resume default error handling
On Error GoTo 0
Loop

Ken Sheridan
Stafford, England

 

[Randy]

Thanks Ken....that is exactly what I needed...You rock man!

 

[Ken Sheridan]

Well, maybe, but I can't help thinking I'm just handing you the gun to shoot
yourself in the foot. Anyway good luck.

Ken Sheridan
Stafford, England

 

[Larry Linson]

Why do you feel _compelled_ to do your updates via a form? And, even after
others have explained to you why you should not, do you feel _compelled_ to
maintain redundant, duplicated information? People here are not just
encouraging you to do things "their way" but to do them "the right way, the
logical way, and the efficient way".

Not long ago, I was asked to assist in debugging errors in an application
where the original implementer had not realized how incredibly inefficient
and slow that approach was... the fellow who asked me to help had been
cancelling because "it was in a never-ending loop", but we got into a
conversation and, lo, it ran long enough to pop up an error message. Yuck.
We were able to find and fix the immediate problem, caused by changes to the
input data from another system. But, I am now assisting the application
owners with a pro-bono project to document their needs / requirements as a
first step in their replacing that application with one that is more
efficient and maintainable.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

Dim Rst As Dao.Recordset
Set Rst = Me.RecordsetClone
Do Until Rst.EOF
Rst!NameOfFieldToCopyFamilySizeTo = Rst!Family_Size
Rst.MoveNext
Next

Don't write code like this when using the RecordsetClone. Instead
use this:

With Me.RecordsetClone
Do Until .EOF
!NameOfFieldToCopyFamilySizeTo = !Family_Size
.MoveNext
Loop
End With

Of course, why one would do this insead of running a SQL UPDATE, I
haven't a clue.