Cannot add record

[Knowlton]

I have a form for entering tire change records that has been working fine for several years.

There are two records created for each tire change, one for the tire that was removed and another for the tire that was installed.

There is also SQL to update the tires table with the current status of the tire(Discarded, In Service, etc.).

Now when I try to enter tire change records I get an error message "Update Tires 2015 You can't go to the specified record". This occurs after the "removal" record is created and the code calls for a new record for the "install".

Upon trying to exit the error message appears "You cannot add or change a record because a related record is required in "tblTires".

The tires are in the table since they are selected from a series of combo boxes based on the tires table. These combos are on a dialog form which when completed is hidden and the variables are defined from this form.

Here is the code I have:

Case 1  'tire removed and discarded
            If Not Me.NewRecord Then    'be sure you're at a new record
                DoCmd.GoToRecord , , acNewRec
            End If
 
            If intOldTire <> 1 Then    'if there is a defined old tire
                If dtOldTire < Forms!frmServices!ServiceDate Then
                    Dim strSQL As String    'set discard to true and InService to False for OldTire in the tires table
                        strSQL = _
                            "UPDATE tblTires " & _
                            "SET tblTires.Discarded = True, tblTires.InService = False " & _
                            "WHERE (((tblTires.TireID) =" & intOldTire & "));"
 
                    CurrentDb.Execute strSQL, dbFailOnError
                End If
 
                With Me
                    .txtUpdatedTire = intOldTire                'set tire ID for this record
                    .txtUpdatedTirePosition = intOldPosition    'set position for this record
                    .txtAction = "Removed"                      'set status as removed
                    .txtTreadDepth = intDiscardDepth            'set tread depth
                    .Notes = Forms!frmUpdateTireLocation!txtNotesOldTire
                End With
 
                DoCmd.GoToRecord , , acNewRec                       'move to new record
            End If

This is where the code fails.
Below is the code for the "new tire install" record.

 With Me
                .txtUpdatedTire = intNewTire                    'set tire ID for this record
                .txtUpdatedTirePosition = intOldPosition        'set position for this record
                .txtAction = "Installed"                        'set status as installed
                .Notes = Forms!frmUpdateTireLocation!txtNotesNewTire
            End With
 
            If dtNewTire < Forms!frmServices!ServiceDate _
                Or dtNewTire = 0 Then               'there are no service records at a later date
                    Dim strInService As String      'set New Tire InService to True
                        strInService = _
                            "UPDATE tblTires " & _
                            "SET tblTires.InService = True, tblTires.Discarded = False " & _
                            "WHERE (((tblTires.TireID) =" & intNewTire & "));"
                CurrentDb.Execute strInService, dbFailOnError
                DoCmd.GoToRecord , , acNewRec                       'move to new record
            End If

Thanks for your assistance!
Knowlton

[zmbd]

Ok,
1) Are you still using the same version of Access that you created the form with?

1a) Which version are you using?
1b)If you've "upgraded" which version have you upgraded to?
1c)If you've "upgraded" is the file still "mdb" or have you upgraded to V2007/V2010/V2013.

2) Is the code failing in the first block of code or the second block of code?

2a) Which line? (insert a STOP in your code at some point prior to where you think the error is occuring, I prefer at the start if the error point is not known. The code will enter DEBUG mode at that point. You can then [F8] to step thru the code until you reach the error line... if it doesn't error, then it may be a timeing issue in the record write)

[Knowlton]

@zmbd
The version I am using is 2003(Access 2000 file format).

It was created with access 2000. The file is a mdb file. I have inserted the code break and stepped through and the code fails at the "DoCmd.GoToRecord , , acNewRec" line at the end of the first block of code. When stepping through the code and I get to the "DoCmd.GoToRecord , , acNewRec" line, I get the error message "Update Tires(the name of the sub)2499 You can't use the GoToRecord Action or Method on an object in design view"

The first block of code works and the "remove" record is created or at least appears so on the form. However, if I open the table, the record is not there. It fails when trying to go to a new record for the second block(install record).

[zmbd]

After line 23 insert me.requery, let's make sure that your form has the latest recordset.

As for why you would receive a design view error, that usually only happens when the form is in, well, design view. If you were in that state, please try it again with the form in normal view.

[Knowlton]

I've figured it out. Somehow the tires table was linked to the wrong file. I don't have a clue how the one table's link got changed but I corrected it and everything is working fine again.
Thanks so much for your help!