copy data from one subform to another subform

[pirseinkim]

I have a main form, Customers. Within that form I have a contacts subform and and mailings suboform. On my contacts subform I have checkbox, 'Add To Mailings', once that is checked I want it to add it to the Mailings subform. However, I cannot seem to get that to work. I have to add a command button on the main form to "merge to mailings" and it does a refresh, then my subform is populated. I would like to just click on the checkbox not have to click on the command button also. Seems like I've tried several things. Any ideas would be helpful.

Thank you,
Kim

 

[pbaldy]

Try the button code in the after update event of the checkbox.

 

[pirseinkim]

I did that. The only thing the button code does is a me.refresh. I put it in my logic on the checkbox and it doesn't populate the form.....or table....

 

[pbaldy]

From the other subform you'd need the full form reference to the other subform:

http://www.mvps.org/access/forms/frm0031.htm

Edit: it may need a requery rather than refresh as well.

 

[pirseinkim]

So on subform 2 I would reference subform 1? Where do I need to do this? And once I do that then my click event on subform 1 should copy my data to suboform 2?

 

[pbaldy]

The after update event of the checkbox would reference the mailings subform. As to "copy", I assume that the record source of the mailings subform is a query that filters on the checkbox field, so requerying it would make the newly checked record show up. It's not "copied", it now meets the criteria so it shows up. Can you attach the db here?

 

[pirseinkim]

I cannot attach my whole db. But I can attach the code. Will that help?

 

[pbaldy]

One way to find out.

 

[pirseinkim]

okay so this is the logic I have on AfterUpdate of my checkbox

Private Sub AddToMailing_AfterUpdate()
Dim rst As ADODB.Recordset
Dim strSQL As String
'Do-It Added Logic KP 11/12/2015

On Error GoTo HandleErr

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
strSQL = "SELECT * FROM tblCustMailingInfo"
rst.Open strSQL, Options:=adCmdText

If Me!AddToMailing Then
'If rst.EOF Then
rst.AddNew
rst!CustID = Me!CustID
rst!Salutation = Me!Salutation
rst!FirstName = Me!FirstName
rst!LastName = Me!LastName
rst!TitleID = Me!Title
rst!EmailAddress = Me!Email
rst!Address1 = Me!Address
rst!City = Me!City
rst!State = Me!State
rst!ZipCode = Me!ZipCode
rst!Mobile = Me!Mobile
rst!Phone = Me!Phone
rst!Ext = Me!Ext
rst!Fax = Me!Fax
rst!Comments = Me!Comments
rst.Update
rst.MoveNext
' End If
Else
'next
End If

rst.Close
Set rst = Nothing

Me.Requery


ExitHere:
Exit Sub

HandleErr:
MsgBox Err.Description, , GetTitle(1)

End Sub

 

[pbaldy]

Ah, you are actually adding a record. No difference actually, as mentioned I'd expect this line:

Me.Requery

to be:

Forms!MainFormName.MailingSubformName.Requery

 

[pbaldy]

By the way, depending on how large the table may be/get, I'd do this:

strSQL = "SELECT * FROM tblCustMailingInfo WHERE 1=0"

That will prevent the entire table from being pulled over, since all you want to do is add a new record, not work with existing records. If the table is large, your method can be noticeably slower.

 

[pirseinkim]

Rock on!!! Thank you! That worked and thank you for the info adding the WHERE 1=0 I didn't know that!

Thanks again for all your time

 

[pbaldy]

Happy to help!