Add Record with Data from Another Form

[Ganymede]

I have two forms: FormA and FormB. Both forms have a field titled "AttachmentID." I would like to press a button on Form A to open Form B to a new record, and automatically populate the "AttachmentID" in the new record on FormB from using the "AttachmentID" from the current record in Form A (i.e., the record I was on when I hit the button to open Form B).



Here's the code I created. I put this on a command button on Form A. It successfully opens Form B to a new record. But it doesn't carry forward the AttachmentID from Form A to the new record in Form B.

DoCmd.RunCommand acCmdSaveRecord
        DoCmd.OpenForm "FormB", , , "AttachmentID=" & Me.AttachmentID

I would very much appreicate if someone could point me in the right direction here. Thank you!

[June7]

Why is same data in two tables?

Have to actually populate field, something like:

Me!AttachmentID = some value

Now the trick is figuring out how to get value to second form. Options:

1. First form code sets value of second form field

2. Second form sets its own field value by referencing control or field on first form

3. Pass value via OpenArgs argument of OpenForm
DoCmd.OpenForm "FormB", , , , , , Me!AttachmentID
Then code in second form:
If Me.NewRecord Then Me!AttachmentID = Me.OpenArgs

4. Should this actually be a form/subform arrangement?

[Ganymede]

Why is same data in two tables?

Have to actually populate field, something like:

Me!AttachmentID = some value

Now the trick is figuring out how to get value to second form. Options:

1. First form code sets value of second form field

2. Second form sets its own field value by referencing control or field on first form

3. Pass value via OpenArgs argument of OpenForm
DoCmd.OpenForm "FormB", , , , , , Me!AttachmentID
Then code in second form:
If Me.NewRecord Then Me!AttachmentID = Me.OpenArgs

4. Should this actually be a form/subform arrangement?

Thank you so much for your reply. To answer your question, AttachmentID is actually the unique key in the table behind Form A. It is how I am linking the data in Form A to Form B.

The problem with a subform is it gets embedded in the master form. There's no way to make a popup subform as far as I know. The reason that's a problem is I would also need to create subforms for Form C, D, E, F, G, H and J. And all in Form A. So Form A is liable to get very cluttered if I make Forms B through J into subforms. I considered using the tabs option but I don't find it asthetically pleasing.

I tried option 3 as you suggested but I can't quite get it to work.

Here's the code I put into the command button on Form A.

Private Sub Command30_Click()
Me.Requery
DoCmd.OpenForm "FormB", , , , , , Me!AttachmentID
End Sub

And here's the code I put into Form B. (I figured it should be a Form Open function.)

Private Sub Form_Open(Cancel As Integer)
If Me.NewRecord Then Me!AttachmentID = Me.OpenArgs
End Sub

Form B opens, but the AttachmentID isn't populated. Should I try a different event procedure for the code in Form B?

[Welshgasman]

I would be using Form_Load.
Also you have not created a new record, for that code to work?

[moke123]

In addition to Junes method you can also do the following provided you are opening the popup modal as opposed to dialog.

My preference is to set the default value of the control as opposed to the value, in order to avoid creating an orphan record if the form is closed and not dirtied.

Private Sub Command3_Click()

    DoCmd.OpenForm "frmB", , , , acNew

    Forms("frmB").AttachmentID.DefaultValue = Me.AttachmentID

End Sub

[June7]

Are your forms set with DataEntry property Yes? I don't do that so forms can serve to edit existing as well as add new. I use VBA to control the form mode - to open to new or existing record.

[Ganymede]

In addition to Junes method you can also do the following provided you are opening the popup modal as opposed to dialog.

My preference is to set the default value of the control as opposed to the value, in order to avoid creating an orphan record if the form is closed and not dirtied.

Private Sub Command3_Click()

    DoCmd.OpenForm "frmB", , , , acNew

    Forms("frmB").AttachmentID.DefaultValue = Me.AttachmentID

End Sub

This solution ended up working for me. Thank you to everyone who replied!

[Ganymede]

In addition to Junes method you can also do the following provided you are opening the popup modal as opposed to dialog.

My preference is to set the default value of the control as opposed to the value, in order to avoid creating an orphan record if the form is closed and not dirtied.

Private Sub Command3_Click()

    DoCmd.OpenForm "frmB", , , , acNew

    Forms("frmB").AttachmentID.DefaultValue = Me.AttachmentID

End Sub

So a follow up to this. This solution was working for me (again, thank you). But now out of the blue I am getting a "type mismatch" error when I run the code.

I am completely puzzled by this. I have not made any changes to the underlying tables for these forms.

The attachmentID is the primary key to the table behind Form A. And the AttachmentID field in the table behind Form B is linked (via relationship) to the AttachmentID filed in the table behind Form A. Both are numerical values. So I can't for the life of me figure out why I would get a type mismatch error.

To add to the mystery: I have been saving distinct copies of my database as I made changes to it. I opened the version of the database where I first implemented this code, and I am getting the error there, too. It's so strange because I was using the same code in all subsequent versions without any errors.