Auto Increment Value on New Record

[Dante Shaheen]

I am new to Access but have done a seach on this subject to gain some info.

I have a form to record shipping information and each record contains a primary key record called "shipper number" followed by dtae, location, etc. The value of the "shipper number" is simply increased by "1" for each individual shipment.

When this form is opened, I want it in data entry mode but the complaint is that you need to look up the last value before you can enter the new "shipper number."

What do I need to do so when the form is opened, the new updated shipper number is already in place and then the other data associated with this "shipper number" can be input.

I am fairly new to this program so please keep it understandable.

Many thanks,

Dante

 

[jdraw]

From your description it sounds like your "Shipper Number" might be more meaningful (and less confusing) if it were "ShipmentNumber".

I think you could use Dmax("ShipmentNumber","ShippingInfoTable") + 1 and assign the numbers automatically. No need for the data entry person to enter that data since it can be calculated when record id about to be stored.

 

[bob fitz]

Maybe use something like this:
If Nz(Me.[NameOfFieldToPopulate], 0) = 0 Then
Me.[NameOfFieldToPopulate] = DMax("[NameOfShipperNumField]", "[NameOfTable]") + 1
End If

 

[ypma]

If the shipper number located on the table is numeric change data type toa auto number this will increase by one each time you open a new record. . You will find the data type next to the field name in table design mode.
If i have missed the point give me more info.

 

[Dante Shaheen]

Sounds simple enough. Can I do this in the control or do I have to copy into the code? I am not very sure about how it works using code. Also, will the new form open with the value in place or do I have to set a parameter to make this happen?

Thanks

 

[bob fitz]

If the shipper number located on the table is numeric change data type toa auto number this will increase by one each time you open a new record. . You will find the data type next to the field name in table design mode.
If i have missed the point give me more info.

Using this method you could end up with numbers missing. The numbers used won't always be consecutive. This happens when a new record is started and then abandoned.

 

[Dante Shaheen]

True. I need the numbers to be consecutive and continuous. I am not sure how to implement your suggestion. Need support putting it in place.

I know this is a simple task but its still new to me.

Thanks,

 

[bob fitz]

I would try the code in the form's On Current event.

I know this is a simple task but its still new to me.

If you need it, we are here to help if we can.

 

[Dante Shaheen]

I inserted the following code on current into the form.


Private Sub Form_Current()
If Nz(Me.[ID], 0) = 0 Then
Me.[ID] = DMax("[ID]", "[table1]") + 1
End Sub

I set up a sample table(table1) and I use ID and my shipper number field. However, I am not getting the desired results. I did get an error message refering to a missing endif statement.

Not sure what tot try next?

Thanks,

Dante

 

[bob fitz]

You are indeed missing an End If

Private Sub Form_Current()
If Nz(Me.[ID], 0) = 0 Then
  Me.[ID] = DMax("[ID]", "[table1]") + 1
[COLOR=red][B]End If
[/B][/COLOR]End Sub

 

[Dante Shaheen]

Did the trick. Thanks for your patience and help,

Dante

 

[bob fitz]

Thanks for your patience and help

Your Welcome. Glad I could help. Sorry I missed the "End If" in the first code posting. Really silly mistake.

 

[RMart]

Hi Bob, I was looking into this scenario of sequential numbering. I have just registered again. Forgot my past username and password. It's me Rob Martellini. You helped me so much, that when I saw your name, I had to contact you. I didn't have much luck with the accounting service calls database sales. In fact, no sales. But I still have your email(not sure) and was wondering if I could send you some money to at the least say thanks for all you have contributed to the accounting and service calls database. I'm sorry for posting here. I hope you remember me. Robert Martellini Toronto ON Canada. I don't know how to contact you here? My user name is RMart. Take care Bob. Try to reach me.

 

[missinglinq]

It should be noted that putting this kind of code in the Form_Current event is fine...as long as the database is free-standing, i.e. has a single-user on one machine.

But if this is a split database, with multiple-users, you run the chance of having two or more Records with the same number!

The problem is that the number is assigned, as noted, when a new Record is started. With multiple-users, you run the chance that UserA will start a new Record, be assigned their number, and before they complete/save their Record, UserB will start a new Record, and be assigned the same number!

You have two ways to avoid this, if the db is a multi-user app. You can, in the line
immediately following the number generating code, save the Record, using something like

If Me.Dirty Then Me.Dirty = False

The problem with this is that it can be inconvenient, for a number of reasons, to save a Record as soon as it is started.

The more commonly used method is to run the code to generate the number at the last possible nanosecond before saving the Record. To do this place the code in the Form_BeforeUpdate event.

Linq ;0)>

 

[ypma]

B Mart. Little confused as i did not reply on the subject post and cannot see y
our post . Must have been bob fitz. You addressed you post to Bob and there are a few of us about .

Regards Ypma

 

[RMart]

Hi Bob, I did email you money from PayPal. I sent you an email. Please check your spam or junk, in case it doesn't get to your inbox?


Please reply to my email and let me know if the funds arrived. I did read your message and replied via email.

Take care Bob.

Sincerely,

Rob

 

[RMart]

Sorry YPMA.


Rob