I have two tables (among others); one named 'firm' and one named 'advisor' that are linked by the [Firm#] key field. The example code (see bottom of this message) is from the "before update" section of the 'new advisor' form. The form is for entering a new advisor and adds a new blank record upon opening. The user fills it in and selects a firm (on a different form when he clicks a 'select firm' button). Upon exiting the 'select firm' form (which updates the newly added firm record in the "firm" table) the user then exits the 'new advisor' form (by clicking the X) which attempts to update the advisor table and that is when the error occurs.
When the X is clicked to close the form, I get the following error:
The Jet database engine cannot find a record in the table "Firm" with the key matching field FIRM#
The error occurs as the line "End Sub" executes. In other words, as the record in the 'advisor' table is being updated.
I don't think it is a problem with the # being used in the field name Firm# because I use that field all over the place without problem in other forms and procedures.
In fact, the error does not occur if I add a new advisor and select an existing firm. It only occurs if I add a new advisor and a new firm. It is as if the error is saying that it does not see the newly added firm (or at least a link to the newly added Firm#). I checked the 'firm' table and the newly added firm is in there.
Thank You (code below)
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strmsg As String
Dim strMsg1 As String
Dim Result As Integer
Me.[Firm#].Requery
strmsg = "Do you wish to save your changes?"
If MsgBox(strmsg, vbQuestion + vbYesNo, "Save Record?"
= vbYes Then
If Me.[Firm#] = 0 Then
strMsg1 = "Must select a firm"
Result = MsgBox(strMsg1, vbOKOnly, "Warning"
DoCmd.RunCommand acCmdUndo
DoCmd.CancelEvent
Else
Me.Text56 = Me.ID
[Date Updated] = Date
[Time Updated] = Time()
[Updator] = GetTheCurrentUser()
End If
Else
DoCmd.RunCommand acCmdUndo
DoCmd.CancelEvent
End If
End Sub
When the X is clicked to close the form, I get the following error:
The Jet database engine cannot find a record in the table "Firm" with the key matching field FIRM#
The error occurs as the line "End Sub" executes. In other words, as the record in the 'advisor' table is being updated.
I don't think it is a problem with the # being used in the field name Firm# because I use that field all over the place without problem in other forms and procedures.
In fact, the error does not occur if I add a new advisor and select an existing firm. It only occurs if I add a new advisor and a new firm. It is as if the error is saying that it does not see the newly added firm (or at least a link to the newly added Firm#). I checked the 'firm' table and the newly added firm is in there.
Thank You (code below)
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strmsg As String
Dim strMsg1 As String
Dim Result As Integer
Me.[Firm#].Requery
strmsg = "Do you wish to save your changes?"
If MsgBox(strmsg, vbQuestion + vbYesNo, "Save Record?"
= vbYes ThenIf Me.[Firm#] = 0 Then
strMsg1 = "Must select a firm"
Result = MsgBox(strMsg1, vbOKOnly, "Warning"
DoCmd.RunCommand acCmdUndo
DoCmd.CancelEvent
Else
Me.Text56 = Me.ID
[Date Updated] = Date
[Time Updated] = Time()
[Updator] = GetTheCurrentUser()
End If
Else
DoCmd.RunCommand acCmdUndo
DoCmd.CancelEvent
End If
End Sub
