Unable to add second record from forms. Get "Can't go to the specified record"

[RobFixit]

I am having very strange problem with all of the tables in my database. I have forms for each table. I can add the first record using the form, but if I try to add a second record from the form, I get the "Can't go to the specified record". The settings are set to allow the addition of records, and here is the strange part. If I manually add a second record to a table from the Table view, then I can add as many records as I want from the forms. It is only the second record that causes the error and prevents me from adding more records. Even a very simple form will not work for the second record of a table. It is not my access install, as it will fail on multiple computers, and it does not appear to be a setting since it works fine if I have 2 records or more in the database. Originally I did not have the issue, and then all of a sudden the issue showed up. And when I imported tables and forms to a new ACCESS database, it brought the issue with it. If I created a totally new database, it works fine. If I create a new table and form in the affected databases, that form will fail as well. I can use the stock Access code to go to a new record, use a DoCmd to open a new record, or use the New Record button on the bottom of the navigation screen and I get the same issue. I have searched all over for solutions and tried everything I could think of, all with no success. I have my tables on a network drive, but I imported them into a different database so that the tables are not split from the database and the database is on the local drive, and it still has the issue, so it is not related to the split database or the network drive. Does anybody have an idea what went wrong with my database to cause this really strange bug? I have a work around by adding a second record manually, but if I want to market the database at some point, I need to figure it out and solve it. I have fought some weird issues and won, but this one is beating me. Any suggestions would be greatly appreciated.

[ranman256]

Check form property CYCLE: all records.
is there a single key?
is there an autoNum?
is there a bound sub form to a sub table?

[ssanfu]

Welcome to the forum....

From your description

<snip> Originally I did not have the issue, and then all of a sudden the issue showed up. And when I imported tables and forms to a new ACCESS database, it brought the issue with it. If I created a totally new database, it works fine. If I create a new table and form in the affected databases, that form will fail as well.<snip>

I would have to say your dB is corrupted. You could try de-compiling, then re-compiling, but I would not trust the dB. I could create a new dB and continue on.

I dealt with a corrupted FE dB that happened over (at least) a 6 month period. So all of my backups were also corrupt. It turns out that the VBA project in my FE dB was slowly corrupting. It took over a week to figure out and another 3 days to fix/recover.

The BE table data is probably OK.
I would recommend you re-create the FE and not import any of the FE objects.


Good luck with your project......

[RobFixit]

Check form property CYCLE: all records.
is there a single key?
is there an autoNum?
is there a bound sub form to a sub table?

I tried changing the property CYCCLE to all forms, and no change. There is normally just one key for each table. Some have auto number, and some do not. I can create even a simple table and a form with just a couple of fields with no subforms, and it still fails, if I create the table and form in the database that is having the issue.

[Missinglinq]

I think that this is one of those instances when getting our hands on your file is really mandatory, if we're to have any chance of debugging this thing for you. If you could save the file as a v2007 format file, then ZIP it up and attach it to a post, more people could open it and have a look at it.

Having said that...given the strange, odd and curious behavior, here, I suspect, as have others, that you're dealing with corruption! And if I'm reading you right, here, and this problem involves multiple Forms and multiple Tables...my guess would be that, sadly, the entire Front End file is corrupted. This assumes that the Forms didn't have a common ancestor...i.e. you didn't take one Form, copy it, then modify it as a second Form, and so forth.

Linq ;0)>

[RobFixit]

Welcome to the forum....

From your description

I would have to say your dB is corrupted. You could try de-compiling, then re-compiling, but I would not trust the dB. I could create a new dB and continue on.

I dealt with a corrupted FE dB that happened over (at least) a 6 month period. So all of my backups were also corrupt. It turns out that the VBA project in my FE dB was slowly corrupting. It took over a week to figure out and another 3 days to fix/recover.

The BE table data is probably OK.
I would recommend you re-create the FE and not import any of the FE objects.


Good luck with your project......

Thanks I will try your decompile/recompile suggestion. I agree something is corrupted.

[RobFixit]

