HOWTO: Create an autonumber that has letters
Q. I want to create an autonumber with a text part, for example JL0001, JL0002 etc. How do I do this ?
A. First of all, while you can do this, I wouldn't recommend using the field above as a primary key. Use an alternative field as a unique ID, such as an autonumber. Numbers are more efficiently indexed and compared, and are generally faster (and take up less space if you have a child table with related records) to use as index fields.
To create our text-number type autonumber, we *must* do the data entry through a form. You can't put the logic and VBA code directly into a table.
In this example, we have a table called tblCustomers, which has a text field called AccountNumber. This is where we want to put our "autonumber".
We do our data entry in a form called frmCustomers, bound to the tblCustomers table. A text box on the form called txtAccountNumber holds the AccountNumber field.
We need to create some code. While we could put this code directly into the form, we'll put it into a module instead (in case you want to do this in several different forms).
*Note*: To use the code in this example, you will need a reference to DAO (Data Access Objects) if you are using Access 2000 or later. When you are in the VBA editor (see below), click on the Tools menu, and then click References. Scroll down until you find "Microsoft DAO 3.6" and tick that.
Go to the Modules page in the database window, and click New to open up the VBA editor, and type in:
---
Function CreateAutonumber(strPrefix
End Function
---
We're creating a function into which we pass two parameters - the text prefix that we want to add (eg. "JL"), and the number of digits we want in the numeric part of the autonumber. All of the following code goes in between the two lines we've typed above.
We now need to create some variables to use later on in the code, so enter this code into the function:
---
Dim strAutonumber As String
Dim rst As DAO.Recordset
Dim strTemp As String
---
strAutonumber will hold our final result.
rst is a general recordset variable which we will use to retrieve existing data from our table.
strTemp is a temporary string which we'll use to store information.
The first thing we need to do is to retrieve the highest number we already have. The most efficient way to do this is to use a recordset. While we could do it using the DMax domain aggregate function, this can be very slow if you have a lot of records. Our code looks like this:
---
Set rst = CurrentDb.OpenRecordset("S
If IsNull(rst!MaxAccountNumbe
strAutonumber = strPrefix & Format(1,String(lngDigits,
Else
strTemp = Mid$(rst!MaxAccountNumber
,Len(strPrefix)+1)
---
The first line opens the recordset to find the highest account number in our tblCustomers table that starts with our prefix. MaxAccountNumber contains this value - if it is null, we didn't find any. If it is, we create the autonumber starting at 1.
Let's look at the Format function that we've used in a little bit more detail. We give it two parameters, the first one being the expression we want to format, in this case the number 1. The second part is the format we want to use. If we wanted a 5 digit number, we need to give it the format "00000". However, we passed the number of digits we wanted in the lngDigits parameter, so we use the String function to build our format string. String creates a string of repeated characters and takes two parameters - the first is the number of times we want to repeat the character, and the second is the character we want to repeat, in this case "0". For more information on the Format and String functions, have a look at the Access Help files.
The part after the Else statement is where we put the code for what happens when we already have a record with our prefix. The first line just grabs the number part of the field (so if we got back JL00007, we put 00007 in strTemp).
We now need to construct the rest of the autonumber. This is quite simple - we use:
---
strAutonumber = strPrefix & Format(Val(strTemp)+1,Stri
---
This is the same as the first strAutonumber line, but instead of 1, we've put Val(strTemp)+1. The val function turns strTemp into a number to which we can now add 1 to get the next number.
Now that we have our autonumber, we can finish off the procedure:
---
End If
rst.Close
Set rst = Nothing
CreateAutonumber = strAutonumber
---
We just finish off the if statement and close the recordset. Our last line tells the function to send back the autonumber string we've just created.
Now that we have our function, how do we use it ?
In our frmCustomers form, we want to put the value into the txtAccountNumber text box. We could do this simply by setting its DefaultValue property to:
=CreateAccountNumber("TL",
The problem with this is that if two people open the form at the same time, they get the same autonumber! So instead, we put it in the BeforeInsert event of the form:
---
Me!txtAccountNumber = CreateAutonumber("JL", 5)
Me.Dirty = False
---
The line "Me.Dirty = False" immediately saves the record once the user starts typing, so that if someone else tries to create a new record as well, they get the correct number.
To put the above code into the BeforeInsert event, bring up the Properties window (View menu->Properties) of the form, and go to the Events page. Click in the Before Insert line, and click on the little build button "..." that appears next to it. Chose Code Builder from the options you are given.
Because you can pass different prefixes to this function, you can use it for different prefixes within the same table. For example, all of John Smith's customers might have an Account Number starting with JS, and all of Bill Bloggs's customers start with BB. In John's copy of the database, you can use:
=CreateAccountNumber("JS",
and in Bill's copy, you use:
=CreateAccountNumber("BB",
John's accounts start at JS00001, and Bill's start at BB00001 and so on, regardless of however many records John has put in. You could store these prefixes against John's and Bill's user records and retrieve them when they log in for example, rather than hard-coding them in, but this is beyond the scope of this tutorial.
Our completed code looks like this:
==========================
Function CreateAutonumber(strPrefix
Dim strAutonumber As String
Dim rst As DAO.Recordset
Dim strTemp As String
Set rst = CurrentDb.OpenRecordset("S
If IsNull(rst!MaxAccountNumbe
strAutonumber = strPrefix & Format(1,String(lngDigits,
Else
strTemp = Mid$(rst!MaxAccountNumber
,Len(strPrefix)+1)
strAutonumber = strPrefix & Format(Val(strTemp)+1,Stri
End If
rst.Close
Set rst = Nothing
CreateAutonumber = strAutonumber
End Function
A. First of all, while you can do this, I wouldn't recommend using the field above as a primary key. Use an alternative field as a unique ID, such as an autonumber. Numbers are more efficiently indexed and compared, and are generally faster (and take up less space if you have a child table with related records) to use as index fields.
To create our text-number type autonumber, we *must* do the data entry through a form. You can't put the logic and VBA code directly into a table.
In this example, we have a table called tblCustomers, which has a text field called AccountNumber. This is where we want to put our "autonumber".
We do our data entry in a form called frmCustomers, bound to the tblCustomers table. A text box on the form called txtAccountNumber holds the AccountNumber field.
We need to create some code. While we could put this code directly into the form, we'll put it into a module instead (in case you want to do this in several different forms).
*Note*: To use the code in this example, you will need a reference to DAO (Data Access Objects) if you are using Access 2000 or later. When you are in the VBA editor (see below), click on the Tools menu, and then click References. Scroll down until you find "Microsoft DAO 3.6" and tick that.
Go to the Modules page in the database window, and click New to open up the VBA editor, and type in:
---
Function CreateAutonumber(strPrefix
End Function
---
We're creating a function into which we pass two parameters - the text prefix that we want to add (eg. "JL"), and the number of digits we want in the numeric part of the autonumber. All of the following code goes in between the two lines we've typed above.
We now need to create some variables to use later on in the code, so enter this code into the function:
---
Dim strAutonumber As String
Dim rst As DAO.Recordset
Dim strTemp As String
---
strAutonumber will hold our final result.
rst is a general recordset variable which we will use to retrieve existing data from our table.
strTemp is a temporary string which we'll use to store information.
The first thing we need to do is to retrieve the highest number we already have. The most efficient way to do this is to use a recordset. While we could do it using the DMax domain aggregate function, this can be very slow if you have a lot of records. Our code looks like this:
---
Set rst = CurrentDb.OpenRecordset("S
If IsNull(rst!MaxAccountNumbe
strAutonumber = strPrefix & Format(1,String(lngDigits,
Else
strTemp = Mid$(rst!MaxAccountNumber
,Len(strPrefix)+1)
---
The first line opens the recordset to find the highest account number in our tblCustomers table that starts with our prefix. MaxAccountNumber contains this value - if it is null, we didn't find any. If it is, we create the autonumber starting at 1.
Let's look at the Format function that we've used in a little bit more detail. We give it two parameters, the first one being the expression we want to format, in this case the number 1. The second part is the format we want to use. If we wanted a 5 digit number, we need to give it the format "00000". However, we passed the number of digits we wanted in the lngDigits parameter, so we use the String function to build our format string. String creates a string of repeated characters and takes two parameters - the first is the number of times we want to repeat the character, and the second is the character we want to repeat, in this case "0". For more information on the Format and String functions, have a look at the Access Help files.
The part after the Else statement is where we put the code for what happens when we already have a record with our prefix. The first line just grabs the number part of the field (so if we got back JL00007, we put 00007 in strTemp).
We now need to construct the rest of the autonumber. This is quite simple - we use:
---
strAutonumber = strPrefix & Format(Val(strTemp)+1,Stri
---
This is the same as the first strAutonumber line, but instead of 1, we've put Val(strTemp)+1. The val function turns strTemp into a number to which we can now add 1 to get the next number.
Now that we have our autonumber, we can finish off the procedure:
---
End If
rst.Close
Set rst = Nothing
CreateAutonumber = strAutonumber
---
We just finish off the if statement and close the recordset. Our last line tells the function to send back the autonumber string we've just created.
Now that we have our function, how do we use it ?
In our frmCustomers form, we want to put the value into the txtAccountNumber text box. We could do this simply by setting its DefaultValue property to:
=CreateAccountNumber("TL",
The problem with this is that if two people open the form at the same time, they get the same autonumber! So instead, we put it in the BeforeInsert event of the form:
---
Me!txtAccountNumber = CreateAutonumber("JL", 5)
Me.Dirty = False
---
The line "Me.Dirty = False" immediately saves the record once the user starts typing, so that if someone else tries to create a new record as well, they get the correct number.
To put the above code into the BeforeInsert event, bring up the Properties window (View menu->Properties) of the form, and go to the Events page. Click in the Before Insert line, and click on the little build button "..." that appears next to it. Chose Code Builder from the options you are given.
Because you can pass different prefixes to this function, you can use it for different prefixes within the same table. For example, all of John Smith's customers might have an Account Number starting with JS, and all of Bill Bloggs's customers start with BB. In John's copy of the database, you can use:
=CreateAccountNumber("JS",
and in Bill's copy, you use:
=CreateAccountNumber("BB",
John's accounts start at JS00001, and Bill's start at BB00001 and so on, regardless of however many records John has put in. You could store these prefixes against John's and Bill's user records and retrieve them when they log in for example, rather than hard-coding them in, but this is beyond the scope of this tutorial.
Our completed code looks like this:
==========================
Function CreateAutonumber(strPrefix
Dim strAutonumber As String
Dim rst As DAO.Recordset
Dim strTemp As String
Set rst = CurrentDb.OpenRecordset("S
If IsNull(rst!MaxAccountNumbe
strAutonumber = strPrefix & Format(1,String(lngDigits,
Else
strTemp = Mid$(rst!MaxAccountNumber
,Len(strPrefix)+1)
strAutonumber = strPrefix & Format(Val(strTemp)+1,Stri
End If
rst.Close
Set rst = Nothing
CreateAutonumber = strAutonumber
End Function
As Nico said, this is a bad idea. If your a purist, then it breaks rules regarding selection of a primary key. If you come from the surrogate viewpoint, then the key is meaningless anyway.
If you pushed that aside, generally any type of scheme you come up with can fail at some point.
So all around, it's a bad idea.
And BTW, we don't do Question/Answer type postings on EE.
Jim.
If you pushed that aside, generally any type of scheme you come up with can fail at some point.
So all around, it's a bad idea.
And BTW, we don't do Question/Answer type postings on EE.
Jim.
ASKER
It's as fast as you're going to get, and considerably faster than using DMax. The speed is perfectly acceptable, and there is no noticeable delay on a table with 10000 records. I'd call that fast enough, wouldn't you ? Or should I use DMax, do you think ? How fast do you reckon that will be ?
As for normalisation rules, what would you do if you require an autonumber scenario where each salesperson's invoices start with 1, and each person has their own ranges ? You obviously haven't worked with financial information before. If you are using this system for invoice numbers, for example, you could store the invoice prefix against the account for which the invoice relates to. If the account number changes, a calculated invoice number on previous invoices would then be WRONG. And if you store the invoice prefix in the invoice record, then you ought to use a single field to do it, to make querying simpler.
Furthermore, not every instance when you need an text-numeric number of this nature is a case where fields are being combined.
I've called it an autonumber because that is the term that many people will search for when they are looking for a solution. But if you want to quibble over semantics, I can change it to AutoText or whatever will make you happy.
But perhaps you can write a better solution instead of finding reasons to criticise ? Go ahead and post one, I'm not stopping you.
As for normalisation rules, what would you do if you require an autonumber scenario where each salesperson's invoices start with 1, and each person has their own ranges ? You obviously haven't worked with financial information before. If you are using this system for invoice numbers, for example, you could store the invoice prefix against the account for which the invoice relates to. If the account number changes, a calculated invoice number on previous invoices would then be WRONG. And if you store the invoice prefix in the invoice record, then you ought to use a single field to do it, to make querying simpler.
Furthermore, not every instance when you need an text-numeric number of this nature is a case where fields are being combined.
I've called it an autonumber because that is the term that many people will search for when they are looking for a solution. But if you want to quibble over semantics, I can change it to AutoText or whatever will make you happy.
But perhaps you can write a better solution instead of finding reasons to criticise ? Go ahead and post one, I'm not stopping you.
ASKER
Jim, as I pointed out at the top, I would not recommend this field to be used as a primary key.
I do come from the surrogate key viewpoint, but for example, you may need to store an account number and a use a seperate surrogate primary key.
There are instances where normalisation rules do need to be broken and this *can* be one of them (but not always) - say for example, when you store the price of a product sold against the invoice record, rather than looking it up in a products table (not related as such, just an example of where you would break what appears to be a normalising situation).
As for the question/answer type posting, please see COBOLdinosaur's comments here:
https://www.experts-exchange.com/questions/20521750/Suggestion-FAQs-HOWTOs.html
https://www.experts-exchange.com/questions/20525134/HOWTO-questions-please-close.html
I do come from the surrogate key viewpoint, but for example, you may need to store an account number and a use a seperate surrogate primary key.
There are instances where normalisation rules do need to be broken and this *can* be one of them (but not always) - say for example, when you store the price of a product sold against the invoice record, rather than looking it up in a products table (not related as such, just an example of where you would break what appears to be a normalising situation).
As for the question/answer type posting, please see COBOLdinosaur's comments here:
https://www.experts-exchange.com/questions/20521750/Suggestion-FAQs-HOWTOs.html
https://www.experts-exchange.com/questions/20525134/HOWTO-questions-please-close.html
<<Jim, as I pointed out at the top, I would not recommend this field to be used as a primary key.>>
Yes you did, but you did not explain why the method itself is a bad idea.
<<I do come from the surrogate key viewpoint, but for example, you may need to store an account number and a use a seperate surrogate primary key.>>
But why bother with the acocunt number? For the user? If so then they should generate their own number or simply use the autonumber for the account number. The point is an automatic method will generally fail at some point.
<<There are instances where normalisation rules do need to be broken and this *can* be one of them (but not always) - say for example, when you store the price of a product sold against the invoice record, rather than looking it up in a products table (not related as such, just an example of where you would break what appears to be a normalising situation).>>
I would not say your breaking a normalising situation as the order details table is clearly historical in nature. The entity is a order line item at a specific point in time.
The only time normalization is broken is for performance reasons. Outside of that, if your breaking the rules, your in trouble.
<<As for the question/answer type posting, please see COBOLdinosaur's comments here:
https://www.experts-exchange.com/questions/20521750/Suggestion-FAQs-HOWTOs.html
https://www.experts-exchange.com/questions/20525134/HOWTO-questions-please-close.html >>
I've reviewed both threads and posted comments on the first.
Some notes on the post to Nico:
"It's as fast as you're going to get, and considerably faster than using DMax. The speed is perfectly acceptable, and there is no noticeable delay on a table with 10000 records. I'd call that fast enough, wouldn't you ? Or should I use DMax, do you think ? How fast do you reckon that will be ?"
It's a myth that the domain functions are *always* slower then other methods. This was proven in a article written up in Smart Access about 4-5 years ago. Testing showed several cases where a domain function could beat out other methods. The suggestion at the end of the article was to test each of the various methods to find which was fastest for the situation.
<<But perhaps you can write a better solution instead of finding reasons to criticise ? Go ahead and post one, I'm not stopping you. >>
Short answer: no need to because you should not be doing something like this in a database design anyway.
Jim.
Yes you did, but you did not explain why the method itself is a bad idea.
<<I do come from the surrogate key viewpoint, but for example, you may need to store an account number and a use a seperate surrogate primary key.>>
But why bother with the acocunt number? For the user? If so then they should generate their own number or simply use the autonumber for the account number. The point is an automatic method will generally fail at some point.
<<There are instances where normalisation rules do need to be broken and this *can* be one of them (but not always) - say for example, when you store the price of a product sold against the invoice record, rather than looking it up in a products table (not related as such, just an example of where you would break what appears to be a normalising situation).>>
I would not say your breaking a normalising situation as the order details table is clearly historical in nature. The entity is a order line item at a specific point in time.
The only time normalization is broken is for performance reasons. Outside of that, if your breaking the rules, your in trouble.
<<As for the question/answer type posting, please see COBOLdinosaur's comments here:
https://www.experts-exchange.com/questions/20521750/Suggestion-FAQs-HOWTOs.html
https://www.experts-exchange.com/questions/20525134/HOWTO-questions-please-close.html >>
I've reviewed both threads and posted comments on the first.
Some notes on the post to Nico:
"It's as fast as you're going to get, and considerably faster than using DMax. The speed is perfectly acceptable, and there is no noticeable delay on a table with 10000 records. I'd call that fast enough, wouldn't you ? Or should I use DMax, do you think ? How fast do you reckon that will be ?"
It's a myth that the domain functions are *always* slower then other methods. This was proven in a article written up in Smart Access about 4-5 years ago. Testing showed several cases where a domain function could beat out other methods. The suggestion at the end of the article was to test each of the various methods to find which was fastest for the situation.
<<But perhaps you can write a better solution instead of finding reasons to criticise ? Go ahead and post one, I'm not stopping you. >>
Short answer: no need to because you should not be doing something like this in a database design anyway.
Jim.
Hmm overlooked an assumption you did in:
Set rst = CurrentDb.OpenRecordset("S
This will only work when all strPrefix strings are unique.
When used with:
BS
BSA
BSB
This will fail...
Nic;o)
Set rst = CurrentDb.OpenRecordset("S
This will only work when all strPrefix strings are unique.
When used with:
BS
BSA
BSB
This will fail...
Nic;o)
ASKER
Jim,
> But why bother with the acocunt number? For the user? If so then they should generate their own number or simply use the autonumber for the account number. The point is an automatic method will generally fail at some point.
There are many situations I've encountered where users want an auto generated account number. One recently was where an edict was issued by the financial department that all account numbers should follow a similar-ish scheme. They had to use the first 3 letters of the customer's surname, followed by an incremental 4 digit number. The number needed to start from 1 for each distinct prefix. While I could ask the user to generate this themselves, it would involve another step for them in looking up the existing accounts starting with that prefix and adding the right one (and god forbid any typing errors!). Thus, it was necessary to provide an automated way of doing this.
I would say that my example of invoice numbers constructed from account numbers is also historic data and needs to be stored. There are a few instances where clients have used this schema.
I agree, it is a myth that domain aggregate functions are always slower, but in this instance, I think that using a max recordset is probably faster although I've not run any timing tests. As I said, there was no noticeable delay on 10K records so I have no idea why Nico reckons it's slow. There is obviously the overhead of opening a recordset, and it may slow down on very large data sets.
I still hold that there are instances where this kind of schema is necessary (almost always for the benefit of humans rather than the application) but I will edit in additional comments at the start as to why it should not normally be done.
Nico, thank you for pointing out that instance where it will fail. I will add that in as a caveat.
> But why bother with the acocunt number? For the user? If so then they should generate their own number or simply use the autonumber for the account number. The point is an automatic method will generally fail at some point.
There are many situations I've encountered where users want an auto generated account number. One recently was where an edict was issued by the financial department that all account numbers should follow a similar-ish scheme. They had to use the first 3 letters of the customer's surname, followed by an incremental 4 digit number. The number needed to start from 1 for each distinct prefix. While I could ask the user to generate this themselves, it would involve another step for them in looking up the existing accounts starting with that prefix and adding the right one (and god forbid any typing errors!). Thus, it was necessary to provide an automated way of doing this.
I would say that my example of invoice numbers constructed from account numbers is also historic data and needs to be stored. There are a few instances where clients have used this schema.
I agree, it is a myth that domain aggregate functions are always slower, but in this instance, I think that using a max recordset is probably faster although I've not run any timing tests. As I said, there was no noticeable delay on 10K records so I have no idea why Nico reckons it's slow. There is obviously the overhead of opening a recordset, and it may slow down on very large data sets.
I still hold that there are instances where this kind of schema is necessary (almost always for the benefit of humans rather than the application) but I will edit in additional comments at the start as to why it should not normally be done.
Nico, thank you for pointing out that instance where it will fail. I will add that in as a caveat.
To move forward with these FAQ's, should we hold a discussion here or somewhere else?
I think the idea is great (I hate answering the simple ones over and over as well), but we need to come to an agreement on how the whole thing is handled.
For example, can we appoint one or two folks to act as a clearning house? In other words, they keep track of who is working on what, which topics are already covered, topics covered in detail by other sites (like Microsoft), etc.
I believe the discussion should be limited to the active experts, as were the ones that will need to maintain this.
Jim.
I think the idea is great (I hate answering the simple ones over and over as well), but we need to come to an agreement on how the whole thing is handled.
For example, can we appoint one or two folks to act as a clearning house? In other words, they keep track of who is working on what, which topics are already covered, topics covered in detail by other sites (like Microsoft), etc.
I believe the discussion should be limited to the active experts, as were the ones that will need to maintain this.
Jim.
Oh and one other thing (and this is always something I'm chiding Nico about 'cause he never mentions it!). This:
Set rst = CurrentDb.OpenRecordset("S
Will can fail in a multi-user situation if two users add a record at the same time.
The recordset needs to be locked.
Jim.
Set rst = CurrentDb.OpenRecordset("S
Will can fail in a multi-user situation if two users add a record at the same time.
The recordset needs to be locked.
Jim.
ASKER
Yes, true - I really should get into the habit of using read-only recordsets in this instance (as I've found out when using ADO!).
I posted a thread for discussions:
https://www.experts-exchange.com/questions/20525288/Open-invitation-to-all-Access-Experts.html
I don't know how many people really read the CS threads apart from the top level forum, so I thought it might be best to post it here. Do you know how to get EEAdmin and so on involved ?
"Will can fail" ? I know it's an important point which is why I think you mentioned it twice :-)
I posted a thread for discussions:
https://www.experts-exchange.com/questions/20525288/Open-invitation-to-all-Access-Experts.html
I don't know how many people really read the CS threads apart from the top level forum, so I thought it might be best to post it here. Do you know how to get EEAdmin and so on involved ?
"Will can fail" ? I know it's an important point which is why I think you mentioned it twice :-)
ASKER
As a point, how many of the top 15 are active ? I've seen Nico obviously, yourself, mgrattan and Volibrawl and I think TextReport is here now too, but I haven't seen the others.
I think I saw perove post when I first started a few weeks ago but not since then.
I think I saw perove post when I first started a few weeks ago but not since then.
I just trust you Jim as my multiuser and network back up :-)
Like shane I'm more in "single/standalone" Access databases.
The clients I normally work for are using "heavy" systems like IBM CICS, three tier C/S or environments build with Oracle and Oracle Forms. On my previous job I got an opportunity to work with Access and MS SQL, but only for data extraction for reporting.
Multi user Access systems are for me "Jim's territory".
Shane, mentioned "slow" as you made a point out of the speed in a previous autonumber Q.
As your recordset query is constructed by code, it needs to be interpreted first. The alternative of having the prefix as a separate field and a DMAX with a WHERE clause will thus probably match in speed and will make the creation of this function obsolete.
Personally I don't make such a point of speed in these situations as it's not likely a user will experience any speed difference between a DMAX or this function just to find one new number.
When speeds becomes an issue, it's always possible to switch to a table approach to keep track of the last used number.
Nic;o)
Like shane I'm more in "single/standalone" Access databases.
The clients I normally work for are using "heavy" systems like IBM CICS, three tier C/S or environments build with Oracle and Oracle Forms. On my previous job I got an opportunity to work with Access and MS SQL, but only for data extraction for reporting.
Multi user Access systems are for me "Jim's territory".
Shane, mentioned "slow" as you made a point out of the speed in a previous autonumber Q.
As your recordset query is constructed by code, it needs to be interpreted first. The alternative of having the prefix as a separate field and a DMAX with a WHERE clause will thus probably match in speed and will make the creation of this function obsolete.
Personally I don't make such a point of speed in these situations as it's not likely a user will experience any speed difference between a DMAX or this function just to find one new number.
When speeds becomes an issue, it's always possible to switch to a table approach to keep track of the last used number.
Nic;o)
ASKER
I do make a point about speed because I have several multi-user systems where querying on (largely static) high volume datasets is important and in general instances, domain aggregate functions perform slowly as opposed to recordset lookups, particularly across a network.
As far as constructing the query in code, it would be possible to do it via a saved querydef and set the parameters but for ease of understanding, it seemed a better point to use a code construct. I don't agree that using a DMax is comparable, especially in a network situation.
I work mostly with multiuser databases. In fact, I have only produced a single commercial single-user database in the last 5 years, and the issues are familiar. The number of users I encounter, however, is small - from 5-20 users at most. Beyond that, the issues are unfamiliar to me.
Most of my clients need to interface largely with AS400 systems - I have not yet encountered Oracle, so that is outside my knowledge although I have an eval version of 8i sitting around somewhere.
Speed is always an issue, as is returning the minimum amount of data needed. If anything can be done in SQL, I believe that it should since it will make upsizing considerably easier - converting to passthrough queries is simpler with the right SQL than having to weed out domain aggregate functions.
As far as constructing the query in code, it would be possible to do it via a saved querydef and set the parameters but for ease of understanding, it seemed a better point to use a code construct. I don't agree that using a DMax is comparable, especially in a network situation.
I work mostly with multiuser databases. In fact, I have only produced a single commercial single-user database in the last 5 years, and the issues are familiar. The number of users I encounter, however, is small - from 5-20 users at most. Beyond that, the issues are unfamiliar to me.
Most of my clients need to interface largely with AS400 systems - I have not yet encountered Oracle, so that is outside my knowledge although I have an eval version of 8i sitting around somewhere.
Speed is always an issue, as is returning the minimum amount of data needed. If anything can be done in SQL, I believe that it should since it will make upsizing considerably easier - converting to passthrough queries is simpler with the right SQL than having to weed out domain aggregate functions.
<<"Will can fail" ? I know it's an important point which is why I think you mentioned it twice :-) >>
I edited my response in mid-stream and left a word in. The way it was worded first time, it was open to question whether it would fail or not.
I can assure you, through first hand experience<g>, it *will* fail sooner or later.
This really bears out my point about the FAQ's needing to be checked. It's simply amazing how much stuff my wife catches of mine, and that's simple spelling and grammer. On top of that, there is all the technical issues to consider.
I'm kinda flip flopping back and forth on the whole FAQ idea. I do like it becase it would save some time when answering questions, but I also understand the amount of work it takes to get them right and keep them updated.
I used to write a 2 page article for the Basic2 Journal on a bi-monthly basis. I learned a lot from that. The simpler articles were the mostly widely accepted as they pertained to all (i.e. the use of UPS's). But the minute I tried to go more specific (i.e. use of in memory heaps or something like that), I'd loose a lot because it didn't pertain to all (this was feedback from the publisher and editor). So that's one of the concerns with the FAQ's. You have to look at how often we really answer the simple ones. CD also had a point: the allure of EE is that someone is answering your specific question. There are lots of sites out there already (darn good ones too) that exist with the Q/A format.
The other issue is time. It took me an average of 3-4 days to produce one of those articles and make sure it was technically correct in every respsect. Your putting your reputation on the line when you post something like that. Make a mistake and at the very least you'll look foolish. Make a major gaff and your in trouble. Believe me, I learned the hard way on that one!<g>
So there are a lot of isses to consider with the FAQ's. Give everything I've said some thought and bounce back with comments.
Jim.
I edited my response in mid-stream and left a word in. The way it was worded first time, it was open to question whether it would fail or not.
I can assure you, through first hand experience<g>, it *will* fail sooner or later.
This really bears out my point about the FAQ's needing to be checked. It's simply amazing how much stuff my wife catches of mine, and that's simple spelling and grammer. On top of that, there is all the technical issues to consider.
I'm kinda flip flopping back and forth on the whole FAQ idea. I do like it becase it would save some time when answering questions, but I also understand the amount of work it takes to get them right and keep them updated.
I used to write a 2 page article for the Basic2 Journal on a bi-monthly basis. I learned a lot from that. The simpler articles were the mostly widely accepted as they pertained to all (i.e. the use of UPS's). But the minute I tried to go more specific (i.e. use of in memory heaps or something like that), I'd loose a lot because it didn't pertain to all (this was feedback from the publisher and editor). So that's one of the concerns with the FAQ's. You have to look at how often we really answer the simple ones. CD also had a point: the allure of EE is that someone is answering your specific question. There are lots of sites out there already (darn good ones too) that exist with the Q/A format.
The other issue is time. It took me an average of 3-4 days to produce one of those articles and make sure it was technically correct in every respsect. Your putting your reputation on the line when you post something like that. Make a mistake and at the very least you'll look foolish. Make a major gaff and your in trouble. Believe me, I learned the hard way on that one!<g>
So there are a lot of isses to consider with the FAQ's. Give everything I've said some thought and bounce back with comments.
Jim.
ASKER
> I edited my response in mid-stream and left a word in. The way it was worded first time, it was open to question whether it would fail or not.
Yes, I know it will (I had something similar fail on a client demonstration about a week ago which wasn't hugely impressive although we did get away with it - just!).
I agree that it does need to be checked. I made a simple gaff with my first one and that was putting Chr(37) instead of Chr(34). I wholeheartedly support the idea that they should be a collaborative effort, and in the instances where the people involved don't agree, perhaps more than one approach should be presented. with the pros and cons, and let the reader decide.
I suppose we could start with the very basic ones - you know, the sort where the answer is to include a reference to DAO. I know the issue is covered elsewhere but for a lot of people, this site is their first port of call (people being lazy) and it might help just to have the info included somewhere so they don't have to go externally.
I don't believe that a FAQ should be seen as a replacement to the interaction, more as a supplement so we don't have to rehash the basics over and over again. Like I said in the CS thread, it can be very tiresome and draining to try and talk someone through creating things like calculated fields or event procedures, or even simple things like referencing a control correctly.
With a collaborative effort, time is less of an issue since more people will be contributing. I think it needs to be collaborative because people have skills in different areas. While I could guide someone through Word OLE automation for example, I don't use it often enough to feel comfortable writing a FAQ about it, and if I did, I would definitely need someone to check it out.
Yes, I know it will (I had something similar fail on a client demonstration about a week ago which wasn't hugely impressive although we did get away with it - just!).
I agree that it does need to be checked. I made a simple gaff with my first one and that was putting Chr(37) instead of Chr(34). I wholeheartedly support the idea that they should be a collaborative effort, and in the instances where the people involved don't agree, perhaps more than one approach should be presented. with the pros and cons, and let the reader decide.
I suppose we could start with the very basic ones - you know, the sort where the answer is to include a reference to DAO. I know the issue is covered elsewhere but for a lot of people, this site is their first port of call (people being lazy) and it might help just to have the info included somewhere so they don't have to go externally.
I don't believe that a FAQ should be seen as a replacement to the interaction, more as a supplement so we don't have to rehash the basics over and over again. Like I said in the CS thread, it can be very tiresome and draining to try and talk someone through creating things like calculated fields or event procedures, or even simple things like referencing a control correctly.
With a collaborative effort, time is less of an issue since more people will be contributing. I think it needs to be collaborative because people have skills in different areas. While I could guide someone through Word OLE automation for example, I don't use it often enough to feel comfortable writing a FAQ about it, and if I did, I would definitely need someone to check it out.
ASKER
Jim, should we put this in another thread ? I think it'll probably attract more attention and hopefully get the others involved in the discussion as well.
Might look better coming from you rather than me :-)
Might look better coming from you rather than me :-)
<<Might look better coming from you rather than me :-) >>
Another thread sure, but you start it off; it's your idea not mine.
Jim.
Another thread sure, but you start it off; it's your idea not mine.
Jim.
Listening
Computer101
E-E Admin
Computer101
E-E Admin
ASKER
C101, I'm going to open another topic in the Access TA if that's ok with you, once I've uncrossed my eyes and done something about this cold I seem to have caught from reading Wes' posts :)
I'd like to get more people involved in the discussion and get more POVs - the title of this Q doesn't really make it appropriate.
I'd like to get more people involved in the discussion and get more POVs - the title of this Q doesn't really make it appropriate.
Cool
C101
C101
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With Access XP (2002) it is very simple to use an auto number in this format:
ifs-003-001
ifs-003-002
ifs-003-003
ifs-003-004
etc...
Simply by setting a field to the required name (e.g. ID), setting it's Data Type to AutoNumber and setting it's format to: "ifs-003-"0000
The format property can be found below the table fields, in the Field Property, under the General tab
ifs-003-001
ifs-003-002
ifs-003-003
ifs-003-004
etc...
Simply by setting a field to the required name (e.g. ID), setting it's Data Type to AutoNumber and setting it's format to: "ifs-003-"0000
The format property can be found below the table fields, in the Field Property, under the General tab
Basically it suffers from the bad idea to combine fields in one as it's against normalisation rules.
The "Meaning full codes" like this function produces always give trouble in the maintenance future.
Why calling it an autonumber as that's also field type in Access, not used here.
Nic;o)