Solved

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

Posted on 2016-07-18
6
39 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
ID: 41719053
You set a TempVar like this:

TempVars!NameOfTheTempVar = YourValue

Are you using that syntax to set the TempVar?
0
 

Author Comment

by:renfrey
ID: 41719358
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
ID: 41719496
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
ID: 41726427
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

739 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