On Open Event Error

[data808]

I am trying to open a form that goes to new record. It works most of the time but sometimes it runs into an error and points to this VBA line:

Docmd.GoToRecord, , acNewRec

The part that stumps me is that is works a lot of the time but then once in a while it will give an error and if I put the "On Error Resume Next" it won't give the usual error but it sometimes doesn't load the form on the new record. When this happens it also says that the recordset won't allow updating or something. So then I go out of the form then back in and its fine again. Does anyone know why this is happening? I even tried transferring the On Open code to the On Load event and still does pretty much the same thing. Intermittent errors.

[JeffChr]

try invoking your docmd in the OnCurrent event of the form That way you know the recordset is loaded before you start manipulating the bookmarks.

[data808]

I tried the On Current event and it does the same thing. I noticed the error seems to happen on an initial opening of these split forms which are all snapshot record types. Its like I can also tell when its going to happen. When the split form loads slower than usual and then I click on the button to take me to my record entry form which is dynaset, it gives the run time error of 2105. Saying that it can't go to the specified record. Right after this happens, if I try again, it works fine. It seems like it has to establish an initial connection first before it will work.

Right now I figured a way to make it work but its not how I would want it to be. I made it so that it will open the form then close the form and then open it again. This seems to work for now but the opening and closing makes the screen flash a bit and it just doesn't look good.

Any other suggestions on how to fix this?

[JeffChr]

I tried the On Current event and it does the same thing. I noticed the error seems to happen on an initial opening of these split forms which are all snapshot record types. Its like I can also tell when its going to happen. When the split form loads slower than usual and then I click on the button to take me to my record entry form which is dynaset, it gives the run time error of 2105. Saying that it can't go to the specified record. Right after this happens, if I try again, it works fine. It seems like it has to establish an initial connection first before it will work.

Right now I figured a way to make it work but its not how I would want it to be. I made it so that it will open the form then close the form and then open it again. This seems to work for now but the opening and closing makes the screen flash a bit and it just doesn't look good.

Any other suggestions on how to fix this?

I found these suggestions on the web searching with the error code 2105. Just a thought.

2105 means you can't go to that record.


So, if you have a macro or VBA which is attempting to move to a new record and it is already on a new record, then that error can occur. You can put in error handling to stop that or you can fix the code that is doing it.

Private Sub Form_Load()


If Not Me.NewRecord Then
DoCmd.GoToRecord , , acNewRec
End If


End Sub

[ssanfu]

I don't use split forms, so I 'm not sure how you are opening "The Form". I use quotes because you didn't provide the form name(s) or the code you are using to open that form.

So let's name the split form "frmSplit" and the form you are trying to open to a new record "frmAddData".

I would try deleting or commenting out any code in the "frmAddData" form for the events "Form_Load" and "Form_Open".
In form "frmSplit", you somehow open "frmAddData". Comment out the code you have to open "frmAddData" and replace it with the following code

    DoCmd.OpenForm "frmAddData", , , , acFormAdd

Obviously, replace "frmAddData" with your form name.

That's it. the form will always open in Add mode... ie a new record.

[data808]

Thanks for all the help guys. I will try out what you have suggested. The split form is called "Counter Citation Number" and the form that I open from there is called "Counter Log". I usually go to a menu form called "Query Menu" and from there I open the split form "Counter Citation Number" first. Once that's open, I go and open the next form which is "Counter Log". This is where the error occurs because it is trying to open "Counter Log" in a new record. The debug points to the On Open event to open the "Counter Log" in a new record. How do you create an error handle? Also, what is a quick way to find out if it is in fact another line of code that is creating the error because maybe I have two lines of code that try to make it open in a new record and that is what may be causing the error, like Jeffchr said.

[ssanfu]

How do you create an error handle?

http://www.cpearson.com/excel/errorhandling.htm
http://www.databasedev.co.uk/error-handling.html
http://www.fmsinc.com/tpapers/vbacode/Debug.asp
http://www.techrepublic.com/blog/fiv...errors-in-vba/

Also, what is a quick way to find out if it is in fact another line of code that is creating the error

Set a breakpoint and single step through the code.
http://www.cpearson.com/excel/debug.htm


Using "DoCmd.OpenForm "Counter Log", , , , acFormAdd" doesn't require any additional code in the form.

Note: shouldn't use spaces, special characters or punctuation in object names (exception is the underscore).
"CounterLog" or "Counter_Log" would be better names.

[data808]

