Tuesday, August 28, 2012

Access 2010 Data Macros and LocalVars

Data macros is a new and exiciting additon to the MS Access 2010 offerrings. However i had a tough time getting it to work. Belowe are some of the issues identified working with Data Macros

  1. The Lookup A Record Data Block only works if a record is found and does not work if a record is not found. So you cannot compose a contruct which triggers if the lookup fails of yields zero records.
  2. The way lookup works is that if the record is found, then code inside of the code block runs (code indented inside of the lookup). If record is not found, then all of that indented (nested) code does not run.
  3. The work around is using LocalVar. So one would have to declare a local variable above the lookup construct and then in the lookup set the local variable to a value to identify that a record was found.
  4. The [localVars]![VariableName] also have a silly problem when trying to use it. There is actuall no need to prefix any variables in code with Localvars infact if you do the code fails so one has to ensure that the prefix is not used while inside a data macro.