Open a form to add new record while passing on the value from the previous form

Question

i have a subform, sfrmList, with a “new” button at the top, i want to click on the “New” button and it will take me a data entry form “frmAdd” but then also passed on the value “APID” from the subform to the “frmAdd”. The goal is to add new record for the same AIPID. Thank u in advance.

Answer 1

Pass the APID value to the new form as the OpenArgs argument of the OpenForm method.  Open the form in dialogue mode to pause code execution.  Requery the subform when code execution resumes to show the new record in the subform:

    DoCmd.OpenForm "frmAdd", WindowMode:=acDialog, OpenArgs:=Me.APID

    Me.Requery

In frmAdd's Open event procedure set the DefaultValue property of the APID control to the value passed to the form:

    If Not IsNull(Me.OpenArgs) Then

        Me.APID.DefaultValue = """" & Me.OpenArgs & """"

    End If

Note that the DefaultValue is always a string expression, regardless of the data type of the column, hence the delimiting literal quotes characters.

Answer 2

Thank you so much Ken! You always save my day!

Answer 3

DiscusGirl,

You should consider marking Ken's reply as the  answer for the benefit of those either looking for answers or threads that haven't been answered.

Answer 4

Hi Ken, your code works perfectly when there is already a record on the subform. I got an error message when i tried to click the button New when a subform does not has any record yet. Because there is no record on the subform yet, there is no APID to pass on to the new form. However my mainform will always has a APID that can be used to pass on. How do I go about passing the APID of the mainform frmCAPDetails to frmAdd? Thank u in advance.

Answer 5

You'll get an error in that context because the code will pass a Null.  Try changing the code as follows:

    DoCmd.OpenForm "frmAdd", _

        DataMode:=acFormAdd, _

        WindowMode:=acDialog, _

        OpenArgs:=Me.Parent.APID

    Me.Requery