I tried the Docmd.OpenForm "Counter Log", , , acFormAdd and it does work but I have found out that the recordset does not load. I have some navigation buttons that I created for the user to be able to look at previous records in case they need to go back and edit them. However, using the acFormAdd opens up the form in a mode that does not allow this. It will only allow me to see the records that I create within that session. Once I leave the form and go back in, the records will be gone. Is there any other way to make it so it still loads the entire recordset?

[data808]

If Not Me.NewRecord Then
DoCmd.GoToRecord , , acNewRec
End If

This did not work. I still get the same error intermittently.

[ssanfu]

My apologies. I understood that you wanted the form "Counter Log" to open to a new record.
Using "Docmd.OpenForm "Counter Log", , , acFormAdd" opens the form in add mode that only allows records to be added.

You can't use the current event because it would try and go to a new record every time you tried to change records.

I would try the form load event or the form activate event. The form open event might be too soon to try and go to a new record.
http://office.microsoft.com/en-us/ac...005186761.aspx
Click on "Order of events for forms and subforms"


Would you care to post a copy of the dB for analysis?

[data808]

Thanks for that article. That was pretty interesting. I will also try that activate event soon. I think you may be right that the on open event is happening too soon for the recordset to load. I must have too many things running that is making it conflict. I never had problems before. So far the work around is working where I have it open the split form, close the split form, then open it again. Then I click the button to take me to the Counter Log and it loads ok. I think its the split form that gets screwed up. Split forms contain more data since you can see a lot of records from that view in the datasheet which probably makes it harder to load it. This probably causes it to time out from the way my database is set up and causing the split form window to become invalid which makes everything in the form inactive, including the buttons. So when I click the button that takes me to the counter log, it runs into an error because the button is broken. That's the best way I can see what is probably happening. So I do have a feeling that the activate may not work either since I believe the problem to lie with the split form and not the Counter Log itself. Another reason why I see this to be the problem is because when I open the Counter Log from a regular form, like say a Menu that doesn't show any records or text boxes but just buttons, it loads fine. These menus are probably a lot quicker to load since they do not show any records at all.

I would like to hear what you think if you have any input to add to this. Thanks for the help. I will report back when I try that activate event.

[data808]

Well its exactly as I suspected. So check out the code that I created for the user to save face on a glitch in the database. I lied but its a white lie:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler:

DoCmd.GoToRecord , , acNewRec
Me.txtCustNum.SetFocus
Exit Sub

'This ErrHandler was created to take care of an error that would occur when opening
'the counter log from one of the split forms in the query menu. The debug would always
'point to the Docmd.GoToRecord , , acNewRec line. To give the user an explanation of why
'they are being redirected to the Query Menu, I have created a message box stating
'that this is due to high traffic. This of course is not the case but makes the interface
'look less glitchy and also prevents a run-time error.

ErrHandler:
MsgBox "Recordset Could Not Load Due To High Traffic." _
& vbCrLf & vbCrLf & "Redirecting To Query Menu.", vbInformation, "Notification"
DoCmd.Close acForm, "Counter Log"
DoCmd.OpenForm "Query Menu"
Exit Sub

End Sub

This is the best I can do unless anyone else has a better suggestion. Also I noticed that the problem only happens if I save the VBA. When I save the VBA, the code is broken until the initial execution. Upon initial execution, the code will present an error. After this happens, the code is established and will work till the next time I edit the code and save it. It's probably a compile sort of thing. If anyone knows anything about this, please give your feedback. This error is killing me. Also the reason why I felt this code was ok for now is that it should only happen once. After the user executes it for the first time, it should work from then on never giving the error message again. I think I can live with that.

[RonL]

Thanks for that article. That was pretty interesting. I will also try that activate event soon. I think you may be right that the on open event is happening too soon for the recordset to load. I must have too many things running that is making it conflict. I never had problems before. .....

Yes, I believe that article is misleading, because one could infer from it that event VBA executes strictly sequentially. The following thread may be of interest, not because it's directly relevant to your problem, but because it suggests an alternative - via DoEvents - to address event code asynchrony.

https://www.accessforums.net/forms/o...vba-47586.html

[ssanfu]

As long as it works for you.

There is a colon at the end of the "On error...." statement that shouldn't be there

On Error GoTo ErrHandler:

The statement should be

On Error GoTo ErrHandler

And, not that it hurts, but the last "Exit Sub" (in the error handler) is not needed because the following line is "End Sub" (which exits the sub)

[data808]

Thanks guys for the input. Yes I do have little unnecessary bits of code. Usually when I'm in a rush this happens. Thanks for the heads up. I guess I'll close this thread.