I have an MS Access database where users add a new record to a main form. The key field on this form is PROJECT NO. Associated with this form are other forms that are opened by command buttons on the bottom of the main form. These additional forms have fields that are filled in to add additional data to the record being added from the main form. I would like to pass the value of the PROJECT NO field on the main form to any of the forms being opened so the record of the main form is linked by that key field to the opened form. I know that this could be accomplished by using tabs on the main form instead of separate forms, but that is not how the users wish to see it. Any suggestions or sample code would be appreciated.
To begin with....you will need this Function (if you don't already have it) which we will use to make sure your main form is open for retrieving data from. Place this Function into a code Module located within the Modules section of your database:
Public 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
Now in the On Open event of each Form you are going to open, enter this code (or a close facsimile ):
If IsLoaded("myMainFormName") Then
Me.myOtherFormProjNumTxtBoxName = Forms![myMainFormName] _