Kd wrote:
Well I am gonna learn a lot of paitence working with this stuff
cleaned out table have 3 fields
PoNum - lng int
Create Date - short Date default Date()
Customer -Text
Query
PoNum
Create Date
Customer
ResponseNo: "R" & Format(CreateDa te(),"yy") & "-" &
Format([PONum],"000")
Form
PONum default value=DMax("[PONum]","[PO Table]")+1
CreateDate default value date()
Customer
ResponseNo
BeforeUpDate on form
=Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year (Date()), 1,
1)"), 0)+1
I am sure I have something in wrong place
Any help is so appreceated
Ken
Get rid of the default value on PONum. That is what the BeforeUpdate event is
for and besides the default value you are using won't start over each year the
way the BeforeUpdate code will.
The DMax expression doesn't go into the BeforeUpdate property box. What you
enter there is "[Event Procedure]" (without the quotes) and then you press the
builder button [...] to the right of the box which will take you to the VBA code
window for your form. THAT is where the DMax statement goes. You should find
your cursor pre-positioned between two lines of VBA code that marks the start
and end of the BeforeUpdate sub-routine. Your DMax() statement goes between
those two lines.
Lastly your DMax statement is incorrect because the value it retrieves needs to
be assigned to the PONum field. You should have (in the VBA code window)...
If IsNull(Me!PONum ) Then
Me!PONum = Nz(DMax("[PONum]","[PO Table]", "CreateDate >=
DateSerial(Year (Date()), 1, 1)"), 0)+1
End If
The reason for the IsNull Test is that the BeforeUpdate event can fire multiple
times for a given record. You only want to assign the the PONum value the very
first time the record is saved (at which point PONum will still be null). There
are other form events that only fire once per record, but they are either
unreliable or allow more chances for two users to grab the same value if you
ever have more than one person entering records.
So to recap...
When you start to create a new record PONum will be Null, CreateDate will
default to the current date, and ResponseNo will be Null (because PONum is still
Null). As soon as you save the record the BeforeUpdate will calculate the
proper PONum value and assign it to the PONum field just before the save occurs.
Afterwards both PONumn and ResponseNo shoud be populated with the correct value.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
