sequence numbering

J

JohnE

I have users that are asking that the form mimics the look of a bank check.
But, in a drop list the selections would be Bank, Direct Deposit, AutoPay,
and BankCheck. What they would like to have done is when "BankCheck" is
selected, in the textbox next to the drop list the next sequential check
number will appear. The textbox will also hold information should any other
of the selections be made. On the table, the field has the data type as text
since the check number will not be used for any calculations.

Does anyone know how the check number can be done automatically? Is there
an example that someone can direct me to?

Thanks in advance to anyone who responds.
 
B

Bob Hairgrove

I have users that are asking that the form mimics the look of a bank check.
But, in a drop list the selections would be Bank, Direct Deposit, AutoPay,
and BankCheck. What they would like to have done is when "BankCheck" is
selected, in the textbox next to the drop list the next sequential check
number will appear. The textbox will also hold information should any other
of the selections be made. On the table, the field has the data type astext
since the check number will not be used for any calculations.

Does anyone know how the check number can be done automatically? Is there
an example that someone can direct me to?

Thanks in advance to anyone who responds.
Click to expand...

With 1 single user adding data, you can use
=CLng(Nz(DMax(<table name>,<check number>),0))+1
as a default value to generate the next sequential number.

However, this won't be adequate when multiple users are entering data
concurrently. The best solution would be to keep a separate sequence
table in a separate MDB file which can be locked for single user
access. This has been done before, and it is not trivial to do it
correctly, so maybe one of the Access MVP's around here can point you
to a link for an example.
 
J

JohnE

Thanks for the reply. This will work if only the field had the seq number in
it. But, the field is text data associated with an afterupdate event of a
drop list. The field could be populated with a word phrase if other items
are selected from the drop list other then BankCheck. The line you provided
will not work and an error will occur if other text appears in the field.

Any further suggestions?

*** john
 
B

Bob Hairgrove

Thanks for the reply. This will work if only the field had the seq number in
it. But, the field is text data associated with an afterupdate event ofa
drop list. The field could be populated with a word phrase if other items
are selected from the drop list other then BankCheck. The line you provided
will not work and an error will occur if other text appears in the field.

Any further suggestions?

*** john
Click to expand...

It sounds like you might need to normalize your design further such
that each kind of check or payment is stored in its own table.
Otherwise, you could try putting an SQL select statement instead of
the table name inside the DMax function, e.g.: "SELECT * FROM
the_table WHERE payment_type='BankCheck'" or something similar. Check
out the help topic for the DMax function if you aren't already
familiar with it.

HTH,
 
B

Bob Hairgrove

Any further suggestions?
Click to expand...

I forgot to add that the default value should be set in code running
in the AfterUpdate event of the combo box, not as a property in design
view. If some other mode of payment is selected, another more
appropriate default value should appear.

Actually, the *value* of the text box, and not the default value,
should be set if this is running in the AfterUpdate event of the combo
box. Sorry for the confusion here (it's late in Switzerland right
now)!
 
J

JohnE

Thanks for the info. I still have some time and will re-evaluate the table
structure. That seems to be the best avenue to take. It is now getting late
here in Minnesota so it's time to call it a day.
.... john
 
Top