Can't go to New Record

[Baldeagle]

A problem has arisen with our Charity's Database. And the only thing that has occurred is that the Database has been transferred between PCs through Dropbox.



We have a form that allows the inputting of new records for gifts received but when the button that calls the form is pressed we get the message 'You can't go to the specified record - you may be at the end of the dataset - (error 2105). The macro behind the button tells it to go to a new record once the form is opened.

I have checked the query that the form is linked to and it no longer has a line at the end for a new record, whereas in my previous version of the Database the query has a line with 'New' in it waiting for input. Can you throw any light on what may have gone wrong and, more importantly, what can I do to restore the Database to its former state?

[June7]

Only reason I can think of that new record row would not show is that the query is not editable.

Have you run Compact & Repair?

[Baldeagle]

Thanks, June, for your comments. Compact & Repair runs each time the Database is closed. But I have run it again now but with no change.

If the query is not editable (and it had previously been editable) can you suggest how I might make it editable? Thanks

[June7]

Would have to view db for data structure and query design.

[Baldeagle]

I have made a dummy Database from my live one with the essential elements which throws up the same problem. If you open the Income Options form and click on the button you will see the problem.

[June7]

There is no primary key defined in Donors table. Set the primary key on DonorNumber field.

The EnterNewReceipts query used as RecordSource for EnterNewReceipts form is an INNER JOIN, should be:

SELECT Ledger.*, Donors.* FROM Donors RIGHT JOIN Ledger ON Donors.[Donor Number] = Ledger.[Donor Number];

However, if you don't display any info from Donors table there is no need for the table in the form RecordSource. Set the RecordSource to Ledger table, not query.

I don't use macros, only VBA

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be IsGiftAidDeclaration. All upper case is harder to read.

Also, avoid reserved words as names - Name is a reserved word.

[Baldeagle]

Thanks once again for your guidance. I followed your instructions in my dummy Database and it worked. The only thing that I did in my live database was to restore the Primary key (which had been there originally but somehow it had got removed!) And all is now working again the way it is supposed to.

Just a couple of comments -
1. In my live Database the Enter New Receipts form has other fields (which I had excluded from the dummy one) including those from the Donors table so that was why the query had to be used.
2. I'm afraid that I am not up to using VBA - I am self-taught over 20 years ago and I relied on the Queries & Macros etc within Access to develop the Database (and I think I am too long in the tooth to change now!!).
3. My use of spaces in naming Tables, Queries etc is also a throwback to when I started. I didn't know about avoiding them then! It would be a major exercise to change them all now!

However I really do appreciate your wisdom & guidance - much appreciated. Thanks.

[June7]

I use Rick Fisher's Find and Replace for Access to do global edits like changing table and field names and all references to them and to find stuff (like the cause for a popup prompt in a query that shouldn't be there). Saved my sanity more than once. Cost about $50. There is also a freebie called VTools.

[Baldeagle]

Sorry for the delay in acknowledging your last message - night comes in Northern Ireland much sooner than where you are!

I will explore the options you have identified and see if I can make the Database conform more to the norm!

I don't know whether I need to open a new string - tell me if I do. But is there any way to identify Tables, Queries or Forms etc in a Database which are no longer called by a button on any of the live Menus? Over the years changes have been made which have resulted in orphan Queries etc and I was wondering if there was any simple way to identify these so that I could decide whether they ought to be deleted or not.

Thanks again.

[June7]

No.

Setting the navigation pane to Tables and Related Views can give some info about how objects are related.

Then there is the Database Documenter on the Database Tools tab. Beware, this utility can take a long time to run and will use a lot of paper.

But neither of those will reveal whether or not code calls any objects.

There are 3rd party add-ins that can do searches of the database. I use Rick Fisher's Find & Replace. Cost about $50. Saved my sanity more than once. There is a freebie called V-Tools.

[Baldeagle]

Thanks again. I will explore that further.