Access form scrolls past last record

Larry Biederman
Larry Biederman used Ask the Experts™
on
I have an Access 2007 database with a form and sub-form.
oNE to many relationship between form and sub-form.

When I scroll with the data control, it scrolls ONE beyond the last record.
So, is there are 2 sub-records, it will scroll to #3.
If I click on 'goto last' button, it goes to record 2, which is correct.
But if I scroll with 'move next' button, it will go to blank record 3.

Sub-form recordset is attached to a single table.
Is that the issue?  Do I need to use recordset with association between both tables?

The problem is that users accidently add a partial record, when scroll to last record, instead of using the 'addnew' button.

Also, how can test for # of records, move to last, and delete (if desired) in vba code?

Advice appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
set the Allow Additions property of the sub form to NO in the sub form property window

in the click event of the AddNew button, place

me.allowadditions=true
Larry BiedermanSoftware Engineer

Author

Commented:
I have a main form. within it is sub-form. then within the sub-form is second sub-form (where records scroll).  I can't find 'allow additions' property for the for or sub-forms.

Do I change your recommended settings in vba?  I'm using access 2007

Thanks
Top Expert 2016

Commented:
close the main form
open the sub form in design view, then hit F4
select Data tab from the Form Property window
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Larry BiedermanSoftware Engineer

Author

Commented:
By 'click event of addnew button' are you referring to that within the scroll data control?
If so, how do I get it to appear to change its properties in design view?

thanks
Top Expert 2016
Commented:
you set the default property in design view, save it
you alter the property using code, to allow you to add new records
Larry BiedermanSoftware Engineer

Author

Commented:
I'm a VB programmer, and in that language, the data control is visible in design view. In Access forms, the data control is NOT VISIBLE in design view, and I have no idea how to find it or its events!  

So where do I go to add the vba code you have mentioned?

Thanks
Most Valuable Expert 2012
Top Expert 2013

Commented:
Can you post a sample copy of your database, with any sensitive data maske or removed?
Larry BiedermanSoftware Engineer

Author

Commented:
Hi mbizup,

Its a big app with front and back end Access files.
I can strip it down, or

- could just email to you personally rather than posting here.
- can have you attach remotely,

is either acceptable to you?
thanks so much,
Larry
Most Valuable Expert 2012
Top Expert 2013

Commented:
Hi -

Go ahead and post a stripped down version of the database, if feasible -- ideally just enough of the database to demonstrate the issue. That is generally EE's guidelines, so that any participant can see it and pitch in.

I've got some tips for posting samples here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_9130-Getting-database-issues-resolved-while-managing-sensitive-information-sensibly.html
Larry BiedermanSoftware Engineer

Author

Commented:
Hi mbizup,
Thanks for your help.

I've striped down the program.

When it starts:
1- click WorkOrders button  (W/O 12-07880 (It should come up that way).  If not, select it in top left dropdown.

2- If you change W/O# in dropdown, click WorkOrders button again
If a query askes you for W/O #, type the same# 12-07880

3- Scroll to near botton and see a data control.
There is only one record present.  If you scroll first and last, you see only the one record present.  However, if you scroll forward one record at a time, it will scroll to record 2, which does not exist.

My client's issue is that mysterious extra records occur here (and on report - not included).
Perhaps, stopping it fro scrolling past last record will help, or maybe I need to add 'Add', 'edit', 'cancel' and 'save' buttons, as I would do in VB6.  But for now, just stop scroll past end.

iF CODE NEEDS TO BE ADDED TO DATA-CONTROL, as other expert suggested, in what event do I add it?  In design view the data-control (scroll bar) is not visible.

Thanks much, Larry
StripDownDemo.zip
Most Valuable Expert 2012
Top Expert 2013
Commented:
Okay - that is just a quirk of the default navigation buttons in Access forms.  It is nothing specific to your database.

In effect, it is creating a new, blank record and giving it a number as a place holder.  If you leave that record without making any changes, that record will disappear -- just as it would if you had used the New Record button.  

When you reach the end of the records in your subform, the next record button acts just like the New Record button.  The New Record button simply has the added benefit that it will create a new record regardless of what record you currently have selected.

So you have a few options to think about:

1 - Live with it as a 'feature' of Access (I have users who prefer it like this)
2 - In your subform's design, disable the navigation buttons and program your own.
3 - Do something in between, such as setting the Subform's "Allow Additions" property  to False (through the property sheet), and coding your own New Record button
Most Valuable Expert 2012
Top Expert 2013
Commented:
I'd personally go with the 3rd option, re-reading your original post.

1. in the subform's property sheet, set the allow additions property to false
2. Add an "Add New" button to the subform's header.

If you are unfamiliar/ uncomfortable with VBA, just use the command button wizard to do the work for you, by choosing "Record Operations" then "New Record"

It will auto generate a block of code for you, which you can edit:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

    Me.AllowAdditions = True  '<--- Add this line to the auto-generated code
    DoCmd.GoToRecord , , acNewRec


Exit_Command31_Click:
    Exit Sub

Err_Command31_Click:
    MsgBox Err.Description
    Resume Exit_Command31_Click
    
End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
Oh - you should also add a little code to disable additions again after a new record has been added.

This would go in the subform's Current Event:

Private Sub Form_Current()
    If Me.NewRecord = False Then Me.AllowAdditions = False
End Sub

Open in new window


And again, the click code for your command button is:

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

    Me.AllowAdditions = True  '<--- Add this line to the auto-generated code
    DoCmd.GoToRecord , , acNewRec


Exit_Command31_Click:
    Exit Sub

Err_Command31_Click:
    MsgBox Err.Description
    Resume Exit_Command31_Click
    
End Sub

Open in new window

Larry BiedermanSoftware Engineer

Author

Commented:
Mbizup,

Lots of great information and code!
Allow me some time to try options.

I agree 3rd option is best, as clients chief complaint is that partial new records get added which appear as incomplete records on report.

I'll report back tomorrow.
Again, thanks!!

-Lbiederman
Most Valuable Expert 2012
Top Expert 2013

Commented:
Sounds good!
Larry BiedermanSoftware Engineer

Author

Commented:
mbizup,

Got side tracked.
Will work on this today or tomorrow and award points.

May need follow up question.

Thanks,
Lbiederman
Most Valuable Expert 2012
Top Expert 2013

Commented:
OK
Larry BiedermanSoftware Engineer

Author

Commented:
The third solutio did the job, I hope.  Time will tell if some user is still adding partial records, or if they still occur at all.

Thanks much!
-Lbiederman

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial