We help IT Professionals succeed at work.

Run Time Error 2499 Revisited

jofoco4
jofoco4 asked
on
846 Views
Last Modified: 2008-01-09
I have a variant question on an issue that has been asked before.

I have a main form that allows the user to locate an individual, and embedded within that main form is a subform that returns transactions associated with the selected individual. These individuals may be tagged as "inactive" for various reason by checking a checkbox entitled "Inactive" on the Main Form record. When the form loads, a filter is set to load only those indivduals whose "Inactive" field is set to False.

I'm attempting to add a command button that will toggle back and forth between the "Inactive" individuals being (A) excluded from the form and (B) included in the form. The command button is named "cmdShowInact", and there's a combo box ("cmbLkpMuse") on the main form that populates with all available Main Form records (Individuals, not transactions) for search and locate. I'm using the caption on the button when pressed to determine whether to toggle one way or the other. When the command button is pressed, the bare bone code is as follows:

     If Me.cmdShowInact.Caption = "Show Inactives" Then
             Me.FilterOn = False
             Me.Requery
             Me.cmbLkpMuse.RowSource = "SELECT [musicians].[musicianID], ....... "
             Me.cmbLkpMuse.Requery
             Me.cmdShowInact.Caption = "Show Actives Only"
             DoCmd.GoToRecord , , acFirst
             Else ......
     End If

Pressing the button results in the Fatal Failure message that Access has encountered a problem and needs to close. Upon further review through the debugger, it appears that when the processing reaches the line "DoCmd.GoToRecord , , acFirst", the Run Time Error 2499, "Can't go to first rec in Design view" is fired; I'm guessing that it is that error that is in turn firing off the Fatal Shutdown Error.

Based on the previous comments regarding Error 2499, I'm guessing that the problem must be in the attempt to marry transactions in the subform with the record selected in the Main Form, but beyond that I'm not sure how to proceed.

As always, my friends, there is great wisdom out there among you all, and I hope you have some good ideas for me to try.

Many thanks ...

... Jed
Comment
Watch Question

Author

Commented:
Hey all ...

As I'm reviewing this situation in my head, I'm noting that I don't explicitly "Requery" the subform when I flip the filter on the Main Form back and forth between "Only Active Records" and "All Records". Might that have something to do with the issue?

My thinking is that the subform detail I/O processing would be intrinsically governed by the records populating the Main Form and would not need my intervention in the code, but I confess I'm not real sure about that.

Before I screw things up further, I'll wait for you folks to provide some feedback.

Thanks again.

... J
J
Try this "toggle code for your toggle button...

Private Sub cmdShowInact_Click()
       Me.cmbLkpMuse.RowSource = "SELECT [musicians].[musicianID], ....... "
       Me.Filter = "[Inactive] = False"
       Me.FilterOn = Not Me.FilterOn    
       Not Me.cmdShowInact = Me.cmdShowInact
End Sub
Private Sub cmbLkpMuse_OnChange
        Me.Requery
End Sub
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Many thanks to you both for your responses ... I have to run right now and won't be able to test your comments until later this afternoon, but will be able to post the results of my trying your suggestions within 24 hours.

Steve, I'm interested in your answer peripherally because I've been aware of a certain amount of redundancy in my coding for some time now (requesting a certain process when It's already happening by default as the result of another command) and this will help me to pinpoint some of that weakness in my coding.

*Thanks* again to you both!

... Jed

Author

Commented:
Hey ...

Simpler is definitely the way to go; I eliminated the redundant "Requery"s, as well as the "GoTo First" line and the system seems to be fine. The application and release of the filter is happening, the Combo Box is being updated and there are no Fatal Error messages.

I have to say I'm a little confused by the simplicity of this; I've been "Requery"ing forms and combo boxes all the way through the application, and was sure that if I didn't, updates wouldn't post, but I was obviously incorrect in that belief. And the funnny thing is, all the other example of that coding practice have all worked fine; this is the only instance that triggered the error message.

Anyway, I really appreciate both of your responses, and since Steve's was the simplest and most direct, I'm going with his answer.

Again, many thanks to you both. This issue can be closed.

... Jed

Commented:
glad to hear it is working for you :-)

The only reason you would need to requery is if you are changing the data in a bound form from a seperate process and then you only need to .Requery if there have been records added or deleted, if you only chnage data them you could use .Refresh which updates records already bound to a form. FOr what you are doing the typical Access model works fine, Access is smart enough to pull new records only when it needs to ... like if you chnage the recordsource of a form or a rowsource of a combobox or listbox. Whenever a recordsource is loaded into a form the default is to display the first record.
Force a .Requery after changing the rowsource/recordsource can be quite expensive if your data is stored on the network because essentially the data gets pulled 2 times ... once by Access and a second time by you.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.