Loop Assistance

[MegA248]

In Access 2007, we have a form (“Run CASE IBNR”) that we manually enter in the year in a text box in descending order (2013, 2012, etc.) for fifty years. After each year is entered, a macro (“Run Macro”) is tied to a button and used to kickoff multiple queries. The queries and macro are written and working properly but obviously, manually entering in the year 50 times is a huge pain. I’ve done some research and I believe this can be solved using a loop in Visual Basic. Any ideas on how to do that?

[ssanfu]

Google "vba access for next loop syntax".

This really doesn't make sense (to me).... but you asked..

Here is the basic (no pun intended) code:

   Dim k As Integer
   Dim StartYr As Integer
   Dim sSQL As String
   Dim TheYear As Integer

   'enter the year
   StartYr = 2013

   For k = 0 To 49
      TheYear = StartYr - k
      
      sSQL = "INSERT INTO YourTableName ( YourFieldName) VALUES ( " & TheYear & ");"

      CurrentDb.Execute sSQL, dbFailOnError

   Next

   MsgBox "Done"

Change the table name and field name (in BLUE) to your table and field names.
You could have a control (textbox) on a form to enter the start year and a button to run this code.

BTW, it really doesn't matter the order in which the years are entered. Records in a table do not have an "order"; a table is just a "bit bucket".

[MegA248]

Hi Steve,

Thanks so much for your response!! I agree with you that this doesn't make a lot of sense...I didn't build it. I just have to use it, frequently

I know these are rookie questions but I am completely new to this so any help is greatly appreciated - in the code above, you reference a table and field name. I tried entering in the form for the table and the macro for the field and I get an error (run-time error '3134'). How can I incorporate the current macro in place into the code without getting an error? The macro takes the year entered and runs it through 13 queries. Also, the amount calculated for each year is not displayed in any table (and I don't want it to) and each year uses the amount from the previous year to provide the correct calculation.

Help!

[ssanfu]

I don't know/understand what your process is.....

You say

we manually enter in the year in a text box in descending order (2013, 2012, etc.) for fifty years

So the form “Run CASE IBNR” (shouldn't use spaces - I know, you didn't design this ) is a bound form? You manually enter 50 years then click a button that executes 13 queries?

Would you post your code?

See attached mdb for looping example

[June7]

Maybe try setting the DefaultValue property of the textbox. In the textbox BeforeUpdate event:

Me.textbox.DefaultValue = Me.textbox - 1

Now when you initiate the next record in the same session, the box will populate with the incremented value.

You can even have the textbox DefaultValue have a starting value of 2013 or calculation such as Year(Date()). The programmatic change will not be saved when form closes so it starts all over again in a new session.

[MegA248]

The form "RUN CASE IBNR" has a textbox to enter the carryforward year. After you enter each year starting with the current and working backwards fifty years, you click the "Run Macro" button that runs 13 queries in the background (the macro kicking off the queries and the queries themselves are working properly). Here is my tedious process - I enter the year 2013, click the run macro button, 13 queries are run. I enter 2012, click the run macro, button 13 queries are run, etc for fifty years. The amount we are calculating within the queries rely on the amount produced from the previous year, however each amount is not presented on the form. Also, we are only concerned with the total amount calculated going back fifty years, not the amount for each year specifically.

To make matters more difficult, this database is #2 of #5 of this process and all of them are linked together. Also, the only code I have is what you provided above, I told you I am a beginner Can I provide screenshots of specific areas that would help make this more clear?

[June7]

So this is an unbound form and unbound textbox, the year value is not feeding into a record? You are using macro not VBA? With VBA could easily enter just one year value and have code loop 50 times, reverse incrementing the year and running the 13 queries in each loop. What are these 13 queries doing? Building loop structure in a macro is not as easy but is doable. https://www.accessforums.net/program...ype-34102.html

[ssanfu]

Like June says, VBA could make this less mind numbing..

I don't use macros, but there is a VBA command that will execute your macro string. Fairly easy to add the code. Can you post the dB?
Make a copy, delete the data in the copy, compact and repair, then zip it and attach it. (See the bottom of June's post)