AutonumberProblem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a very, very beginner with Access. I have received some responses from
my question yesterday, but I don't know how to implement it. I was told to
use the DMax function and was given the formula. I need step by step
instructions. I know nothing about visual basics.

I have an ID Field with pre-existing numbers. This table was imported. I
want to know step by step what I need to do with this DMax function. Here is
what the person gave me =DMax("ID","ASPATRON")+1. I went to View design,
changed the field size to LOng integer, placed the formula in Default VAlue
and went to save. Access did not reconize DMax. Where am I going wrong?
HELP!!!!

If anyone can help, please walk me through step by step. I'm not kidding.
I saw a small databased that illustrates the DMax function, but didn't know
what I was looking at. I am not a programmer!!!!

Thanks.
 
I hope that's a fake e-mail address you're using. If it isn't, go back and
edit all of your e-mails if you can, but it is probably too late to avoid a
lot of spam.
 
You're asking us to provide step by step instructions without describing
clearly exactly what it is that you are trying to *do*. I'm guessing
that you are trying to create your own custom counter as a primary key
in your ASPATRON table rather than using an autonumber or some unique
combination of fields. If this is correct, then this doesn't really have
anything to do with VB programming. Custom counters are fairly routine,
but you can't just plop the formula in table Design View and expect
Access to be able to figure what you meant to to achieve. Most older
versions of Access (not sure about XP) do not support Table Triggers.
Are you trying to change the existing ID numbers that were imported with
your table? If so, then your options are to either use an Update query;
or to delete the old ID field, compact the database, then add an
autonumber field to your table & let Access number each record
sequentially. After that, you would change the datatype in the new ID
field from autonumber to number (integer(long)). Then for new records,
use a Form to assign the new, incremented ID using the DMax()+1 function
(either in the BeforeInsert event or in the Click event of an "assign
id" button) each time you add a new record to the table.

hth,

LeAnne
 
LeAnne,

Thanks for your response. I do not want to change the existing ID numbers
that were imported. What I want to do is have Access start with the next
sequential number when I create a new record. Is this possible? If you can
help me, I would really appreciate it.

MARY
 
LeAnne,

Thanks for your response. I do not want to change the existing ID numbers
that were imported. What I want to do is have Access start with the next
sequential number when I create a new record. Is this possible? If you can
help me, I would really appreciate it.

MARY
Click to expand...

You must be trying to do this in the Table.
Do it in the FORM that you (hopefully) are using for data entry.
Just open the control's property sheet.
Click on the Data tab.
Write your expression on the Default Value line.
Save the change.
New entries will have the next higher number as default.
 
Mary - As a (maybe) advanced beginner my general advise to you is get a book.
Lots of them, even. I started with Access (97) for Windows for Dummies and
what I *should* have done is to start at page 1 and read every word and tried
out the examples they provide. I've borrowed books from the library to test
and then found them on Ebay, too. And I have a loose-leaf notebook where I
add printouts of stuff I find on the web so I can refer to it again.

Good source of info: go to Google | Groups and type your question, like
"Access Autonumbers" and a selection of previous posts and answers will
appear.

One page I have in my notebook is the ten commandments of Access.
www.mvps.org/access/tencommandments.htm The first time I looked at it I
thought - wow, this is important stuff. Then I got annoyed at the "Thou
shalt" language, and then I realized I had no idea what half of it meant. I
still don't follow some of it. There is a lot of good information on the mvps
site.

Getting back to your original problem, instead of jumping off the deep end
with incremental numbers - how often do you add new fields? You could add a
new field to your table which is an autonumber - it doesn't need to mean
anything to mere humans, then add your sequential number that is important to
you by hand. Do this for a couple months until you're more confidant and then
tackle automating it.

Will your employer spring for a class in Access?

One book I have is "Access 97 Answers" pub Osborne McGraw-Hill. The answer
to the question "How can I change the initial value of an Autonumber field to
something other than 1" begins:
"It's a little complicated to do . . . ." and I won't duplicate it here -
don't want to infringe on copyrights. But in general on books, I'm still
using '97 books even tho' I'm using a newer version of Access.

Gotta go - hope this helps! - Lisa
 
Hi Mary,

See the last sentence of my previous response. You must ensure that all
data entry is done via a Form (which is the preferred mode in most
cases). Open the data entry form in Design View, select the ID field,
then right-click & go to Properties (or click Properties on the
toolbar). Insert the formula (=DMax("ID","ASPATRON")+1) in the
BeforeInsert event of the the ID field. Another option would be to
automate this action by adding a button to your form (using the Control
toolbox) & adding the same formula to the OnClick event of the button.
Note that this will work just fine if you're working with a
non-networked, single-user database. BUT...if this is a multiuser,
networked db with several users competing for the "next" ID value,
unfortunately there will be some VBA coding involved. For detailed
instructions on how to do this, follow this link to the MS Knowledge
Base article, "ACC2000: How to Create a Multiuser Custom Counter."
Although the title says A2000, this technique should work with all
versions of Access from 2000 on.

Good luck,

LeAnne
 
Hi Mary - your problem spurred me to fix my similar problem. I just got good
advise (see "Change a number to Autonumber" above) that you might want to
print for future reference.

Two other comments - I'm going to make the changes recommended, BUT first
I'm going to make a copy of my 'good' database, so if I goof up, I haven't
lost anything. And I tried working with the replies LeAnne wrote you, and
found that the BeforeInsert event only applies to a form, not a single
control. Then I still couldn't get DMax to work anyhow - I had to be talked
through it line-by-line for the one place I used it, and I've been mucking
about with Access for several years now.

THe one work-around that I was using on my non-autonumber field was in the
form, type CTRL Apostrophe in the field with the ID #. This copies the
previous record's number and then lets you edit it and add 1 manually. Cheap,
dirty and wrong, but it will work for the next long while, while you're
learning more.

Good luck
 
LReber wrote:
And I tried working with the replies LeAnne wrote you, and
found that the BeforeInsert event only applies to a form, not a single
control.
Click to expand...

Whoops! You're absolutely right. Thanks for catching that.

LeAnne
 
Back
Top