Form Will Not Move to Next Record in Table

[dinobot]

Hello everyone. I was wondering if anyone has a fix for this problem.

I have a form that an end user populates. All fields will be populated by the end user except for one, the sales ID field, which has been prepopulated all the way down the table to record 165. When the users enter there info and click save, I want the form to move to the next available row, not an entirely new record. The VBA that I use is below:

Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, "Non Revshare House Misc List", acNext


AND

Private Sub SaveRecord_Click()
DoCmd.Save acForm, "Non Revshare House Misc List"
DoCmd.GoToRecord acDataForm, "Non Revshare House Misc List", acNext
End Sub.

Everytime I load the form, it goes to the previous stored record instead of moving to the next row. Is there a fix for this?

Thanks

 

[boblarson]

I'm not understanding exactly what you have and what you are trying to do.

Do you have the form's recordsource as a QUERY and does that query have a sort defined?

If you want the form to go to the next record (as long as your form is using a query and sort order as tables don't store records in a particular order and what you see today may not be the same order as tomorrow unless you provide a sort on a field/fields), then you can simply use

DoCmd.RunCommand acCmdRecordsGoToNext

 

[dinobot]

No, it was not set as a query. Should I do that? If so, will it move to the next record?

 

[boblarson]

No, it was not set as a query. Should I do that? If so, will it move to the next record?

This will move it to the next record:

DoCmd.RunCommand acCmdRecordsGoToNext

But if you don't use a query with a sort order, there are no guarantees that the record it moves to next is the one you would be expecting.

 

[dinobot]

Got it. Thanks it worked. I had to set it as a query as you specified. I appreciate the help.