Thanks for all of your help! It turns out I had never compiled my database, but doing that did not help. So based on one suggestion that I post my database, I decided to copy a form an table and create a simple database with the issue to post (as my project is pretty massive with lots of complex forms with tabs, subforms, and lots of complex VBA code) so I could have a bear minimum database for people to look at. I was able to eliminate the table and VBA coded, so I knew it was something in the form. As I stripped out as much as I could to have a very simple form with minimal code. I discovered a few lines I believe I had put in because of an issue I had at one point with the record number (record 1 of 1 for example) that I had at the bottom of the form not working correctly if there was only one record. As these lines only ran if I had only one record, I commented them out and the issue went away!!! I had added that code after I had multiple records in the database, so I did not find the bug until I was creating a new database and bringing over some of the forms. The new database did not have any records, so when I added one, the bug showed up. Here are the lines that it seems was causing me all my grief. I have no idea why they would cause the bug, but they did. Now I have to play with my forms to see why I put that code in the first place! Thanks again for all of your input. I would never have figured it out if I had not tried making a subset of my database to post! I cannot tell you how happy I am that it was not a corruption issue, as I could not have recreated the front end, as complex as it is!

If Recordset.RecordCount = 1 Then
Recordset.MoveLast
Recordset.MoveFirst
End If

[Missinglinq]

That exact code really makes no sense...if you only have one Record, why would you need to navigate to the last Record, then back to the first Record, which are actually one and the same?

Moving to the last Record then back to the first Record is commonly done when you're using a custom 'Record # x of y Records' hack. It forces Access to load the entire Record Source, which is not always done when first opening a Form...especially in cases where a huge number of records are present. This causes the real/correct number of Records to be displayed.

The problem is that doing this kind maneuver causes an error to pop (the error is No Current Record, I believe), when there are no Records in the Record Source. The proper way around this would be

Private Sub Form_Load()
 If Recordset.RecordCount > 0 Then
   Recordset.MoveLast
   Recordset.MoveFirst
End If
End Sub

Notice that the If clause has changed. Only running

Recordset.MoveLast
Recordset.MoveFirst

if there was one Record, as stated above, would make no sense...you'd want it to run anytime there were Records present...hence the

If Recordset.RecordCount > 0 Then

If you don't have a custom 'Record # x of y Records' hack, I have no idea why the code would be present! Possibly it was germane in one Form that you've since copied and reused?

Linq ;0)>

[RobFixit]

That exact code really makes no sense...if you only have one Record, why would you need to navigate to the last Record, then back to the first Record, which are actually one and the same?

Moving to the last Record then back to the first Record is commonly done when you're using a custom 'Record # x of y Records' hack. It forces Access to load the entire Record Source, which is not always done when first opening a Form...especially in cases where a huge number of records are present. This causes the real/correct number of Records to be displayed.

The problem is that doing this kind maneuver causes an error to pop (the error is No Current Record, I believe), when there are no Records in the Record Source. The proper way around this would be

Private Sub Form_Load()
 If Recordset.RecordCount > 0 Then
   Recordset.MoveLast
   Recordset.MoveFirst
End If
End Sub

Notice that the If clause has changed. Only running

Recordset.MoveLast
Recordset.MoveFirst

if there was one Record, as stated above, would make no sense...you'd want it to run anytime there were Records present...hence the

If Recordset.RecordCount > 0 Then

If you don't have a custom 'Record # x of y Records' hack, I have no idea why the code would be present! Possibly it was germane in one Form that you've since copied and reused?

Linq ;0)>

I had an issue with the recordcount not working correctly in some cases, and I found that code online somewhere as a work around. The coding I am using for the record count on the form is Me.RecordNum.Caption = "Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount, where RecordNum is a textbox. Since I removed the offending code, I have not been able to duplicate the issue I was having before, so I will probably just leave it out. I am not sure what addressed it. I know one change I made recently that cleaned up a lot of my issues I was having (extremely long time to open some forms and design mode acting unbearably slow), was a tip I found online of always having one table open. I created a dummy table with no records and when the database is loaded, I open that dummy table and hide it so there is always one table open. It made everything fly (some forms opening instantly when they took 10 seconds before). Maybe that change did something with the record count issue. Who knows. I taught myself access with some online courses, a book, the help of a lot of online forums, and lots of trial and error, and ended up with a database far better than I ever thought I could do, but it was not without a lot of frustration, including this bug. Thanks for the suggestion. If my problem comes back I will try that change.