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

Take a look at FormsDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates how to insert/edit data in referencing tables, either by means of subforms on a tab control, or by means of linked forms, the latter in either dialogue or synchronized mode.

Answer 2

I do have some tab controls and even tried that approach and it worked, but I just don't like the layout.  I'm afraid it will confuse my users.  None of them have experience with Access.  If all else fails, I may consider going trying again.

Answer 3

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.

Answer 4

Hi,

instead of sub-sub-form, I think is better a sub-form with a tab control.

Ciao Mimmo

Answer 5

What it sounds like you are talking about is a correlated form. This is where you open a form filtered for an existing record or with default data.

There are a number of ways to accomplish this. If you are only filling in one piece of data is very easy. Multiples make it more difficult. 

What is the exact workflow here? Are the users filling in some data to create the record or are you opening the form blank to fill in the data?