working in access 2003

B

betty

i am trying to set up a database ina ccess and need to have a quote number
field as autonumber. however, the autonumber field needs to be driven by the
quote field (y/n) adding a number for each yes and nothing for each no.
there has to be a way.
please respond to [email protected]
 
B

Beetle

Betty

Whenever your y/n field has a value, it's going to create a record in your
table even if none of the other fields have values. If you are using
autonumber, then Access will assign a number to the new record (hence the
name "autonumber"). I would say that you may have a design problem with your
DB and/or you need to approach this in a different way. If you post back with
some more specific info about your situation, someone may be able to offer
some more specific advice.

BTW - you should be very careful about posting your real e-mail adress.
Forums like this are routinely mined by spammers
 
K

Ken Sheridan

Firstly the field would not be an autonumber per se as that automatically
inserts the value and is designed purely to guarantee unique values. Your
field would be a straightforward long integer number data type. You can
insert the next number in sequence in the AfterInsert event procedure of data
entry form by looking up he highest number currently in the table and adding
1. If you want the non-quote records to have a Null in the QuoteNumber field
then the code would go like this:

If Me.
Then
Me.[Quote Number] = Nz(DMax("[Quote Number], "[YourTable]),0)+1
End If

If you want a zero in the non-quote records' Quote Number field it would be:

If Me.
Then
Me.[Quote Number] = Nz(DMax("[Quote Number]", "[YourTable]"),0)+1
Else
Me.[Quote Number] = 0
End If

This will work fine in a single-user environment, but in a multi-user
environment then two or more users could be adding records simultaneously, in
which case they'd get the same number. In normal circumstances where
sequential numbers are generated for every record the Quote Number field
would be indexed uniquely and the index violation error could be handled to
overcome this. This can be done if the non-quote rows have Null quote
numbers but if you are using zeros for these then you can't index the field
uniquely. There are other methods you can use to prevent duplication,
however, one being to store the last used number in a separate databse on the
server and open this uniquely to get the next number. Only one user can get
the next number at any one time therefore. A user could get a number and
then abandon it or delete the record of course in which case the sequence
would be broken as the number would not be reused, as is the case when a true
autonumber is used.

I have posted a demo of how incrementing numbers can be generated in this
way at the following link. It also allows for the starting number of the
sequence to be reset.


http://community.netscape.com/n/pfx...libraryMessages&webtag=ws-msdevapps&tid=23839


Ken Sheridan
Stafford, England
Click to expand...
Click to expand...
 
B

betty

thanx. i will need to ramp up my blockers for awhile. i did get some
assistance from ken, and that may work. right now everything has a quoted
number generated whether the response is yes/no. i think we are looking for
the easiest way for the end users to go in & see what is out there, but i
don't believe they are going to actually be inputting anything other than
their status and a date. at least that is my hope. i think someone has this
confused with excel...
 
Top