Explanation of correct arguments needed

Discussion in 'Access Macros' started by Toller, May 6, 2010.

  1. Toller

    Toller Guest

    Background: I'm using the "Real Estate" template (Access 2007) to develop a
    leasing application for my state agency. Thus far I have made very good
    progress in adapting this template to our use and we've started loading test

    Problem: There is a report that produces a list of expiring leases. On this
    report is a field that has the appearance of a hyperlink. When one clicks on
    it, an input screen opens but while it's the right screen, it's not the right
    record. Instead it's the low order record in the database...always.

    When I look at the Properties, I see that this field has an embedded macro.
    It's linked to the "On Click" event. When I look at the macro, it is supposed
    to open a form (which it does) and the arguments are.....

    Tenant Details, Form, , "TenantID=" & [Screen].[ActiveControl], , Dialog

    How does the program supply the "Screen" and "Active Control" arguments to
    make certain the correct record is shown or what arguments do I need to
    include ?

    Thanks for your assistance.

    Eric Johnson
    Lands Manager
    Toller, May 6, 2010
    1. Advertisements

  2. Eric,

    [Screen].[ActiveControl] would refer to the textbox that you clicked, i.e.
    the one with the 'hyperlink'. If the value of this control is the TenantID
    of the current record on the report, then it should work. If this control
    is something else, then please have a look at the design of the report, and
    see if there is a TenantID field in the Record Source of the report. If
    there is, try changing the macro argument to this:
    "TenantID=" & [TenantID]
    Steve Schapel, May 6, 2010
    1. Advertisements

  3. Toller

    Toller Guest


    I changed "screen" to "TenantID"...no joy.

    I changed it back and then changed "activecontrol" to "TenantID"...no joy.

    I then dug around for a bit and found that the "Data|Row Source" is a select
    statement that reads:

    SELECT [Tenant].[TenantID], Tenant.[LastName], Tenant.[FirstName] FROM
    Tenant ORDER BY [LastName], [FirstName];

    Is there anything to be found in that?
    Toller, May 6, 2010
  4. Eric,

    See responses "inline"...

    Actually, my suggestion was to change the macro argument to:
    "TenantID=" & [TenantID]

    Replacing one or the other part of [Screen].[ActiveControl] was not part of
    the deal at all.
    Row Source? Does that mean it's a Combobox? On a Report? Hmmm... that's
    What do you actually see on the report in the control with the hyperlink?
    Is it a number, or a name, or something else?

    Can you have a look at the Bound Column property of the combobox? It should
    say 1.

    Can you also look at the Name property of th combobox, and let us know what
    it is?

    Another point of clarification... just looking back to your original
    question, can you explain what you mean by "the low order record"?

    Steve Schapel, May 7, 2010
  5. Toller

    Toller Guest

    Actually, that's what I thought I did but this morning I tried again with
    the same result.
    Yes. It's a combo box on a report. The control shows in the design view but
    is not present in the actual report.
    It is a last name only.
    Yes. It is 1.
    The name is TenantID.
    The record returned is always the tenant with an ID of 2. There isn't a
    record with the TenantID of 1 as this was a record I created and then
    deleted. The result is that 2 is the lowest numbered record....right?

    I guess this raises the question of whether it is getting record number 2
    for a reason or simply because it's the first one it comes to. Is there some
    kind of setpoint monitoring that can be used with an embedded macro to see
    what the value is?

    I appreciate the time you are taking for this. I really had hoped that it
    would be a matter of setting a property to a different value and that it
    would be recognizable.

    Toller, May 7, 2010
  6. Eric,

    Yes, it looked at first like it would be a quick and easy problem. And I am
    sorry for coming up with more questions than answers at this stage. But I
    am really trying to understand what you are looking at there, and so far I
    haven't quite grasped it, I'm afraid. So now, what I am confused about is
    that I thought the combobox was the control that is formatted as a
    hyperlink, and is the one that you are clicking. But now you tell me that
    you only see it on the design view and not the report view.

    If I gave you my email address, is there any way you could zip and email it
    to me (after removing any sensitive data), so I can check it out?
    Steve Schapel, May 8, 2010
  7. Toller

    Toller Guest

    The "working" file is at the office and I can't get to it until Monday.
    However, I don't think I've changed anything in this area yet. I'll check the
    file copy on the template site to see if it exhibits the same behavior and
    then let you know.


    Toller, May 8, 2010
  8. Toller

    Toller Guest

    There are two options.

    1. I checked the "Real Estate" template that you can download (Access 2007)
    and it demonstrates the problem right out of the box. You'd have to add data.
    My suggestion is that if you do this, you add the tenant data, then the
    property data, then the lease data....couple records of each will do it as
    long as the "End Date" of the lease record is within 30 days.

    2. I can email you a copy of the database with the data filled in. It's only
    about 250kb zipped.

    Your choice.

    The test will be to "run" the "Expiring Leases" report and then click on
    either "Tenant" to see the tenant record details along with the lease.
    Whichever you click on, the same record will pop up which will be the record
    with the lowest record number. Then when back at the report, click on either
    "Lease ID" to see the lease details. Same thing will happen....the lease with
    the lowest record number will appear.

    Thanks for your help. In practice, we could use it with this flaw but it's a
    cludge in an otherwise fairly simple but elegant template.

    Toller, May 10, 2010
  9. Toller

    Toller Guest


    Please send your email address to me at:

    I'll zip the file and send it to you.

    Toller, May 11, 2010
  10. Eric,

    I have received your sample database, and see immediately the problem. You
    need a = in front of the Where Condition expression. Change to:
    ="[LeaseID]=" & [Screen].[ActiveControl]
    .... and I think it will work as intended.
    Steve Schapel, May 12, 2010
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.