I'm just working on solving a similar problem today. I have a 1-1
relationship which worked fine when the be was Jet. The form is bound to a
query and as long as at least one field in each table is populated, Jet was
able to handle the dual insert. However, the code is now failing with a SQL
server back end. Luckily the "parent" record has only a couple of fields
that are used to identify the class of record being inserted so it can be
built with no user input. Here's what I put in the Form's BeforeUpdate
event.
Dim rsADO As ADODB.Recordset
Dim objconn As ADODB.Connection
Dim strSQL As String
Dim ConnectString As String
If Me.NewRecord Then
strSQL = "Set Nocount on "
strSQL = strSQL + " Insert Producer (ProducerType, ChangedBy,
ChangedDate)"
strSQL = strSQL + " VALUES ('Agency', " + "'" + Environ("UserName") +
"'" + ", GetDate()) "
strSQL = strSQL + " select IdentityInsert = SCOPE_IDENTITY()"
strSQL = strSQL + " set nocount off"
ConnectString = DLookup("Connect", "qGetConnectionString")
Set objconn = New ADODB.Connection
objconn.ConnectionString = ConnectString
objconn.Open
Set rsADO = objconn.Execute(strSQL)
Me.AgencyID = rsADO("IdentityInsert")
End If
That leaves the form bound only to the Agency side of the relationship and
it works fine now that I can populate the AgencyID with the value of the
autonumber from the Producer table. I have the same code, except for the
ProducerType value in the Licensee form.
The pass through query sets no count on so that the procedure doesn't see
the messages. It inserts the row in the producer table and then retrieves
the identity column using SCOPE_IDENTITY() rather than @@IDENTITY which will
return an invalid result if the table has a trigger that causes an insert in
another table. IMHO, it is better to be safe than sorry and so even though
I do not have such a trigger and do not foresee adding one in the future,
someone else may and so why take the chance?
I use a query (qGetConnectionString) to look up the current connection
string for the linked tables from the MSysObjects table. This will allow
the code to work regardless of whether the fe is linked to the test or
production back end.