Inserting value into a field when form is opened

[MChancellor]

Here is a basic description of what I want to happen, but I am not completely sure how to go about getting it to do this:

I have 3 forms based on underlying tables.

Company Information (which contains company Federal ID #, name & address)
Contact Information (contact number, name & phone number - F. Key = Federal ID#)
Contract Information (F. Key = Fed. ID # & Contact #, also includes information pertaining to each individual contract)

I have tried putting all three forms on the same form (main & sub forms) but I wasn't getting that to work right, so my boss suggested creating a button on the first form (the company form) that would allow you to go to the next form. This will work fine, however, I need to know how to get the Fed ID # to populate the corresponding field in the following forms. Also, I need for the Contact # to populate the corresponding field in the Contract Information form. I know that there is a way to do this, but I can't figure out.

Any help is greatly appreciated.

Thanks,
Misti

 

[ghubbell]

Hi Misti,
The idea to have seperate forms is probably a good one as Access forms get "heavy" with lots of subs, and when analyzed, will always show up having too many controls.
I'll assume both command buttons are on your Co Info form.

You want to set the "Default Value" on each of your other forms:

To open your conTacts Form:
DoCmd.OpenForm "NameOfYourConTactsForm", , , "[NameOfTheFedIDFieldOnThatForm]= " & Me.NameOfFedIDFieldOnCoInfoForm
Forms![NameOfYourConTactsForm]![NameOfTheFedIDFieldOnThatForm].DefaultValue = Me![NameOfFedIDFieldOnCoInfoForm]

Your ContRacts form something like:
DoCmd.OpenForm "NameOfYourContRactsForm", , , "[NameOfTheFedIDFieldOnThatForm] = Forms![NameOfYourCoInfoForm]![NameOfFedIDFieldOnCoInfoForm] AND [NameOfTheContact#FieldOnThatForm] = Forms![NameOfYourConTactsForm]![NameOfTheConTact#FieldOnThatForm]"

Forms![NameOfYourContRactsForm]![NameOfTheFedIDFieldOnThatForm].DefaultValue = Me![NameOfFedIDFieldOnCoInfoForm]
Forms![NameOfYourContRactsForm]![NameOfTheContact#FieldOnThatForm].DefaultValue = Me![NameOfConTactFieldOnCoInfoForm]

There is one issue: Your 1st and 2nd forms must be open for this second operation to work correctly so you may on opening the ContRacts form, first run all the code to open the conTacts form again.

Investigate the IsOpen or IsLoaded function that ships with the Northwinds DB sample. This will check if forms are open and is a good function for things like this.

P.S. If you turn on the wizard and drop a subform on to a main form it will do most of the work for you, however you'll still have a "heavyweight" form. Good Luck,

Gord
ghubbell@total.net

 

[MChancellor]

I don't know if this will make a difference or not, but I am using MS Access ver. 7.0 (the version previous to Office 97). I have looked through the code and also in the Help file, but can't find the IsLoaded or IsOpen function. I am somewhat familiar with VB, and therefore know what you are referring to, but I'm not sure if I can use these functions in this version of MS Access. (Yes, the company I work with IS planning to upgrade to Office 2000, but nobody knows for sure when.) I also took a look at the Northwind Sample, but couldn't locate these functions there either. Is there a way that I can check to see if they are open, or should I just automatically open the other forms (Company Information & Contact Information) in the background every time I open the Contract Information form?

Any help is appreciated...

Thanks,
Misti

 

[ghubbell]

From the module called "Utility Functions" in the Northwinds db. (my version is 2000. Perhaps your's doesn't have):

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function


Open a brand new module and paste this in. As it says, it checks if a form is loaded. In your case for the third form you might write:

If Not IsLoaded(&quot;NameOfContRactsForm&quot then
'You add all that open form stuff here
End if

All Yours!

Gord
ghubbell@total.net

 

[MChancellor]

Thanks!!