Defualf values in form

[Guest]

Hi, helpers
I have voucher no field in my form (key) which they should be entered in
order. I mean 2 after 3, 4 after 3, and so forth. I can not use auto numbers,
since by deleting a voucher ( a record) their orders become messed up.
So I created a VB function, and gave both defualt value and validation rule
to this funcion. the function counts the number of records in the form by
Recordset object's count property (Cloneset) and add one for the default and
validated value.
it works well in normal way, but when the user delet a record or goes up and
down in the form, it shows #Error Value in that feild.
I used refresh method to update the based query in the form, but it didnt
work.
what is the problem??!! and what causes this!?
thanks in advance.

 

[Nikos Yannacopoulos]

Leo,

To begin with, you didn't necessarily have to resort to a VBA function, much
less use a recordset! It would have been enough to just use a:

DMax("[VoucherNo]","tblVouchers") + 1

(with the actual table and field names) in the control's default value
property! You can also setthe voucher no control's Enabled property to No,
so the user cannot manually change it, so you no longer need validation.

The question is, would the user delete the last record only, or might they
delete, say, record 3 wehn 4 and 5 already exist? How would you handle the
latter? would you just leave the gap, or would you want to renumber all the
subsequent voucher records? The latter can be easily done by means of a
recordset operation, but you need to be more careful if the vouvher no field
is part of a relationship on another table.

HTH,
Nikos

 

[Guest]

Thank you for the answer,
but I used your aggregate function, but every time it showed #Error or #Name
error
I tried it with;
DMAX([Voucher No], [Myform])+1
DMAX([Voucher No], "Myform")+1
DMAX([Voucher No], Forms![MyForm])+1
DMAX([Voucher No], Tables![MyTable])+1
do you have any idea why it happens..!??

Leo,

To begin with, you didn't necessarily have to resort to a VBA function, much
less use a recordset! It would have been enough to just use a:

DMax("[VoucherNo]","tblVouchers") + 1

(with the actual table and field names) in the control's default value
property! You can also setthe voucher no control's Enabled property to No,
so the user cannot manually change it, so you no longer need validation.

The question is, would the user delete the last record only, or might they
delete, say, record 3 wehn 4 and 5 already exist? How would you handle the
latter? would you just leave the gap, or would you want to renumber all the
subsequent voucher records? The latter can be easily done by means of a
recordset operation, but you need to be more careful if the vouvher no field
is part of a relationship on another table.

HTH,
Nikos

Hi, helpers
I have voucher no field in my form (key) which they should be entered in
order. I mean 2 after 3, 4 after 3, and so forth. I can not use auto numbers,
since by deleting a voucher ( a record) their orders become messed up.
So I created a VB function, and gave both defualt value and validation rule
to this funcion. the function counts the number of records in the form by
Recordset object's count property (Cloneset) and add one for the default and
validated value.
it works well in normal way, but when the user delet a record or goes up and
down in the form, it shows #Error Value in that feild.
I used refresh method to update the based query in the form, but it didnt
work.
what is the problem??!! and what causes this!?
thanks in advance.

 

[Nikos Yannacopoulos]

Leo,

Wrong syntax. Try:

DMAX("[Voucher No]", "MyTable")+1

HTH,
Nikos

Thank you for the answer,
but I used your aggregate function, but every time it showed #Error or #Name
error
I tried it with;
DMAX([Voucher No], [Myform])+1
DMAX([Voucher No], "Myform")+1
DMAX([Voucher No], Forms![MyForm])+1
DMAX([Voucher No], Tables![MyTable])+1
do you have any idea why it happens..!??

Leo,

To begin with, you didn't necessarily have to resort to a VBA function, much
less use a recordset! It would have been enough to just use a:

DMax("[VoucherNo]","tblVouchers") + 1

(with the actual table and field names) in the control's default value
property! You can also setthe voucher no control's Enabled property to No,
so the user cannot manually change it, so you no longer need validation.

The question is, would the user delete the last record only, or might they
delete, say, record 3 wehn 4 and 5 already exist? How would you handle the
latter? would you just leave the gap, or would you want to renumber all the
subsequent voucher records? The latter can be easily done by means of a
recordset operation, but you need to be more careful if the vouvher no field
is part of a relationship on another table.

HTH,
Nikos

