1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access: Open new form with control button?

Discussion in 'Business Applications' started by Sam_NYC, Jul 9, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Sam_NYC

    Sam_NYC Thread Starter

    Joined:
    Jul 9, 2009
    Messages:
    3
    Hello,

    This is my first post here ever, so apologies if I do something wrong...

    In Access '03, I have two forms ("Funder" and "Grants"). The forms are based on tables by the same names, with a Primary Key from the Funder table linked to a Foreign key in Grants table (one-to-many relationship).

    From the Funder form, I would like to make a control button that does the following:

    1) Opens the Grants form
    2) Goes straight to a new record
    3) Passes the Primary Key value from the Funder form to the Grants form.

    I'm not so great with VBA, but I've been trying... this is what I have:

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Grants"

    stLinkCriteria = "[FdnID]=" & Me![FdnID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec

    "FdnID" is the Primary Key on the Funder table. This was all generated by the Command Button wizard, except for the GoToRecord line, which I found on another forum.

    When I set this as the event procedure for onClick on the button, it opens the form, takes me to a new entry, but the FdnID is "0", rather than the field from the opening form.

    Any ideas. I would be most grateful.

    Sam
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Sam, welcome to the Forum.
    You have set up the Tables well and your VBA is good, but it's logic is wrong.
    You are opening the Grant Form with records for the Current Funder and then moving to a New Record, thus losing the Funder's ID. There are ways around this, one of which is to pass the FdnID using something called OpenArgs, then after you move to the New Record set the FdnID to the value in OpenArgs with some simple VBA.
    But I do question your overall form concept, the reason being is that if you make the Grants form a subform on your Funders Form you do not need to set or pass anything.
    That is taken care of by the Master/Child Links between the 2 forms, so any record generated automatically gets the Current Funder's FdnID.
    You can set the Grant Subform Property "Data Entry" to Yes and it will always open on a new record for data entry, if you really want to.
     
  3. Sam_NYC

    Sam_NYC Thread Starter

    Joined:
    Jul 9, 2009
    Messages:
    3
    Thanks OBP,

    I thought it might have something to do with openArgs property, but I'm having trouble figuring out how to use it.

    I specify openargs in the DoCmd.openform line, right? What is the syntax for this? Again, I'm very new to VBA, but have a good grasp of DOMs from web design work. (that's only gotten me so far, though).

    And once openargs is set to the FdnID from the opening form (Funder), how do I set the value in the new form. It this an onLoad event set to the form's property, or can this be performed as part of button's code?

    You're right to question the point in opening a new form, rather than the subform. In fact, I originally had the Grants for as a subform. Mostly I want to change it for layout reasons (Grants has too many fields--including yet another subform within the Grants form--to fit comfortably).

    Thanks again!
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Sam, I gave it some thought last night (I am in the UK) and there are 3 ways to fo what you want.
    One is OpenArgs, another is to set a Public Variable (Global) and the last is to just refer to the Form and Field
    So in the Grant Form's On Current event you can just put
    If Me.Newrecord then me.FdnID = forms![Funder]![FdnID]

    The Public Variable is useful when you want to refer to the value a few times in the Database, even after the Funder form has been closed.
     
  5. Sam_NYC

    Sam_NYC Thread Starter

    Joined:
    Jul 9, 2009
    Messages:
    3
    OBP,

    That worked perfectly! Thanks for getting back to me.

    Sam
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/841803