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


(!)

friendlyfriend's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
07-Jun-2012, 10:44 AM #1
Access 2007 randomly new Newrow does not appear upon editing Newrow
This is a new one for me and I've been developing with Access for about 15 years. This problem is with Access 2007.

I have a subform in datasheet view. After entering something into a particular field of the Newrow row, the program undoes the changed row (Me.Undo), filters the records and puts the user back on the Newrow row.

As soon as the user starts typing anything into any field, what should happen is that another Newrow should appear, so that he can enter another record after this one. However, this only happens some of the time. Other times, when I type something, the pencil appears as it should for this first Newrow that I am editing, but another Newrow does not appear. If I tab or click away from the row to a previous row, the pencil goes away as it should, but a new Newrow never appears.

I have debug code that tells me that while I'm on this Newrow row, MyForm.AllowAdditions is still True, and MyForm.NewRecord is still True. If I move off to a previous row and back, MyForm.NewRecord changes to False as it should and MyForm.AllowAdditions stays True.

This problem seems to be totally unpredictable and random. I can enter the same inputs and sometimes the new Newrow appears and sometimes it doesn't.

So what to do?

Thanks
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
07-Jun-2012, 11:01 AM #2
friendlyfriend, welcome to the Forum.
Another quirk of wonderful Access 2007.
Have you Compacted & Repaired the database?
Have you created a new blank Database & imported everything from the problem database in to the new one?
If it is just the one Subform that has this problem can't you re-create it?
__________________
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
07-Jun-2012, 11:44 AM #3
Thanks, OBP

compact and repair didn't work
creating a blank database and importing didn't work
creating a new subform in the new database and copy/pasting controls and code didn't work.

One other thing...

If I start typing into the Newrow, then press Escape twice to undo what I typed, the cursor remains blinking as normal in the active field, the pencil disappears, and the Record Selector turns from gray to white. I confirm that MyForm.NewRecord = -1 and MyForm.AllowAdditions = True.

Then, here's the kicker... If I then click the mouse to a previous row, the Newrow I was just on completely disappears and I am unable to go back to it.


Is there no longer a /decompile option with Access? That cured a lot of problems for Access 97.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
07-Jun-2012, 11:50 AM #4
I don't know what Access 2007 has, as I am still converting over from Access 2003, but I don't think it decompiles.
Have you compiled the VBA code?
Do all your forms/subforms in Continuous forms mode exhibit this fault?
Can you post a copy of the database, less it's data?
friendlyfriend's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
07-Jun-2012, 12:31 PM #5
everything compiles fine.
this is the only form that does this.
i am not able to share the app here.

thanks in advance
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
07-Jun-2012, 12:34 PM #6
I notice you say the form is in datasheet view, if you convert it to Continuous Forms mode does it still do the same?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
07-Jun-2012, 12:39 PM #7
Does the form work correctly on it's own as a stand alone form?
friendlyfriend's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
07-Jun-2012, 03:55 PM #8
Unfortunately, single view of the form is critical to the application. It toggles back and forth between datasheet view and single form. So continuous forms would not be an option for this.

The random misbehavior occurs with the subform used in a standalone manner too.

Hey, while I'm enjoying your attention, here's another good one... In one place, I have a simple statement to set a form filter... Me.Filter = something. But the assignment just doesn't take with one statement. I need to add another identical copy of this statement in order for the Me.Filter to change! If I duplicate the statement, the filter property changes. This is really (euphemismed) up!
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
08-Jun-2012, 02:45 PM #9
I assume that you are using a "Split" form then.
What Event Procedure do you use to set the Filter & Filter On functions.
friendlyfriend's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
08-Jun-2012, 07:18 PM #10
I'm not using a split form. I just use DoCmd.RunCommand acCmdSubformDatasheet to switch between Single Form view and Datasheet view. I started writing this with Access 97 which doesn't have split form.

The process leading to the problem goes something like
Code:
textbox.AfterUpdate
     DLookup
     If it finds something, then 
          Me.Undo
          Me.Filter = blah
          Me.FilterOn = True
          Me.OrderBy = blah
          Me.OrderByOn = True
          Me.FindFirst

    then if it finds it,
          Me!txtSomeField = somevalue
          DoCmd.RunCommand acCmdSaveRecord

    otherwise, 
          Me.Recordset.AddNew     'just moves focus to the NewRow
I almost thought I could predictably reproduce the problem with a couple different input strings typed into the text box. The problem would happen the first time I entered the string after having entered something different the prior time. But sometimes it works okay on the first time too.
friendlyfriend's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
09-Jun-2012, 04:10 AM #11
Argh, I feel like I'm using beta software.

