Auto Increment Value on New Record (1 Viewer)

D

Dante Shaheen

New member
Local time
Today, 05:14
Joined
May 10, 2012
Messages
8
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

jdraw

Super Moderator
Staff member
Local time
Today, 08:14
Joined
Jan 23, 2006
Messages
15,379
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

bob fitz

AWF VIP
Local time
Today, 13:14
Joined
May 23, 2011
Messages
4,722
Maybe use something like this:
If Nz(Me.[NameOfFieldToPopulate], 0) = 0 Then
Me.[NameOfFieldToPopulate] = DMax("[NameOfShipperNumField]", "[NameOfTable]") + 1
End If
 
Y

ypma

Registered User.
Local time
Today, 13:14
Joined
Apr 13, 2012
Messages
643
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.
 
D

Dante Shaheen

New member
Local time
Today, 05:14
Joined
May 10, 2012
Messages
8
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

bob fitz

AWF VIP
Local time
Today, 13:14
Joined
May 23, 2011
Messages
4,722
ypma said:
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.
Click to expand...
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.
 
D

Dante Shaheen

New member
Local time
Today, 05:14
Joined
May 10, 2012
Messages
8
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

bob fitz

AWF VIP
Local time
Today, 13:14
Joined
May 23, 2011
Messages
4,722
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.
Click to expand...
If you need it, we are here to help if we can. :)
 
D

Dante Shaheen

New member
Local time
Today, 05:14
Joined
May 10, 2012
Messages
8
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

bob fitz

AWF VIP
Local time
Today, 13:14
Joined
May 23, 2011
Messages
4,722
You are indeed missing an End If
Code: 
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
 
bob fitz

bob fitz

AWF VIP
Local time
Today, 13:14
Joined
May 23, 2011
Messages
4,722
Thanks for your patience and help
Click to expand...
Your Welcome. Glad I could help. :) Sorry I missed the "End If" in the first code posting. Really silly mistake.:eek:
 
R

RMart

Registered User.
Local time
Today, 05:14
Joined
Jun 12, 2018
Messages
12
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

missinglinq

AWF VIP
Local time
Today, 08:14
Joined
Jun 20, 2003
Messages
6,423
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)>
 
Y

ypma

Registered User.
Local time
Today, 13:14
Joined
Apr 13, 2012
Messages
643
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
 
R

RMart

Registered User.
Local time
Today, 05:14
Joined
Jun 12, 2018
Messages
12
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
 
You must log in or register to reply here.

Similar threads

T
Solved Add new record from list box
Replies
5
Views
399
arnelgp
arnelgp
B
Solved Data entry form how can I only show new record
Replies
2
Views
179
MajP
MajP
T
Solved nagivation control. where clause = new record
2
Replies
25
Views
410
theDBguy
theDBguy
A
Solved Two Combo Boxs for Name & Number
Replies
4
Views
362
accessonly11
A
J
New Record if Matching Record Not Found
Replies
9
Views
519
jacko6
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom