Start at beginning, change Rec, Stop at End

[forman]

Hi

I'm trying to run a bit of code changing each record, starting at the begining of my database and stopping at the end.

I've looked at a For...Next, but then it doesn't start at the begining, and will only step to the TO number.

Any ideas what I need please.

Thanks

Alan

 

[stopher]

Take a look at FOR EACH....

If you are wanting to update records are you not able to just run an update query?

Chris

 

[forman]

Loop, If..Then, or ?????

Hi

Barclays give me the feature of downloading my statements in csv format.

There are several fields, but the important being 'Amount'. I've imported into a table and added a 'debit' and 'Credit' field.

I've created a form, and using a button at the end of every row, with the code in the 'On Click' have achived what I need.

If [Forms]![frm_Bank_Account]![Amount] > 0 Then

Forms![frm_Bank_Account]![Credit] = [Forms]![frm_Bank_Account]![Amount]

Else: Forms![frm_Bank_Account]![Debit] = Abs([Forms]![frm_Bank_Account]![Amount])

End If

But I would like it to start at the begining of the table and finish at the last record, changing all the records with one button.

I think I need a loop, like an IF...Then.

Thanks for your help

 

[stopher]

There's really no point in storing the credit/debit values in your table. The reason is you can always calculate them. Use a query to create calculated columns for credit and debit. Are you familiar with queries because queries are really the heart of managing data in relational databases such as Access.

I've attached an example database to show you a query (qryCreditDebit). Just run the query to see the effect and also take a look at it in design view to see how it was created. Also take a look at the form frmCreditsDebits. Based on the query rather than the table so includes the calculated fields.

There's nothing to stop you doing what you were planning to dd (store calculated data) but this is considered bad practice in databases for many reasons. If you want to continue though then just change the query I created to an update query using similar formula.

Chris

 

[boblarson]

Chris is correct that you shouldn't normally store calculated data, but if you are going to do that you can simply run an update query which is faster and more efficient than iterating through each record.

 

[forman]

Thanks Chris

Wow, who would have thought it would be so simple, I've seen the light.

I worked for ages to get that code correct, and didn't need it anyway.

Thanks for your time & help

Alan

 

[boblarson]

Glad we could all help