Link Two Forms for Data entry

Question

I have a main form with two subforms (i.e., main form, subform, subfom within subform).  Because of space for data entry, I'm trying to provide the user the ability to open the 2nd subform as a stand alone.  That part I've got worked out, however the problem is linking the two forms together.  I can enter the new data, however the link ID requires manual input, which I want to avoid.  Hope this is all making sense.  Here are the name of my forms:

MainForm: frmSupportArea

Subform: frmSupportDescSubform

Subform w/in subform: frmSupportsSubform

The name of the table for frmSupportsSubform is tblSupports

Fields in the frmSupportsSubform are: SupportsID - AutoNumber, SupportNeedID - Number (linking field), SupportType, Facilitator, Frequency, Duration, Documentation and Cost.

Actually with some VBA, I'm able to get the form to open and enter the SupportNeedID number, upon opening, but when I want to add additional data the SupportsID number is added, but not the SupportNeedID (link number).  Below is the vba I'm using for this process:

Private Sub Form_Open(Cancel As Integer)

Dim intSupportNeedID As Integer

intSupportNeedID = Forms!frmISPWriter!frmSupportArea.Form!frmSupportDescSubform.Form!SupportNeedID

DoCmd.RunCommand acCmdRecordsGoToNew

DoCmd.GoToControl ("SupportNeedID")

Forms!frmSupportsAppendSubform!SupportNeedID = intSupportNeedID

End Sub

Any help with this would be appreciated and even if there is a better way to accomplish what I'm doing.

Mike

Answer 1

Ooops!

I've just noticed that I forgot to change the form named in my code in one spot.

*DoCmd.OpenForm "dlgAddNewPart", , , , acAdd, acDialog,*intSupportNeedID

Change this to:

**DoCmd.OpenForm stDocName, , , , acAdd, acDialog,**intSupportNeedID

I was also thinking that as long as the command button was on the parent form of "frmSupportsSubform", you could shorten the code:intSupportNeedID = Forms!frmISPWriter!frmSupportArea.Form!frmSupportDescSubform.Form!SupportNeedID

to:

intSupportNeedID = Me!frmSupportDescSubform.Form!SupportNeedID

Answer 2

Only eight

Answer 3

The SupportNeedID could be written to a variable in the code that calls the OpenForm and passed to the intended control using OpenArgs, and the Form_load() event offrmSupportsSubform

1.) First, in whatever code you are using to open frmSupportsSubform, *(I have assumed you are using a Command Button named "*cmdOpenSupportsSubform" on your main form "frmSupportArea") you need to Dim a variable, and assign the value of SupportNeedID to it.

*** I just noticed that your code references "frmISPWriter" in the form hierarchy, so I'm not quite sure now where this command button will reside?***

=====================================

Private Sub cmdOpenSupportsSubform_Click()

'This Procedure opens frmSupportsSubform in Data Entry and Dialog mode.

'The user adds the new item, closes the form, then returns to this main form

'to continue with the editing operation...

On Error GoTo Err_cmdOpenSupportsSubform_Click

Dim stDocName As String

Dim intSupportNeedID As Integer

intSupportNeedID = Forms!frmISPWriter!frmSupportArea.Form!frmSupportDescSubform.Form!SupportNeedID

    stDocName = "frmSupportsSubform"

    DoCmd.OpenForm "dlgAddNewPart", , , , acAdd, acDialog, intSupportNeedID

Exit_cmdOpenSupportsSubform_Click:

    Exit Sub

Err_cmdOpenSupportsSubform_Click:

    MsgBox Err.Description

    Resume Exit_cmdOpenSupportsSubform_Click

End Sub

==========================================

Now that we have set a value for the "intSupportNeedID" variable, the form will open an assign that value to the intended control. 

Note that if the form is opened by any other method, the OpenArgs value will be Null, and the form will just open normally.

==========================================

Private Sub Form_Load()

      ' If form's OpenArgs property has a value, assign the contents

      ' of OpenArgs to the SupportNeedID field. OpenArgs will contain

      ' a value ONLY if this form is opened using the cmdOpenSupportsSubform command button

      ' method with an OpenArgs argument.

If Not IsNull(Me.OpenArgs) Then

Me![SupportNeedID].SetFocus

Me![SupportNeedID] = Me.OpenArgs

End If

End Sub

==========================================

Give that a try and let us know if it works for you.

Answer 4

If data already exist, it's displayed, but the primary purpose is to allow the users to add new records.  Again, I'm able to do this with one new record, but adding more, without having to manually input the link ID, is proving difficult.  Another thing I just realized is, if my user opens the form and does not enter anything a new record is created without anything in the other fields.  Don't want that to happen.

If the user can add new records during the time they have the form open, then either set the default value to reference the control on the other form or use the Before Insert event to assign the control on the current form the value from the referenced control.

To prevent blank records I would use the Close event to delete records where the link field is Null

Anyone who uses a computer in this day and age understands what a Tab control is and does.

Answer 5

Sometimes if you have too many fields in a table, it can be a bad design structure of the DB.

How many fields in your table?