Want to get a field in a form to look at the last record when entering new data

  • Thread starter Thread starter Steve S
  • Start date Start date
S

Steve S

I have a Data Entry Form that I use to enter new records to a table.
One field is CheckNo, it is a Number Field. Currently when I add a
record using this form the field starts off blank. What I would like
to have happen is for it to check the value of the last record entered
and if it is blank - leave it blank but if there is a number in the
last record entered I would like it to automatically come up with a
default value one higher.

I've been trying to do this by entering an expression in the default
value property of the text box of the form I am using to enter data.
From info I have found elsewhere I have been trying to use the
following expression:
=DLookUp("[CheckNo]","Empty","[ID]=Forms![DataEntryForm]![ID]+1")

CheckNo is the name of the Number Field in the Table
Empty is the Name of the Table
DataEntryForm is the name of the Form

I figured I would first tackle just getting it to increment the fileld
and that then I would tackle adding the logic of leaving it blank if
the record before is blank.


I keep getting Error Messages of #Error and/or #Name when I try to use
this and variations of this.

Any ideas?

Thanks
 
Steve S said:
I have a Data Entry Form that I use to enter new records to a table.
One field is CheckNo, it is a Number Field. Currently when I add a
record using this form the field starts off blank. What I would like
to have happen is for it to check the value of the last record entered
and if it is blank - leave it blank but if there is a number in the
last record entered I would like it to automatically come up with a
default value one higher.

I've been trying to do this by entering an expression in the default
value property of the text box of the form I am using to enter data.
From info I have found elsewhere I have been trying to use the
following expression:
=DLookUp("[CheckNo]","Empty","[ID]=Forms![DataEntryForm]![ID]+1")

CheckNo is the name of the Number Field in the Table
Empty is the Name of the Table
DataEntryForm is the name of the Form

I figured I would first tackle just getting it to increment the fileld
and that then I would tackle adding the logic of leaving it blank if
the record before is blank.
Click to expand...

Your expression (even if it worked) is asking for the [CheckNo] value for the
record having an ID one greater than the current record. Since the current
record doesn't have an ID value yet, then obviously it doesn't work. Even if
the current record did have an ID value the +1 is not going to give the value
from a previous record. It is going to look for a record even higher so that is
wrong too. Your criteria argument shouldn't be looking at the form at all. If
your on a new record the form is blank so there is nothing there of any use.

Does your table include a DateTime field indicating when the record was created?
You need some field or field combination that can positively identify the most
previous record. Assuming you did have a [WhenEntered] field you would first
need to retrieve the Primary Key value for the record having the highest value
in [WhenEntered]. Then you could use DLookup to retrieve the ID value for that
record, Test it for Null (blank) and add 1 to it when it is not Null.
 
Yes - I was wondering about those things and had experimented with som
variations with no luck. so if understaand you correctly I shoul
refer to the previous record, -1 and to the table not the form.
tried the following and still got an error message.

Is this better?

=DLookUp("[CheckNo]","Empty","[ID]=Tables![Empty]![ID]-1")
CheckNo is the name of the Number Field in the Table
Empty is the Name of the Table
Click to expand...

I was hoping that this would return the CheckNo from the previou
record whcih I coudl then add to or . . .

Thank
 
To finish up this thread: Just wanted to say I ended up using:
=DLast("[CheckNo]","Empty")+1
placed in the Default valu Field of the text box on the form.

It works
 
Back
Top