Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access: Open new form with control button?


(!)

Sam_NYC's Avatar
Sam_NYC Sam_NYC is offline
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Beginner
09-Jul-2009, 01:48 PM #1
Access: Open new form with control button?
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
09-Jul-2009, 02:05 PM #2
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.
__________________
OBP
I do not give up easily
Sam_NYC's Avatar
Sam_NYC Sam_NYC is offline
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Beginner
09-Jul-2009, 02:59 PM #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!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
10-Jul-2009, 06:24 AM #4
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's Avatar
Sam_NYC Sam_NYC is offline
Junior Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2009
Experience: Beginner
10-Jul-2009, 11:38 AM #5
OBP,

That worked perfectly! Thanks for getting back to me.

Sam
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access, command button, openform

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