Hi, helpers
I have voucher no field in my form (key) which they should be entered in
order. I mean 2 after 3, 4 after 3, and so forth. I can not use auto numbers,
since by deleting a voucher ( a record) their orders become messed up.
So I created a VB function, and gave both defualt value and validation rule
to this funcion. the function counts the number of records in the form by
Recordset object's count property (Cloneset) and add one for the

default
and

validated value.
it works well in normal way, but when the user delet a record or goes

up
and

down in the form, it shows #Error Value in that feild.
I used refresh method to update the based query in the form, but it didnt
work.
what is the problem??!! and what causes this!?
thanks in advance.

 

[Guest]

Hi,
sorry, but I got the #Error sign in that feild...

Leo,

Wrong syntax. Try:

DMAX("[Voucher No]", "MyTable")+1

HTH,
Nikos

Thank you for the answer,
but I used your aggregate function, but every time it showed #Error or #Name
error
I tried it with;
DMAX([Voucher No], [Myform])+1
DMAX([Voucher No], "Myform")+1
DMAX([Voucher No], Forms![MyForm])+1
DMAX([Voucher No], Tables![MyTable])+1
do you have any idea why it happens..!??

Leo,

To begin with, you didn't necessarily have to resort to a VBA function, much
less use a recordset! It would have been enough to just use a:

DMax("[VoucherNo]","tblVouchers") + 1

(with the actual table and field names) in the control's default value
property! You can also setthe voucher no control's Enabled property to No,
so the user cannot manually change it, so you no longer need validation.

The question is, would the user delete the last record only, or might they
delete, say, record 3 wehn 4 and 5 already exist? How would you handle the
latter? would you just leave the gap, or would you want to renumber all the
subsequent voucher records? The latter can be easily done by means of a
recordset operation, but you need to be more careful if the vouvher no field
is part of a relationship on another table.

HTH,
Nikos


Hi, helpers
I have voucher no field in my form (key) which they should be entered in
order. I mean 2 after 3, 4 after 3, and so forth. I can not use auto
numbers,
since by deleting a voucher ( a record) their orders become messed up.
So I created a VB function, and gave both defualt value and validation
rule
to this funcion. the function counts the number of records in the form by
Recordset object's count property (Cloneset) and add one for the default
and
validated value.
it works well in normal way, but when the user delet a record or goes up
and
down in the form, it shows #Error Value in that feild.
I used refresh method to update the based query in the form, but it didnt
work.
what is the problem??!! and what causes this!?
thanks in advance.

 

[Nikos Yannacopoulos]

Leo,

If the syntax is correct, then either (a) the table or field name are not
correct, or (b) the Voucher No field is not numeric so the addition fails.

Nikos

Hi,
sorry, but I got the #Error sign in that feild...

Leo,

Wrong syntax. Try:

DMAX("[Voucher No]", "MyTable")+1

HTH,
Nikos

Thank you for the answer,
but I used your aggregate function, but every time it showed #Error or #Name
error
I tried it with;
DMAX([Voucher No], [Myform])+1
DMAX([Voucher No], "Myform")+1
DMAX([Voucher No], Forms![MyForm])+1
DMAX([Voucher No], Tables![MyTable])+1
do you have any idea why it happens..!??

:

Leo,

To begin with, you didn't necessarily have to resort to a VBA

function,
much

less use a recordset! It would have been enough to just use a:

DMax("[VoucherNo]","tblVouchers") + 1

(with the actual table and field names) in the control's default value
property! You can also setthe voucher no control's Enabled property

to
No,

so the user cannot manually change it, so you no longer need validation.

The question is, would the user delete the last record only, or

might
they

delete, say, record 3 wehn 4 and 5 already exist? How would you

handle
the

latter? would you just leave the gap, or would you want to renumber

all
the

subsequent voucher records? The latter can be easily done by means of a
recordset operation, but you need to be more careful if the vouvher

no
field

is part of a relationship on another table.

HTH,
Nikos


Hi, helpers
I have voucher no field in my form (key) which they should be

entered
in

order. I mean 2 after 3, 4 after 3, and so forth. I can not use auto
numbers,
since by deleting a voucher ( a record) their orders become messed up.
So I created a VB function, and gave both defualt value and validation
rule
to this funcion. the function counts the number of records in the

form
by

Recordset object's count property (Cloneset) and add one for the default
and
validated value.
it works well in normal way, but when the user delet a record or

goes
up

and
down in the form, it shows #Error Value in that feild.
I used refresh method to update the based query in the form, but

it
didnt

work.
what is the problem??!! and what causes this!?
thanks in advance.