Solved

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

Posted on 2016-07-18
6
30 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
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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.
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…

867 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

21 Experts available now in Live!

Get 1:1 Help Now