JET Error Upon Exiting Form

[RH610]

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#

This error occurs in the "Before Update" section of code as the "End Sub" is executed.

I am lost as to why I get this. I look in the table "Firm" and there is an appropriate record with the right FIRM#.

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?&quot = vbYes Then
If Me.[Firm#] = 0 Then
strMsg1 = "Must select a firm"
Result = MsgBox(strMsg1, vbOKOnly, "Warning&quot
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
Me.Firm_.Requery
End Sub

 

[ChipsIT]

Hi

I don't know whether this will help, but take a copy of your database and try renaming the field so that you don't use the '#' and see if that makes a difference. Being from the UK we use 'No' to signify number rather than '#' but I know that '#' is a special character and is likely to give you problems.

In a database I once wrote for a US customer, I used 'No' in the field names etc and reserved use of the '#' to the labels on forms and reports.

Good luck
Steve

 

[danvlas]

This looks weird:
Me.Firm_.Requery

although it may be so...Do you really have a trailing space in the name of the control?

What is the line where the code breaks?

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[RH610]

Thanks for the replies.

There is no space in the field name Firm#. Firm_ came up in the dropdown list so that's what I used. The field is named Firm#. I also tried me.[Firm#] and it gave the same error. I also removed the lines that requeried Firm# (or Firm_) and got the same error.

The error occurs as the line "End Sub" executes. In other words, as the record is being updated. I should explain a bit further...I have two tables (among others); one named firm and one named advisor that are linked by the Firm# key field. The example code in my original message is from the "before update" section of a '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 which attempts to update the advisor table and that is when the error occurs.

I don't think it is a problem with the # being used in the field name Firm# because I used 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.

Thank You

 

[Bowers74]

Have you tried posting this in forum701 or forum702?

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!