How do I increment an already existing numeric field in Access 2013?

[Oldtimer]

The last time I worked in Access was back in 2007, so a lot has changed with the versions and I've forgotten a lot as well. I'm trying to increment a numeric field already created in Access 2013, but I'm not getting anywhere. I've tried to create it through "code builder" using DMAX, but that didn't work. Then I tried using it through a "macro", but it didn't work there either. So much has changed in the 2013 version that I'm having a hard time finding the correct code and place to put it. Can someone help?

[burrina]

Can you post the Details of your issue? Table,Field?
The general syntax is;

SomeField = Nz(DMax("FieldInTable", "YourTableName") + 1)

[Oldtimer]

I tried...
Private Sub RecordLocator_beforeinsert (cancel as integer)
Recordlocator = Nz (Dmax ("recordlocator", "customers")) + 1
End sub
recordlocator is my long integer field
customers is my table

[Oldtimer]

Then I tried a macro. I was going to use "setvalue", but couldn't find it in 2013 version so I did the following macro.
SetLocalVar
Name Recordlocator
Expression= Dmax ("[Recordlocator]", "[Customers]") + 1

[June7]

Why not just use Autonumber field type?

The SetValue can be accessed by clicking Show All Actions on the macro design tab.

[Oldtimer]

I can't find an option for that in 2013 version. Plus, will that work if I have imported data that has that field already populated? I want to start off from where it left off in that imported file.

[June7]

Did that solve the issue?

Then I guess Autonumber is not suitable for this situation.

With macro builder open, do you see the macro tools design tab?

[Oldtimer]

I do see the design tab.

[Oldtimer]

Did you see the code I posted? Any suggestions?

[June7]

If you see the tab then should see Show All Actions.

I don't use macros, only VBA. What happened when you ran the VBA? Error message, wrong results, nothing? I would think your VBA as shown should work but I have never used BeforeInsert event. Try BeforeUpdate.

[Oldtimer]

I don't get an error, just don't get any results. I need to only increment this field when I'm "adding" a record not updating.

[Missinglinq]

...will that work if I have imported data that has that field already populated? I want to start off from where it left off in that imported file...

Maybe you need to back up and explain what you're trying to do here, in a little more detail.

1. What is this Field actually being used for?
2. Where is this imported data coming from?
3. What is to keep the Field in an imported Record from having the same value as a Record that already exists in the Table you're importing it to?

Linq ;0)>

[ipisors]

I second Missinglinq's motion. We really should step back here. It's not at all clear what you want this for, why you want it, what its purpose is going to be, why you don't just design your table with an AutoNumber integer (if your objective aligns with that) - etc.

I can't even begin to make a suggestion when I can't tell at what specific time you need this number generated, and why you need it generated. 75% of the time when people want to create incremental numbers in Access, they're making a mistake. I do know that.

Do you need a surrogate key? Use an auto number at the table level.
Do you need a natural key? If so, an incremental number is not natural.

Background, purpose, usage, runtime description, please!

[Oldtimer]

The company I work for is a home services/construction company that bought out a local company and inherited their previous customers for "warranty" issues if any arise. So, I have a file from the prior business owners with customer files and their line item files. I've imported their old customers and now I want to build on that with new customers moving forward with the new business owners. Here is an example of what I need to accomplish...
1. A customer file is created for needing a new roof
a. Line item with Shingles 125 units @ $12 a unit
b. Line item with Boards 60 units @ $5 each

Then say 2 years later the same customer calls back and needs some waterproofing done on their home.
1. Add another customer record indicating waterproofing work
a. Line item with 6 downspouts @ $45 each

So, I create a new customer file each time a customer calls for work to be done. They remain the same "customer number" so we don't have multiple "physical" files for the same person. But I also create a Line Item table that connects how much product and what product we use for each job for that customer. The way the previous business owners had it setup was that a "Record Locator" field was initially created uniquely for each customer file entered and that Record Locator number was the link to the job costing in the Line Item table so we can tell what products and the cost for a particular customer per job we performed. Does that make sense/help?

[June7]

Still confused by terminology.

You say 'create a new customer file each time a customer calls'. Do you mean a new record?

You say 'create a Line Item table', tables should not be created just because you have new data. Do you mean you enter records to existing table?

Then you say 'a "Record Locator" field was initially created uniquely for each customer file entered'. Do you mean there is a single field with unique ID value for each customer?

I see two issues involved:

1. generating a custom unique identifier for each customer, something that has meaning to customers and db users

2. defining the PK/FK fields for linking customer and associated job records

The custom identifier can be the PK/FK or an ID generated by autonumber can be the PK/FK.

There are arguments and advocates for both. I have used both approaches.

If you only imported customer info and not all the historical business info, then you have a chance to make this decision now. Decide carefully.