I am looking for a non-programming solution to a common problem. When importing data files into access, I sometimes receive data where certain values have been suppressed if they are identical to the record above. Example: SSN and Name fields are suppressed.
SSN NAME AMOUNT
111111111 Smith, Joe 10.00
20.00
15.00
222222222 Doe, Jane 11.00
333333333 Jones, Bob 12.00
Excel allows users to "Copy Down" the data to fill in the missing values. I wanted to recreate the "copy down" function in Access. There are several threads that tell you how to write a program to programatically copy down the data (thread 181-1102367 for one). Additionally, there was Thread 700-551171 which included an excellent tip from GingerR that identified to "Copy Down" in Access, press {control}+'(apostrophe). This is a wonderful tip and GingerR gets a star!
The programming solutions are beyond my capability at this time (sadly) so I used a macro solution with GingerR's tip. The macro solution uses OpenTable, GoToControl, FindRecord and SendKeys actions to perform the copy down. It works great - but only ONE time!!! Therefore, I need the ability to repeat the macro until all the SSN's and NAMES in the table have been copied down. It appears as if this repeat requirement is solved by using the RUNMACRO action which allows me to specify how often I want to repeat the macro I am calling in on the RunMacro action.
The RunMacro Action provides choices on how to repeat the called macro "copy down". My choices to repeat the macro are to specify an exact number of times to repeat(which I don't want to use because the number of times I need to repeat will vary with each file I import; also I tried setting this to a very large number and it was too slow and eventually, I ran out of memory). The other option to repeat is to enter a "repeat expression" which Microsoft defines as "An expression that evaluates to True (–1) or False (0). The macro stops running if the expression evaluates to False. The expression is evaluated each time the macro runs."
My question is this. How do you write the repeat expression? I want this macro to repeat until all the Names and SSN's in the table have valid values (and no nulls). I've tried to write the expression several ways using the builder while in the macro but I haven't gotten the syntax right yet(I am so close!)
Any help is greatly appreciated.
SSN NAME AMOUNT
111111111 Smith, Joe 10.00
20.00
15.00
222222222 Doe, Jane 11.00
333333333 Jones, Bob 12.00
Excel allows users to "Copy Down" the data to fill in the missing values. I wanted to recreate the "copy down" function in Access. There are several threads that tell you how to write a program to programatically copy down the data (thread 181-1102367 for one). Additionally, there was Thread 700-551171 which included an excellent tip from GingerR that identified to "Copy Down" in Access, press {control}+'(apostrophe). This is a wonderful tip and GingerR gets a star!
The programming solutions are beyond my capability at this time (sadly) so I used a macro solution with GingerR's tip. The macro solution uses OpenTable, GoToControl, FindRecord and SendKeys actions to perform the copy down. It works great - but only ONE time!!! Therefore, I need the ability to repeat the macro until all the SSN's and NAMES in the table have been copied down. It appears as if this repeat requirement is solved by using the RUNMACRO action which allows me to specify how often I want to repeat the macro I am calling in on the RunMacro action.
The RunMacro Action provides choices on how to repeat the called macro "copy down". My choices to repeat the macro are to specify an exact number of times to repeat(which I don't want to use because the number of times I need to repeat will vary with each file I import; also I tried setting this to a very large number and it was too slow and eventually, I ran out of memory). The other option to repeat is to enter a "repeat expression" which Microsoft defines as "An expression that evaluates to True (–1) or False (0). The macro stops running if the expression evaluates to False. The expression is evaluated each time the macro runs."
My question is this. How do you write the repeat expression? I want this macro to repeat until all the Names and SSN's in the table have valid values (and no nulls). I've tried to write the expression several ways using the builder while in the macro but I haven't gotten the syntax right yet(I am so close!)
Any help is greatly appreciated.
