Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 03-03-2004, 03:11 AM   #1
scouser
Newly Registered User
 
scouser's Avatar
 
Join Date: Nov 2003
Location: England
Posts: 767
Thanks: 12
Thanked 2 Times in 1 Post
scouser
Set Start Value for Order Number

Guys I have been having a dabble but am unsure how to proceed. I currently have a tblOrders, OrderID = AutoNumber. I have added a field 'OrderNumber'. I want to set the initial value of OrderNumber to 1000. I have butchered some code:Module modSetNum (No Laughing!!)
Code:
Public Sub SetNum ()
Dim dblOrderNo As Double
Dim dblEnquiryNo As Double

dblOrderNo = Nz(DMax("InvoiceNo", "tblInvoice") + 1, "1000")
dblEnquiryNo = Nz(DMax("InvoiceNo", "tblInvoice") + 1, "1000")
End Sub
Right how do I get the above to work? I will need to amend my order form to show the OrderNumber not current OrderID field. I have no idea how to call the module to set the value for my OrderNumber!! (or if the module code actually works!!!)
Cheers,
Phil.

scouser is offline   Reply With Quote
Old 03-03-2004, 04:08 AM   #2
norman
Newly Registered User
 
norman's Avatar
 
Join Date: Apr 2001
Location: Pinchbeck
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
norman
Quote:
Originally Posted by scouser
Guys I have been having a dabble but am unsure how to proceed. I currently have a tblOrders, OrderID = AutoNumber. I have added a field 'OrderNumber'. I want to set the initial value of OrderNumber to 1000. I have butchered some code:Module modSetNum (No Laughing!!)
Code:
Public Sub SetNum ()
Dim dblOrderNo As Double
Dim dblEnquiryNo As Double

dblOrderNo = Nz(DMax("InvoiceNo", "tblInvoice") + 1, "1000")
dblEnquiryNo = Nz(DMax("InvoiceNo", "tblInvoice") + 1, "1000")
End Sub
Right how do I get the above to work? I will need to amend my order form to show the OrderNumber not current OrderID field. I have no idea how to call the module to set the value for my OrderNumber!! (or if the module code actually works!!!)
Cheers,
Phil.

You don't need any code to do this, make the field autonumber and follow the instructions in Access help - autonumber.
Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number..................

HTH
Norman
norman is offline   Reply With Quote
Old 03-03-2004, 04:32 AM   #3
scouser
Newly Registered User
 
scouser's Avatar
 
Join Date: Nov 2003
Location: England
Posts: 767
Thanks: 12
Thanked 2 Times in 1 Post
scouser
Cheers

Thanks for that. I will have a look at the help. Never been much good putting the help menu examples to good use!!

scouser is offline   Reply With Quote
Old 03-03-2004, 05:10 AM   #4
scouser
Newly Registered User
 
scouser's Avatar
 
Join Date: Nov 2003
Location: England
Posts: 767
Thanks: 12
Thanked 2 Times in 1 Post
scouser
Primary Key Problem

I did as suggested. All was going well until I attempted to remove the PK from OrderID field. (See attached Jpeg). No matter what I do to the Relationship table I can't remove the error!! I am attempting to amend a number in an existing table, not new.
Cheers,
Phil.
scouser is offline   Reply With Quote
Old 03-03-2004, 06:14 AM   #5
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
I don't think Norman's idea is good, even if you can get it to work. The use of an autonumber in a multiuser environment will lead to non-continuous numbering. An autonumber value will be set when a new record is started by a user. The next user will then get the following number. If the first user abandons the record, then that autonumber will not be allocated to a record, but the following user's autonumber will be retained, thus leaving a gap.

A better idea is to use DMax to find the largest existing number, but only allocate that when the record is complete and committed. Applying an index, no duplicates, will ensure that simultaneous saves will not result in duplicate numbers.

There may be other solutions.

If you want to commence your numbering at 1000, then seed your table with a dummy record with a number of 999. You can arrange to either delete this later, or exclude it from any queries.
__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 03-03-2004, 06:39 AM   #6
scouser
Newly Registered User
 
scouser's Avatar
 
Join Date: Nov 2003
Location: England
Posts: 767
Thanks: 12
Thanked 2 Times in 1 Post
scouser
Dirty Hacker

Neil any chance of some sample code and how to implement the Dmax option? How will that tie in with my current set-up? At the moment all my data is test data so I can delete EVERYTHING if required!!

I need to implement this in 2 tables:
tblOrders - PK = OrderID / AutoNumber
tblEnquiries - PK = EnquiryID / AutoNumber

I also have tblOrderServices - PK = ID & a foreign Key OrderID (Same for tblEnquiries.......)

Hope the additional info helps!!!!
Phil.
scouser is offline   Reply With Quote
Old 03-03-2004, 07:16 AM   #7
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
I suppose you could have two buttons on your form that collects the data from the use. Set one to undo the record if the user wants to close without saving the record. The second one should be the Save button. In here I would requery the control that holds the order number. The control should have its data source set to =DMax(tblMyTable.Ordnerno)+1 (substituting the relevant atble and field names). If you think a changing number in here would confuse the user, set the control to invisible and pop up a message box displaying the order number.

You may have to play around with this a bit to get it to work. I'm just a hacker, you see, not a proper programmer!

__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Old 03-04-2004, 07:42 AM   #8
scouser
Newly Registered User
 
scouser's Avatar
 
Join Date: Nov 2003
Location: England
Posts: 767
Thanks: 12
Thanked 2 Times in 1 Post
scouser
All working

Picked this up on my travels:

Code:
If Me.NewRecord Then
    On Error Resume Next
    Me!OrderID.DefaultValue = Nz(DMax("[OrderID]", "tblOrders"), 999) + 1
End If
Not sure what the 'Resume Next' bit does!!
Phil.
scouser is offline   Reply With Quote
Old 03-04-2004, 07:54 AM   #9
neileg
AWF VIP
 
neileg's Avatar
 
Join Date: Dec 2002
Location: Newcastle, England
Posts: 5,975
Thanks: 0
Thanked 8 Times in 8 Posts
neileg has a spectacular aura about neileg has a spectacular aura about
Yes, good. Setting the value if no record is found to 999 is better than my idea of seeding the table.

I suspect that this code frogment has come from a For..Next loop, and the resume next ensures that the process isn't halted on an error.

__________________
You can't always get what you want, but if you try sometimes you may just get what you need - Rolling Stones

Cheers, Neil
neileg is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 05:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World