Solved Go To The Next Record

[Eljefegeneo]

If I use the standard VBA to open a specific form, that is:

DoCmd.OpenForm "frmNewNames", , , "ID = " & Forms!frmLastN.txtLastN

I open the exact record I want.

But when I get to frmNewNames and then want to go to the next record it says I cannot. I've looked up a solution but can't seem to find one that works. I was using the standard gotonext macro, then tried.

DoCmd.GoToRecord acDataForm, "frmNewNames", acGoTo, 7

And variations of the above to no avail. If I tell it to remove the filters first before going to a new record,

Me.Filter = ""
Me.FilterOn = False[\code]
It still won't go anywhere.

I then tried the following found on a forum:
[code]Filter = ""
FilterOn = False
Const NO_NEXT_RECORD = 2105
    Me.AllowAdditions = True
    On Error Resume Next
    DoCmd.GoToRecord acDataForm, "frmNewNames", acGoTo, 7
    Select Case Err.Number
        Case 0
       Case NO_NEXT_RECORD
        Case Else
        MsgBox Err.Description, vbExclamation, "Error"
    End Select

But that gets me to the next record only and no further.

I also would like the filter to be removed and have the ability to go to the previous record.

 

[theDBguy]

Hi. To remove the filter, you can execute the following code:

Me.Filter = ""
Me.FilterOn = False

Hope that helps...

 

[pbaldy]

I don't think filter code does anything when a form was opened with a wherecondition. It appears you've already tried what db guy suggested. Perhaps opening it without a filter but still to the desired record?

Open a second form to the record

 

[Eljefegeneo]

I did do that, didn't I? First two lines of the code.

 

[theDBguy]

I did do that, didn't I? First two lines of the code.

Oh, sorry, didn't notice that.

PS. I just gave it a try, and it worked for me. Curious...

 

[bastanu]

Can you try to use the suggestion in post #9 of this thread:

Recordset, bookmark and FindFirst

Hello, I am using Findfirst to move the current focus to a record identified by an ID. The ID is my primary key, type autonumber. I have my record set, I know the ID but I am confused by the syntax of Findfirst and the subsequent use of bookmarks. I am calling this from the OnActivate event...

www.access-programmers.co.uk

Cheers,
Vlad

 

[arnelgp]

after disabling the filter, do a Requery, then go to the desired record:

Me.FilterOn = False
Me.Requery
DoCmd.GoToRecord acActiveDataObject, Me.Name, acGoTo, 7

 

[strive4peace]

hi Eljefegeneo,

when you open the form, is new record available to go to manually?

If not:

• Is there more than one table in the RecordSource? If so, can you change that to just use the table you want to add a record to?
• In the form design, what is the property setting for AllowAdditions and AllowEdits? Both must be Yes

Here is another way to go to a new record that makes things more clear if the form you want to add a record to is the active object

DoCmd.RunCommand acCmdRecordsGoToNew

 

[Cronk]

The issue is highlighted in #3 by Paul. The form has been opened with a WHERE clause. The form's recordsource contains only one record if ID is the key field.

Options are
1 Open the form with a filter, not a where clause
2 Open the form with no filter/where but with openargs and then in the load event position the record to be displayed
3 Open the form with no filter/where clause, and in the procedure opening the form, position the record to be displayed
4 After displaying the single record, change the form's recordsource and then go to the desired record
5 Something else that I can't think of

 

[Gasman]

I was curious about this and found that if I used the Where clause I too got one record as I used the ID field.
Then if I just switched the filter off, I did not actually clear the filter (just being lazy) I got back all my records.?

The only drawback was the record being displayed was the first record, but that could easily be overcome.?

I myself would go with the openargs option.?

 

[theDBguy]

The issue is highlighted in #3 by Paul. The form has been opened with a WHERE clause. The form's recordsource contains only one record if ID is the key field.

Options are
1 Open the form with a filter, not a where clause
2 Open the form with no filter/where but with openargs and then in the load event position the record to be displayed
3 Open the form with no filter/where clause, and in the procedure opening the form, position the record to be displayed
4 After displaying the single record, change the form's recordsource and then go to the desired record
5 Something else that I can't think of

Okay, unless I am misreading the problem, I really don't understand it. I said it earlier, I tried clearing the filter and it worked for me. So, just to make sure I wasn't going crazy, I created a small demo. Please let me know what I am missing. Thanks!

 

[pbaldy]

I did notice that the "Filtered" icon on the bottom of a form opened with a wherecondition could be clicked on to get the full recordset displayed. Presumably a specific set of steps needs to be taken in code to recreate that, as I'm sure db guy and others are using.

 

[Eljefegeneo]

Been busy and I do appreciate all the comments and suggestions. Will be working on this tomorrow. Thanks to all.

 

[Eljefegeneo]

after disabling the filter, do a Requery, then go to the desired record:

Me.FilterOn = False
Me.Requery
DoCmd.GoToRecord acActiveDataObject, Me.Name, acGoTo, 7

This merely brings me back to the first record in the table. Going to try another suggestion. Just thought you wiould like to know my result on this.

 

[theDBguy]

Been busy and I do appreciate all the comments and suggestions. Will be working on this tomorrow. Thanks to all.

Hi. Just curious, did you try out my demo? Was it the same as what you're trying to do?

 

[zeroaccess]

The issue is highlighted in #3 by Paul. The form has been opened with a WHERE clause. The form's recordsource contains only one record if ID is the key field.

Options are
1 Open the form with a filter, not a where clause
2 Open the form with no filter/where but with openargs and then in the load event position the record to be displayed
3 Open the form with no filter/where clause, and in the procedure opening the form, position the record to be displayed
4 After displaying the single record, change the form's recordsource and then go to the desired record
5 Something else that I can't think of

6 Add a criteria to the record source query for the ID, so it gets the record # from a control or TempVar. Don't change records per instance of the form - do a form close and open again with a different record ID.

 

[Eljefegeneo]

Okay, unless I am misreading the problem, I really don't understand it. I said it earlier, I tried clearing the filter and it worked for me. So, just to make sure I wasn't going crazy, I created a small demo. Please let me know what I am missing. Thanks!

Demo works fine, except if the next record is missing. That should nto be a problem. Not trying to make this too complicated but I plan just to show if an error message occurs, that the user has to go to another method of opening a record through a report. Unless there is an easy fix to missing numbers.

 

[theDBguy]

Demo works fine, except if the next record is missing. That should nto be a problem. Not trying to make this too complicated but I plan just to show if an error message occurs, that the user has to go to another method of opening a record through a report. Unless there is an easy fix to missing numbers.

Okay, thanks for checking it out. I was just trying to demonstrate that using Me.FilterOn=False, like I said earlier, worked for me; but you said it didn't work for you, so I guess it was just a bit of miscommunication.