How do i link one box on a form to two databases?

[Guest]

I have multiple tables and I want fields on each to be filled by a single box
on the form.

This is for an animal shelter. 1 table is surrender information
1 table is mailing list.

I want 1 form that will automatically put the surrener address and name onto
the mailing list table.

 

[Ron Kunce]

Depends on if you are using bound or unbound forms. Bound forms are created
with a link to a specific table or query (for a muti-table form). Unbound
forms have no direct link to a specific table and all updates are done via
recordset or class module coding in VBA. I will assume you are using a
bound form connected to one of the two tables you want updated. In this
case all you need to do is place a little code in the for's After Update
event to set an updatable recordset to point to the second table and write
the current values from the form to the recordset. The form's After Update
event is evoked only when a successful save of the forms data is performed
to the bound table, which is what you want. Creating a recordset depends on
whether your application is configured for older DAO coding or newer ADO
coding. ADO coding would look something like this:

Private Sub Form_AfterUpdate()
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblMailingList WHERE ListIndex =" & Me.Index,
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst!SurrenderAddress = Me.SurrenderAddress
rst!SurrenderName = Me.SurrenderName
rst.Update
rst.Close
End Sub

In this case the source of the recordset is set to an SQL Query string which
finds the one record with the same index value as the index value on the
form's bound table's record, if that is how the two tables are related.
I have multiple tables and I want fields on each to be filled by a single
box
on the form.

This is for an animal shelter. 1 table is surrender information
1 table is mailing list.

I want 1 form that will automatically put the surrener address and name onto

the mailing list table.