Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access 2007 randomly new Newrow does not appear upon editing Newrow


(!)

OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
11-Jun-2012, 09:45 AM #16
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?
__________________
OBP
I do not give up easily
friendlyfriend's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
11-Jun-2012, 12:10 PM #17
Quote:
Originally Posted by OBP View Post
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.

Last edited by friendlyfriend; 11-Jun-2012 at 12:18 PM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
11-Jun-2012, 12:28 PM #18
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's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
11-Jun-2012, 01:05 PM #19
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
11-Jun-2012, 01:26 PM #20
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's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
11-Jun-2012, 03:06 PM #21
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's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
12-Jun-2012, 10:29 AM #22
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
12-Jun-2012, 12:15 PM #23
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's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
12-Jun-2012, 12:25 PM #24
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
12-Jun-2012, 12:37 PM #25
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's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
13-Jun-2012, 11:32 AM #26
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
13-Jun-2012, 11:41 AM #27
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's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
13-Jun-2012, 11:54 AM #28
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,545 posts.
 
Join Date: Mar 2005
Location: UK
13-Jun-2012, 12:10 PM #29
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's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
13-Jun-2012, 12:16 PM #30
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
access 07, access 2007

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