New problem... I am trying to change the DefaultValue for a couple of textboxes. I have to manually click the button twice to get the change to take hold. It doesn't even work if I do it programmatically.

Do you know of any general issues with Access 2007 related to multithreading?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
09-Jun-2012, 05:18 AM #12
No I haven't heard of any, but it sounds like your version may be a bit screwed. Have you had any automatic security downloads lately?
Have you downloaded MS Office updates?
Have you tried Re-installing Access or Office.
Have you tried the database on someone else's computer?

I notice that you are using
Me.Recordset.AddNew
to move to a new record, have you tried the conventional
DoCmd.GoToRecord , , acNewrec
friendlyfriend's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
10-Jun-2012, 03:21 PM #13
I re-ran the Access 2007 installer clicking Repair. Nothing changed. I think all of my updates are up to date except one related to Visual Studio and an XML editor that just would never install. I get the same problem with DoCmd.GoToRecord , , acNewrec that I get with Me.Recordset.AddNew.

I haven't tried it on another computer, but I created a really simple form and had the same problem. I am attaching the zipped database. It has a table called tblFruit with a Fruit field and a Color field. The form just has txtFruit and txtColor and the only code is

Code:
Private Sub txtFruit_BeforeUpdate(Cancel As Integer)
Dim strLikeSearch As String
strLikeSearch = "Fruit Like ""*" & txtFruit & "*"""
Me!txtFruit.Undo
Me.Undo
Me.Filter = strLikeSearch
Me.FilterOn = True
End Sub
the data looks like

Code:
ID	Fruit	        Color
1	apple	        green
2	orange	        orange
3	pear	        yellow
4	lemon	        yellow
After I enter "pear" into the txtFruit box, and press Enter, it filters the records to just the pear one (and a popup shows up for some reason). Then I start entering something into the newline row but another newline doesn't appear.

If, into the txtFruit box of this newline, I type "pear" again, it repeats the filter. Now when I start typing into the newline, another newline does appear as desired.
Attached Files
File Type: zip Fruit.zip (14.3 KB, 17 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
11-Jun-2012, 06:02 AM #14
I can't understand why you are using the table controlled field as a Filter, it is just asking for problems.
You should have an unbound filter/search field.
The other thing with the code is not happening is the Filter is not being switched off.
I would have the form as a subform on a blank mainform that just has the search field and reset button on it.
I have created one in your database and it works with the records filtered & Unfiltered.

I see with Access 2007 you don't actually need the Filter reset as it has it as part of the form controls.
Attached Files
File Type: zip Fruit.zip (57.4 KB, 17 views)
friendlyfriend's Avatar
friendlyfriend friendlyfriend is offline
Member with 21 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Advanced
11-Jun-2012, 09:15 AM #15
Many thanks for putting that together for me.

So, first off, did you have the same problem with my form that I did?

Now a long story about why I designed it this way...
I chose to do it this way for speed and simplicity of interface. I don't want users to have to use the mouse at all. It's something I haven't tried before because of my natural fear of Access quirkiness about anything unorthodox. But now that I have done it, I like it a lot better. My users will need to add records somehow so keeping AllowAdditions True and having the NewRow available is the most natural thing to do. It's just like adding to a list in Excel or a simple Access table. Since they can add here, I want to be able to show them potential duplicates as soon as they start adding. As a simple grid, they can move around with the Arrow keys or Tab keys predictably. Otherwise, they would need to hop back and forth between unbound controls and the grid, most likely by reaching for the mouse. In that case, I would need to have unbound controls for adding/finding and get into a potential mess of toggling the AllowAdditions property or adding the record with DAO and having record locking problems. If the NewRow is just always there, then it can serve double duty for both finding and adding, relieving the user from the need to know whether what he wants is or isn't already in the database. If it is there, the program takes him to it. If not, he goes merrily along adding it as the next record. And searches can be done on more than one field. With the unbound control design, I would need to have one for each. It would be klunky to align them vertically above their respective data columns which can be resized by the user, and it would be visually suboptimal to position them above each other in the traditional fashion. I also toggle the whole subform between datasheet and single-form. In single-form, I don't want unbound seach/add fields to be visible, and don't want empty space where they were. So I would need to toggle the subform dimensions too. Not a big deal, but a little one.

It's just an elegant style that I would love if only I could solve this strange problem. I understand that the database will need frequent compacting too but I still like the design better than with unbound controls.

If I can't get it working, I'll resort to your way, but I would prefer not to.

I'm not sure why you recommend resetting the filter. My users normally won't need to see the whole list but they can do so with the controls at the bottom as you indicated.
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 ↑