Updating two tables at once

[Paul H]

My form looks up a record in tblData and populated several fields on frmTransactions. I then fill in type, status, date etc. When I'm done the data is stored in tblTransacations and the status field is updated in the master record in tblData. I've been putting the update code in the Before Update event so I can capture it before it goes away. I'm using something like this.

 
If Not IsNull(Me.txtTransaction_Type) And Me.txtTransaction_Type = "2" 
 
strSQL = "Update tblData Set Status_ID = " & Me.txtSubmission_Status_ID
strSQL = strSQL & " Where Record_ID = " & Me.txtRecord_ID Then
DoCmd.RunSQL strSQL
 
End If

One thing that happens is when I switch to design mode the code wants to fire, even though all fields are empty. I get this error each time.

"The expression you entered refers to an object that is closed or doesn't exist."

Followed by:

"You cant Save this record at this time."

I can work my way through this, but something like this has happened on other occasions. I'm not satisfield I've found a fix for evertything yet. I only want this code to fire when I've entered a record, it gets stored and I'm on to a new one.

Hopefully you can help.

Paul

[orange]

I don't have Acc2010, but I think it would be helpful if you showed us all the Event code. It may also be helpful if you included a jpg of your tables and relationships.

Also, an UPDATE only updates 1 table. You could have multiple Update sql as part of a
Begin Trans...EndTrans and have both or neither table updated.

The db.Execute sqlStatement,dbFailOnError syntax may be better than DoCmd.RunSql

What exactly does this mean

I only want this code to fire when I've entered a record

You could check that the record exists with a Dcount, then issue an Update transaction
(Begin..End). I'm not sure what it is you are trying to prevent/accomplish with the above quoted statement.

[Paul H]

I only want this code to fire when I've entered a record.

Quote:

I'm not sure what it is you are trying to prevent/accomplish with the above quoted statement.

I tried not to include too much information, but it looks like I entered too little. I'll try again.

I have a data entry from. You fill in the boxes, when your done the form flips to a new records, just like normal and a new row ends up in tblTransactions . In addition to that I want to update a related record in tblData with information from the form. The easiest way I could see to do that is in the Before Update event.

Here is that code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
strSQL = "Update tblData Set Status_ID = " & Me.txtStatus_ID
strSQL = strSQL & " Where Record_ID = " & Me.txtRecord_ID
If Not IsNull(Me.txtTransaction_Type) And Me.txtTransaction_Type = "1" Then
DoCmd.RunSQL strSQL
End If
End Sub

This may seem like much ado about nothing, but when I first open the form, then switch to Design Mode, the Before Update event runs and encounters this error:

"The expression you entered refers to an object that is closed and doesn't exist."

The code is hanging up on the first line of of the sub.

strSQL = "Update tblData Set Status_ID = " & Me.txtStatus_ID

If I dismiss that error I get a 2nd message saying "Access encountered an error trying to save this record. Do you want to close object anyway?"

I don't know why the Before Update event is even "firing" at this time. I only want to run this code when I enter a new record. This started out as a bigger problem, but I'd like to understand this. I'm afraid it might occur at some other time than design time.

Should I be using a different event?