auto number

[ChrisO]

My question was intended for the original poster, but to make it clearer…

Why does the original poster think it is required to have the next Purchase Order Number prior to the Purchase Order itself being created?

Chris.

 

[RainLover]

gizmogeek

ChrisO asks a very important question.

AutoNumber is created immediately and that causes your problem.

Also you said you were going to start a new thread to get rid of some of the unwanted things in this post.

If you have please put a link at the end of this thread to the new thread. Then when you start a new thread put a link back to this one. That way we will all know what is going on.

My question was intended for the original poster, but to make it clearer…

Why does the original poster think it is required to have the next Purchase Order Number prior to the Purchase Order itself being created?

Chris.

 

[gizmogeek]

I don't need the auto number as soon as it opens but before the form is updated. I believe RainLovers code would work for me but my programming skills are very rusty. I posted the code that I added but sure that the error is on my part. I must not have changed something in the code correctly.

I did start a new thread on this but someone opened this one back up. I deleted it do to the like 15 posts of controversy that are an embarrassment to this forum.

New thread: http://www.access-programmers.co.uk/forums/showthread.php?p=1250244#post1250244
Thanks!

 

[RainLover]

I don't need the auto number as soon as it opens but before the form is updated. I believe RainLovers code would work for me but my programming skills are very rusty. I posted the code that I added but sure that the error is on my part. I must not have changed something in the code correctly.

I did start a new thread on this but someone opened this one back up. I deleted it do to the like 15 posts of controversy that are an embarrassment to this forum.

New thread: http://www.access-programmers.co.uk/forums/showthread.php?p=1250244#post1250244
Thanks!

I was one of those that suggested starting a new thread. I did mean to put a link at the end of this one and at the beginning of the next.

As the OP (Original Poster) said it would get rid of the bad vibes without deleting them.

The has been reversed so let's move on with some positives.

Here are some thoughts.

The moment a number is produced it cannot be reproduced. Even if it is deleted. I know of no laws that say numbers must be consecutive. In fact you don't even need to use numbers on Invoices. However, it is better if you do. Therefore this becomes a Business rule. A rule within your organisation.

If you can live with some gaps in the numbering system then we can produce that number at the start. Then it could be reused if no other user tries to add a new record.

The commonly accepted method is to create the number as you save (Just Before) the entire record. This gives the most likely best result.

You have the code to do this; you only need to put that code in the right place and then save the record immediately.

The second part is where you have several users who are requesting a number regularly. In this case you can end up with duplicates.

ChrisO and I have written a test Data base for multi users. We programmed three computers to loop and keep adding numbers and ran it overnight. Therefore, we had three computers writing to the same Back Ends constantly for 12 hours. It did not fail. No duplicates and no missing numbers. To use this it is a bit harder that a single user type system.

What you need to do is to access all the information here and choose which way you want to go. I guess they all have their pluses and minuses.

Tell us what you want and please post a copy of your Database.

Then we will be in a better position to help.

PS My link has been fixed so you should be able to download either sample.

 

[RainLover]

I am as sick as three Mother in Laws.

But I still need to know when you need this number to be calculated.

i.e. First up, Last Thing or sometime in between.

Please do not leave it to me to decide when

 

[gizmogeek]

I think I probably want the number upon save that way there would be no duplicates.

 

[RainLover]

gizmogeek

I doubt that you will ever talk to me again after this.

Did I not point you in the direction of naming conventions? If I didn’t then look at my signature link. There is a document there plus others on this site and by Google.

For a start you do not name Fields with the prefix of tbl for Table.

You need foreign keys of which you have none.

You most likely need another two or more tables.

I suggested you read a Dummies book or an Idiots book. I was serious and said so in a PM. However, you got upset with me. I also suggested that you start again, and again you got upset with me.

You have repeating data in the form of, tblQuantityOrdered_1, tblQuantityOrdered_2, tblQuantityOrdered_3, tblQuantityOrdered_4. tblQuantityOrdered_5. What would happen if you needed 12 or more. You can’t do that and you should be able to.

In your code you have Combo1, Combo2, Combo3, Combo4, Combo5 etc. When you come back to this code in 6 months time how are you going to know which Combo is which.

I am not a school teacher and am not about to teach you how to write Access Databases. You need to follow some suggestions and do some research.

Your Relations do not exist and this should be high in your list or priorities. Have a look at the Northwind Database written by Microsoft.

I doubt if any one who has tried to help you was to download your sample would disagree with me.

I am stopping here before I go too far.

 

[Beetle]

If this is not the place to ask can someone point me to somewhere that is more basic?

You can ask here, and you should be able to get answers here, but steps need to be taken one at a time and in the right order. I haven't actually looked at your db copy yet, but from what Rain said in #74 it sounds like you're putting the cart before the horse. If you have repeating fields, no foreign keys and no relationships established yet, then us helping you figure out how to assign the next PO number on a form isn't really helping you. You're building on a faulty foundation and it's all going to come crashing down. At that point, getting the next PO number assigned is going to be the least of your worries.

I think you should go back to your table design and get that correct first. We can help you with that, but you may want to start a new thread since that is really a different subject than what has been discussed in this one.

 

[RainLover]

gizmogeek

Where are you with this DB.

If you need a little help I should be available.

If you have more questions could you post a copy of what you have so far.

Access 2003 would be best. If it is getting big go to the FAQs for instructions on how to zip and upload.

 

[gizmogeek]

Wow RainLover you have good timing. I opened up a new thread (http://www.access-programmers.co.uk/forums/showthread.php?p=1253844#post1253844) just this afternoon. I'll zip the database and attach it to the new thread.
Thanks again!