[OBP]

I didn't try to fix the problem that you have, I just don't like entering things in established data, in case it gets overwritten.

I am not sure why you are using the old records for the test as you can use it just for the new record each time that row is used.
Would that suit your purposes?

[friendlyfriend]

Originally Posted by OBP

I am not sure why you are using the old records for the test as you can use it just for the new record each time that row is used.

I'm not sure I understand what you mean here. Could you put it a different way?

And I'm curious if you tried the form I made and if it did the same thing for you. Thanks.

[OBP]

I have similar problems to yours.
I am not quite sure how you can ever enter a real new value as the 2 Undo statements remove any data that could have been entered.
I think it would be much better using a RecordsetClone to find if the entered value exists and if not allow the entry to continue.

[friendlyfriend]

For sure. The file I sent was only an example. In the real program, it only issues the Undo statement under certain conditions. If it doesn't find anything in the database, it just goes on to the next field and the user finishes entering the new record. I use a Dlookup rather than a recordsetclone because I want to search all records not just those meeting the current filter conditions.

[OBP]

The version that you supplied always clears the new record so it cannot be entered.
With a recordsetclone of the complete recordset you can actually go to the found record if required.

[friendlyfriend]

Yes. The file I provided is not complete. It is just the simplest possible example for demonstrating the problem that I am having of another NewRow not appearing after the first character is typed into the existing NewRow. That was my only purpose of posting it. The real program contains logic for locating the existing record and for adding what was entered as a new record. Those two things are not problems.

[friendlyfriend]

I just created an access 97 db like the example one I posted here and it has the same problem. So this bug has been around for at least 15 years. I'd say it's a safe bet they haven't fixed it in Access 2010 either.

[OBP]

The reason it has probably never been fixed is that in 15 years I have never heard of anyone trying to do it the way that you have chosen.
I could do it using the new record only to search for a duplicate and if present go to it or continue with the new record.
But not using any existing record, especially in a filtered form.

[friendlyfriend]

Exactly. In the real program, this behavior only happens in the NewRow. If you are editing, then it won't do the search.

I had another thought. I bet this would work if I were using SQL server because in that case, the database is not changed until the record is saved. With Access, storage and an ID number are allocated as soon as the record is dirtied and that may play a role in the strangeness. This would also make it unnecessary to compact as much since no space is consumed unnecessarily with each search.

I thought there was a free "light" version of SQL server that came around since Access 97. Maybe I could work with that.

[OBP]

I am not sure why you need to compact so much due to searching.
However as you say once the record is "dirty" the ID is created which is one of the reasons using an unbound field for the search entry.
You could of course just set the field to Indexed with no duplicates which prevents users entering duplicates by error.

[friendlyfriend]

Access consumes space for the record when it is first dirtied. Whether you finish the record or abort and undo it, the space is still taken. It is recovered when you compact though. I've had to use a nightly automatic maintenance script for programs anyway and that includes compacting. So this isn't really an issue. Actually, I wonder if having some empty space between records would be a good thing to help prevent concurrency problems of people trying to update the same records.

I don't need my ID numbers to be sequentially contiguous either. So it's fine to have gaps.

I don't want my users to see the standard cryptic error messages that Access produces. So I don't use indexes as my only safeguards. I want a more elaborate handling of duplication too with various options, so relying on this isn't enough for me.

[OBP]

You do not have to allow Access to display the message, it can be intercepted and worded or controlled any way you like.
It just ensures duplicates are not possible.

[friendlyfriend]

Sure. I still use indexes to mark things as strictly unique that need to be strictly unique. I just have code to take care of the situation before Jet ever needs to get involved. Often uniqueness is not so simple. Something has to be unique in certain cases and not others.

[OBP]

I usually combine fields in to one field to create some uniqueness, like last name, first name & DOB or National Health No, etc
So where do you intend to go from here?

[friendlyfriend]

Honestly, JQuery.

For a long time, I have been wanting to get out of Access and into something where I can see and fix whatever is misbehaving. I'll use this as a prototype to give to friends for testing while I get a better grip on Javascript, Ajax and whatever else I need to learn.