Access Forms hiding the row in a form and showing it after the last record is entered.

Posted on 2012-09-13
Medium Priority
Last Modified: 2012-10-02
We have a form that is a series of detail records that enters in
Line Number - Product Code - Product Description - Weight - Lot Number

After the user enters the following records into the Access Form by default there is always a new empty row already waiting for the user to enter in the next line of data for the next record.

We were wondering if that blank empty set of windows for that next line can be set up to only be visible to the user say...."after the Lot Number shown above has had a value entered in and or the user has hit the Tab Key or The Enter Key" Is this possible...?
Question by:ruavol2
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 38395372
Not really - the New Row is always shown if the user can Add new records to the form's recordset.

You could perhaps toggle the AllowAdd property, but I assume you want to do this on the last filled row, and Access has no concept of that - it's not Excel, so you can't really refer to "the row above/below" like you do in Excel. If you have some sort of marker - like an ID field, or perhaps a Sequence number - then you might be able to determine the last number showing on the form, and use the AllowAdd property ... but that's tenuous, at best.

Besides, users really don't like things that appear and disappear at random.
LVL 20

Accepted Solution

GrahamMandeno earned 1000 total points
ID: 38396170
You would generally want to do this if new records are added only occasionally.  In such a case I would start the form with AllowAdditions set to False, and place an "Add New Record" button on the form footer.

Use an event procedure like this:
Private Sub cmdAddNew_Click()
    Me.AllowAdditions = True
    DoCmd.GoToRecord Record:=acNewRec
End Sub

Open in new window

You will also want to reset after the new record has been added:
Private Sub Form_Current()
    If Me.AllowAdditions Then
        If Not Me.NewRecord Then Me.AllowAdditions = False
    End If
End Sub

Open in new window

Graham Mandeno [Access MVP since 1996]

Author Closing Comment

ID: 38455322
Thank you gentlemen that helped.

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question