Creating a new entry from a subform with a 1 to many relationship

[cowboy]

I am trying to create a fairly simple tracking database. A request comes in and is logged as a New Submission. If that request gets resubmitted at a later time that is a Re Submission. All Re Submissions require an original New Submission as shown in the relationship image:

On the form housing a New Submission is a subform with all the Re Submissions associated with that New Submission. As shown in the following image:



There is a column with a hyperlink to open a resubmission or add. If you open a resubmission it uses the simple VBA code:

        DoCmd.OpenForm "f_ReSub Details", , , "ReSub.ID =" & Me!ID
        DoCmd.Close acForm, Me.Parent.Name

and opens this nice Re Submission Details form:

and it works great on the resubmission test data that I entered by hand in the tables. Now my problem is when I hit the add to add a resubmission I cant seem to get it to open that same Re Submission Details form and populate the MDE_num that is required in the relationship image above. I did something similar in another project with creating the record ahead of time in VBA , but that was a long time ago and cant seem to get that working. Here is the code I was trying commented out:

Private Sub txtOpen_Click()
    If IsNull(Me.ID) Then
'        Dim rst As DAO.Recordset
'        Dim var1 As Integer
'
'        RunCommand acCmdSaveRecord
'        Set rst = CurrentDb.OpenRecordset("ReSub")
'        rst.AddNew
'            rst!MDE_num = Me.Parent.MDE_num
'            var1 = rst!ID
'        rst.Update
'       sf_BidsPerCustomer.Requery
        DoCmd.Close acForm, Me.Parent.Name
'        DoCmd.OpenForm "f_ReSub Details", , , "ReSub.ID =" & var1
        DoCmd.OpenForm "f_ReSub Details", , , , acFormAdd
    Else
        DoCmd.OpenForm "f_ReSub Details", , , "ReSub.ID =" & Me!ID
        DoCmd.Close acForm, Me.Parent.Name
    End If
End Sub

So my question after all that is, how should I be properly creating a new record that is linked to the parent record in the New Submission Details form?

I have attached the database as a zip if you want to look at the actual file, it would let me just attach due to size.

Thank you so much for any help provided.
MDE Tracking-test1.zip

[Bob Fitz]

I am trying to create a fairly simple tracking database. A request comes in and is logged as a New Submission. If that request gets resubmitted at a later time that is a Re Submission.

I can't see the need or benefit of a second table. If you have a date/time field that automatically records the date and time that a record is created you can determine which order the submissions were made. Use DCount() with criteria to find out how many submissions have been made.

BTW
What you have been doing is not using a sub form. A sub form is a control on a main form which displays the related records on the "many" side while the main form displays the record from the "one" side.

[cowboy]

Bob, thank you for the input. Here are my thoughts:

What you have been doing is not using a sub form. A sub form is a control on a main form which displays the related records on the "many" side while the main form displays the record from the "one" side.

I thought that is what was going on in the f_Details. The form shows the records from the new submissions and at the bottom of that form is a what I thought is a subform with the title ReSubmissions which displays the records that are associated with the main form. I am not an expert so I could be wrong.

If I have them enter resubmission in the same table as new submission they would need to reenter some of the data for the project and might screw it up and write something different and then searches and consistency would be a nightmare. That is why the new submissions contain overall project data (and that will expand after I get things running) and re submission are linked to a new submission with only the data that will change for that event.

[cowboy]

I was able to get it to work with sending an openArg and then in the onLoad() I set to fill in the field that is linked to the main table. The only draw back to this is if they do not fill out anything then I had to add the following code:

    If Len(Me.txtMDE_LogNum & vbNullString) = 0 And Len(Me.txtMDE_LogDate & vbNullString) = 0 Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
    End If

Any other suggestions are still welcome. Thanks everyone.