Solved

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

Posted on 2016-07-18
6
25 Views
Last Modified: 2016-08-05
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?
0
Comment
Question by:renfrey
  • 3
6 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
You set a TempVar like this:

TempVars!NameOfTheTempVar = YourValue

Are you using that syntax to set the TempVar?
0
 

Author Comment

by:renfrey
Comment Utility
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
 

Accepted Solution

by:
renfrey earned 0 total points
Comment Utility
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
 

Author Comment

by:renfrey
Comment Utility
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now