Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-07-18
6
Medium Priority
?
52 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

715 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