0

My Problem

I have a fairly straight forward autoexec macro to perform a version control check for my split database. I have a table in the frontend called LocalVersionNumber, and a table in the backend called LiveVersionNumber. Each version number may have multiple values (ex. which version of the backend is being used, which frontend, etc). Because of this, the value in these tables are parsed via the queries __LiveVersionParser_Q and __LocalVersionParser_Q. Without going into too much wasted detail, the local version number should always be equal or greater than the live version number.

I wrote up a query that compared my local & live parsed values - and via IIF expressions, determined the logic to update or keep with current version. This test query ran without issue, but now that I've pulled the logic into a autoexec macro and I'm getting the error:

Cannot find the name '__LiveVersionParser_Q' you entered in the expression.

I've double checked, and the table and fields are spelled correctly. I've even gone as far as to copy and paste the statement from the macro back into a query, and the query executes properly still.

The Code

(In Macro Builder not VBA - ugh)

"IF" Action

CDbl([__LiveVersionParser_Q].[DevelopmentVersion])
    <=CDbl([__LocalVersionParser_Q].[DevelopmentVersion])

Then... execute my update frontend logic.

NOTE: For those asking "why use CDbl?", this is used because the version number is a text string with multiple values separated by ".". After parsing the versions, these strings are then compared by converting them to a double (using CDbl) to see which is greater.

Troubleshooting

As I stated earlier, this statement DOES work, just not in this macro. I've troubleshot it by using the following query (to keep it consistent, here is the 'design' view expression I tested rather than SQL):

Expr1: IIf(CDbl([__LiveVersionParser_Q].[DevelopmentVersion])
               <=CDbl([__LocalVersionParser_Q].[DevelopmentVersion]),"OK","UPDATE")

Any suggestions? I feel like I'm missing something simple.

TMY
  • 471
  • 1
  • 7
  • 20
  • If multiple dots in string to convert exist, CDbl will fail. – Gustav Jan 23 '17 at 15:26
  • @Gustav - there are no dots in the parsed strings. Ex. "134" would be one result, while "132" would be the other. The logic always passes in the query. It is only the macro that errors due to "Cannot find the name" - which mean its not finding the container object -- so possibly a domain issue? – TMY Jan 23 '17 at 15:56
  • Well, that's not what you wrote: _the version number is a text string with multiple values separated by "."._ – Gustav Jan 23 '17 at 18:43

1 Answers1

0

I have found a workaround (not a root cause & solution). If someone can provide a proper explanation of the root cause, I will gladly switch over answer credit to them!

But in the meantime, should anyone else run into a similar problem, here is my workaround:

Step 1: As stated in my question, I have a query that runs the "IF" action perfectly fine. So I used that to output a Result field that states either "OK" or "Update".

Step 2: I changed my autoexec macro to be as follows:

IF...
    DMax("Result","__VersionControl_FinalTest")="Update"
THEN...
    Run my update code.
ELSE...
    CancelEvent
END IF

NOTE: I tried to use a simple statement of:

IF...
    [__VersionControl].[Result] = "Update"

However, when I ran that macro, I again received the error 2482 ("cannot find the name...").

Why DMax is able to 'find' the __VersionControl_FinalTest query and the IF statement cannot, I do not know - but at least I'm back up and running.

TMY
  • 471
  • 1
  • 7
  • 20