Access Data Macro - Looking up a record using a value in a variable

Hello experts,

I have a named data macro (in Access 2013), which when called, I want it to find a specific record (within the table for which it is attached to) based upon a record key, which is saved in a tempvar.  The tempvar is set in the macro which calls the data macro.  However, I cannot seem to figure out how to get it to use the tempvar in the where condition.

Here is a better picture:

TVarSWSerKey = 71  (this tempvar is set in the calling macro, before the datamacro is called)
[sw_serials].[ser_key] = (this is a numerical field in the sw_serials table, and is the primary key)

Datamacro: sw_serials.uninstall (datamacro "uninstall", on the "sw_serials" table)

Look Up A Record In sw_serials
     Where Condition = [sw_serials].[ser_key] = FormatNumber([TempVars]![TVarSWSerKey])

The above where condition, does not seem to grab any record.  If I replace "FormatNumber......" with the number 71, then the where condition successfully grabs the record with ser_key of 71.  I've without the FormatNumber function, and with various different quotation formats, but can't seem to get it to work.

Any help?
JP DI.T.Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JP DConnect With a Mentor I.T.Author Commented:
GOT IT!!!

I tried testing if the datamacro was actually seeing the tempvar from the calling macro, by defining a ReturnVar in the datamacro, and setting it to the tempvar I want it to use.  Then back in the calling macro, after it calls the datamacro, I had it populate another textbox with the ReturnVar from the datamacro.

It returned nada.  So I then went back to the datamacro and changed the ReturnVar value definition to a fixed value (like the number 21).  Run the whole thing again, and presto....the textbox that I created for the ReturnVar, correctly populated with the manual number that I set the ReturnVar to (21, in this example).

That led me to thinking that TempVars can't be passed (or at least not easily) from a regular macro to a named datamacro, which led me to Parameters (for named datamacros).

I should have been using parameters to begin with. By setting a parameter from within the datamacro, using that parameter in my Where clause, the datamacro finds the correct record using the value that I send as a parameter when calling the data macro.

Although I ended up answering my own question, I will definitely mark as an assisted answer, because it did make me want to test further, and the results of which led me to discovering the solution.

Thanks!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You set a TempVar like this:

TempVars!NameOfTheTempVar = YourValue

Are you using that syntax to set the TempVar?
0
 
JP DI.T.Author Commented:
Yes.

I have been able to confirm that my variable is being set with the correct data, by temporarily placing a text box, and populating it with the TempVar, from within the same macro in which the TempVar is initially defined, and the datamacro is called.

When I run that macro, the text box shows the contents of the TempVar, which is correct.

However, I have not done anything similar, to confirm that the datamacro is in fact able to use the same TempVar.

I will have to test that, and will get back with the results.
0
 
JP DI.T.Author Commented:
Please see my last posting on this question.

Discovered the solution myself, but the discovery was because of further tests which Scott McDaniel made me think of running.
0
All Courses

From novice to tech pro — start learning today.