Ok I've just begun to use Access 97 and there's two annoying problems which I haven't been able to find a solution for:

1) Autonumber fields end up out of sequence, e.g. I add a record and the autonumber field is 2, I then delete this record and then add a new one - but the new one is now 3, not 2. So the sequence goes 1, 3, 4, etc... Anyone know how to fix this? I've also had this happen under MySQL.

2) When you open a db in Access you can set an option to automatically display a form when it's opened. This works fine but I'd like to know what option tells it to maximise the form.

The 1st problem is probably just a way of Access to keep track of unique ID's as such.

Think for instance if someone gets a student number at uni, and then they drop out or finish, someone else doesn't then get that ID. I don't know of a way around this but they should be.

Autonumber fields are just a way to give a record a unique id, it shouldn't matter what the number is, whether they're concurrent or not, just as long as their unique. If you need concurrent numbers, you probably shouldn't be using an Autonumber field, but if you're using the Autonumber field to act as a unique record id, then it shouldn't need to be concurrent.

Theres no way around this afaik, and rightly so, its designed to behave that way. If you want a different numbering scheme, just use a plain number type and increment/decrement it yourself.

Also, why not use Access 2000? Its much better, and the database side of things is much more efficient (its basically cut down sql server).

I don't have Office 2000, only 97 Professional.

Two caveats before you read my suggestions - I've been using SQL lately and when I have been using Access it's 2002, but I'm pretty sure what I suggest works.

The Autonumber thing is by design as other people have said. But you can get around it by a couple of means. If the field is not your primary key then you should be able to manually change the values in there. I think from memory I did it by doing an insert query to insert rows in the numbers that were gaps.

And if it is the primary key you can sort of remove spaces... If you add a record (id 3) and then delete it the next record will be ID 4. If you compact and repair the DB before adding the next record it will take on ID 3 instead. That doesn't help if you have a gap in the middle of your records though.

As for the maximised option, there are a number of ways to do it. You can do it programmatically - ie: code some VBscript in the Form Open event to maximise it, use a macro to open it and put a maximise event in there. And... I forget but there was a third way I used to use (so long since I used access...)

there is no way around the autonumber.

AH f***ET! I'm gonna have to reinstall Office on my home PC now because I'm curious...

Yeah skrybe I was just considering a macro before. Anyone happen to have a decent online VBA tutorial?

There are no decent VBA tutorials, because VBA isn't decent! haha! Damn I'm funny.

I thought I was right. Note: This was tested in Access 2000 cos that's the oldest version of Office I have on hand...

If you have a table with an Autonumber field that is NOT a primary key you can insert records into it using an Append Query (same as an Insert Query in SQL). Say you have records 1,2,4,5 you can create record 3 by the following;

INSERT INTO TableName ( FieldName ) SELECT 3 AS Expr1;

And the other tip about compacting and repairing - that works too. But only if you had records 1,2,3,4,5 and deleted record 5 - then compact and repair. If you deleted record 3 compacting and repairing won't allow you to fill that gap.

Let's just say I'm not willingly learning VBA.

PFFT! to Khel... ;)

There are heaps of good VBA tutorials. Even the standard help that comes with access is fairly comprehensive if bloody clumsy to use. This is one of the best The Access Web. Otherwise just do a google search on the particular thing you're trying to achieve and you should get hits.

If you're in a real hurry here is the code to put in the Form Open Event. It's really complicated...

DoCmd.Maximize

best why is to buy a vb book. sybex do a few good ones pretty cheap, $30 or wrox are great but cost $90+

learn about events, objects, properties first.

then you'll be able to use code samples from planetsourcecode.com or msdn.com

also get fimilar with object explorer.

get a good naming convension going.

then youll be able to get into enough mischief for a while.

Thanks for the help guys, much appreciated it :).

Heheh I didn't want to hit him with the naming conventions yet :) But definitely a good point. It makes code much easier to read afterwards. I just spent two weeks debugging faulty code that someone else wrote - and didn't use data type prefixes, consistent variable naming, anything useful at all. It made a 2 day job a 2 week one.

MSDN is good but has the same problems as their online help - ie: it's a pain in the arse to try and find stuff - but it's all there... somewhere :)

why not learn vba
its a nice clean language
great for glue!

yer, msdn is great if u can find it.

i was a home tought vb boy gone pro. had to re learn all my naming convestions because no-one told me the standard format in the first place.

thats why i meantioned it, get into the habbit of using correct names and terms, elimates teh pain of relearning.

Yeah ditto Mooby. I bought Access 1 when it first was released because I'd been doing some tinkering in Infomix and DB3 (or was it DB4...) Then I upgraded to Access 2 (still learning from home) and at that point I wrote a couple DBs for work and we started using it there. I finally went on an official advanced Access course (about the time Access 97 came out) and found I knew more than the tutor who was running the course. It was bloody annoying cos I paid to go (well work paid for me to go :) and it was a complete waste. I learned nothing :( I now work with MS SQL Server building and administering databases for our work intranet. Not bad for no formal training at Uni :)

re: Naming Conventions

It's unfortunate that almost all of the sample code that microsoft ships with Access doesn't use naming conventions, and the table names, fields etc have spaces in them... Argh! How many bugs are caused by that... Their samples are great for beginners who just want to have a DB that lists their CD collection or something. But for a semi-professional system it teaches bad habits. Pity they don't include a copy of the Lezynzki standards in the help :)

or you could come to QUT, go into S Block level 7 and *borrow* MSDN..

although.. you know.. i get this sneaking feeling that people are copying it *shock* *horror*

sif half the people here pay for any software whatsoever (here = QUT)

soz to revive... but...

if u have a feild with an autonumber, delete all the records, the compact the db, it resets the autonumber to 1

peace out.