Default Value or Control Source?

[Tony Williams]

I have a form which has a calculated control. The control allocates a
reference number [txtrefnbr], which includes the use of the DMax function to
allocate the next number in a sequence from a table called tblcomplaints.
The default value of the control is
=Nz(DMax("[txtrefnbr]","tblcomplaints"),0)+1
However we need to input say 13 records, which already have a reference
number and for the next new record to be allocated the next number in the
sequence ie 14. How do I stop the first 13 numbers being allocated as we
enter the backlog of records and then start the DMax function?
Do I need to change the Default value to a control source and use an IIF
statement. If so any guidance would be grateful?
Thanks
Tony

 

[Jeff Boyce]

Tony

It sounds like if you use your form, the the function will assign the
reference number.

A couple possibilities:

1) you use your form to enter thirteen "stubs" to secure the first 13
reference numbers.
2) you enter your thirteen records directly in the table, entering your
OWN reference numbers, then resort to the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Williams]

Thanks Jeff, option2 sounds good as the form does allocate the number.
Thanks
Tony

Tony

It sounds like if you use your form, the the function will assign the
reference number.

A couple possibilities:

1) you use your form to enter thirteen "stubs" to secure the first 13
reference numbers.
2) you enter your thirteen records directly in the table, entering your
OWN reference numbers, then resort to the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form which has a calculated control. The control allocates a
reference number [txtrefnbr], which includes the use of the DMax function
to allocate the next number in a sequence from a table called
tblcomplaints. The default value of the control is
=Nz(DMax("[txtrefnbr]","tblcomplaints"),0)+1
However we need to input say 13 records, which already have a reference
number and for the next new record to be allocated the next number in the
sequence ie 14. How do I stop the first 13 numbers being allocated as we
enter the backlog of records and then start the DMax function?
Do I need to change the Default value to a control source and use an IIF
statement. If so any guidance would be grateful?
Thanks
Tony

 

[Linq Adams via AccessMonster.com]

To avoid jumping back and forth between form and table:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txtrefnbr) Then Me.txtrefnbr =Nz(DMax("[txtrefnbr]",
"tblcomplaints"),0)+1
End Sub

Allowing end users direct access to tables is considered by most to be a bad
practice.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Tony Williams]

Thanks for that worked fine.
Tony