[Sam_NYC]

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

[OBP]

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.

[Sam_NYC]

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!

[OBP]

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.

[Sam_NYC]

OBP,

That worked perfectly! Thanks for getting back to me.

Sam