Automate data entry in one field

[brodkat]

I have a continuous form with properties set for data entry mode. The user opens the form and enters 60 records from a datasheet. The pattern of the values in one field [DropPt] start with 0.5 and sequentially increase by 0.5: (0.5, 1.0, 1.5,... 30.0).

I would like any suggestions if anyone can think of an easy way to automatically fill in the values for DropPt.

Can you set the default value to (previous + 0.5) using an expression?

Thanks so much for any insight you can provide!

 

[John Big Booty]

You could use DMax() + .05

 

[brodkat]

Thanks for super fast reply!

Your suggestion seemed perfect but I can't get it to work correctly. I think it is because I'm unclear about proper syntax. I've tried with and without quotes, square brackets, adding [Forms]!... out in front (and all combinations thereof) and usually #Error is displayed in the cell but once (or twice) Names? was displayed. For example:

=DMax([Forms]![LinePoint]![DropPoint],[Forms]![LinePoint])+0.5

Where LinePoint is the name of the form
And DropPoint is the field in question

gives me the #Error....

 

[Galaxiom]

The DMax arguments are strings so need quote marks.
However it only works on tables and queries anyway, not forms.

You could query the table that is used for the form but you would also have to limit the query to the records with the current key.

=DMax("fieldname", "tablename", "keyfieldname=Forms!formname!keycontrolname")

So this would find the largest value where the value in the keyfieldname of the form's recordsource was equal to the value in the keycontrol.

I am assuming that the sixty data points are all related in some way as a group of records and would share a common key field. If so I would normally expect the datasheet form to be a subform in a main form that held information (such as that key) common to all the records. This structure will fill out the key field of the subform automatically through the Master and Child Link Fields of the subformcontrol.

 

[Galaxiom]

A better way is to place a textbox in the footer of the form with the ControlSource:
= Max([DropPoint])

Then use the BeforeInsert event to copy this value plus the increment to the required field or control in the new record. The IsError function is also necessary to deal with the error when the form has no records.

The control's Visible property can be set to No if you don't want to see it.

 

[brodkat]

thanks ... it worked! I like it. Easy.

And per your last comment/first message - the 60 records in question are from only one datasheet and share a key (foreign key) but there is a stack of datasheets. The records are related via foreign key to a parent table but the form for entry into the parent table already has multiple subforms from other child tables -- not enough room for all the children. I'll just say that I'm glad I'm not the one that has to enter all the data!

 

[Galaxiom]

A good way to hold large numbers of subforms is with a Tab control. The Tab control is just a formatting tool and has no effect on references to the subforms so is easy to add late in the design.

Also note that (if your data is suitable) you can reuse the same subform object as the SourceObject for multiple subformcontrols. Use the OnLoad Event of the main form to set the RecordSource of each subform instance to the appropriate query.

 

[brodkat]

sure, it looks like that tab controls are a good way to organize some of my data entry... always learning new tricks, some day I might feel like I know something about access.

thanks again!

 

[Galaxiom]

When using tab controls for data entry it is convenient to include a set of buttons on the main form below the tab control to move to the Next and Previous tabs. This saves the operator having to move back to the top of the page and think about select the right tab.