Is there a way for me to declare a variable, say x=10000001. Any time I call this variable, I'd like it to take that number and add one to it. So when X is called, I get 1000001. The next time it's called I get 1000002. the next time 100003, etc...

This way when I am assigning this number it will always be unique and be easily called from a query. Especially an update query. Would this be feasible in VBA?

Didn't notice which version of Access you are using?

You've described "how" you want to do something ... now, if you'll describe "why" (i.e., "what" business need will having that capability allow you to solve?), folks here may be better able to offer appropriate suggestions. You may not realize it, but some folks pick up a chainsaw and try to drive nails with it. Might work ...<g>

More info, please...

Access 2010

I initially attempted to try the Why portion of this another way. It become very cumbersome to perform what I think should be a simple task. I'm attempting to create a unique number to be used as a UPC. I had been thinking that if I could assign a variable that increased by one number every time it was used, that I would never be able to use the same number. The old way I did this is below

I created a table with an Autonumber and then formated that autonumber into a different field by adding a one in front of it and then 000001. This gave me a final UPC field of 100001, which would work for the UPC I was looking for. I have another table that has items (could have 20-30 in this table) and these items must be assigned this unique UPC number. The only way I could figure to give each one of these items in the table needing the unique number was to create a field in the new table that was basically a autonumber field itself. Then I created a query that selected the MIN of this number. This isolated one record from my item table. I then updated the UPC field of this table to equal the isolated one from the UPC table. Long story short, after I updated the Item table with the new UPC, I had another query that updated the UPC table to Not Available and then went to the next one. I cycled this through for every item I had in the item table. Very cumbersome process to give items in a table a unique UPC. Thus the reason I was wondering if I could just store a number in a variable, call it, and then add one to it every time I called it.

Don

If you're talking about MS Access, you don't get to modify the value of an Autonumber field ... it gets created, well, automatically, by Access.

I suspect what you're looking to do could be handled by a routine that goes by the name of "Custom Autonumber" and/or "Sequential numbering". Search here at UA and online for these terms. The generic explanation is that Access will look and see what the greatest existing number is, add one, then use that. You don't need to store what it is, adjust it, and re-store it.

TempVars may provide an interesting solution.
hope this helps

Hi Don,

You may be working harder than you have to just to generate a UPC for your "items." If the business rule is just to have a "unique" number for each item without any regards if they're sequential or incremented at a predetermined interval, then I think a simple Autonumber field is all you need. If so, you can avoid all the code and query to update several tables just to keep track of the UPC number. You also won't need a system-wide variable, which if I understand your requirement correctly won't work anyway because a variable would reset to initial value when you close the db.

If you can explain the whole business process that your database is trying to model, I'm sure we can give a suggestion that would be more appropriate for your needs.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

It's a little different. Some of the items already have an established UPC, I just have to create a new UPC for the items that don't already have one. I've got some good ideas here. I believe you may have responded to another thread I started and I was able to create what I needed, it just took like 10 steps in a macro. But I'm thinking a new game plan now and may actually make it easier. If I use the autonumber within the table I currently have and simply do an update query on the null values to replace the null to the autonumber formated to 1 & 000000 I should be able to update the non-upc's to this new unique number. Previously, I was going through all the steps above.

Hi,

Yes, I think that's a good plan. Just make sure non of the existing UPCs would result in a duplicate with the new UPCs that's generated using the Autonumber field. If so, you might consider also generating new UPCs for the existing ones too.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)