Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Assigning Codes Automatically (2000)

    I am trying to create a form for my users who need to add Standing Orders (which are similar to blanket orders). I have it all together with the exception of one piece -- that is to assign Standing Order Codes to each record.

    Standing Order Code (SO_CODE) is an alphanumeric field where the first letter of the name of the Standing Order is followed by the next sequential number. For example:

    Harvard Semitic Studies has an SO_CODE of H12
    Historical Dictionary has an SO_CODE of H13
    etc.

    The Standing Order information is in one table called SO_DATA. The fields are: TITLE (text) VENDOR_ID (text) SO_CODE (text) LETTER (text) NUMBER
    (numeric) FUND_ID (text).

    The SO_CODE is the primary key. The LETTER and NUMBER fields are the SO_CODE broken up into their respective characters.

    My initial thought is to write some VB code to loop through the table looking at the first letter in the LETTER field based on the title of the
    standing order and then finding the last NUMBER that is associated with that letter and then assigning the Order that number. I have 2 text boxes
    on my form, one that uses a LEFT() function to get the first letter of the Standing Order and then a text box for the number. The text in these boxes will be
    concatenated together to create the Standing Order.

    That's my logic -- can anyone find either the fatal flaw or help me with
    the nuts and bolts of putting this together?

    Thanks!
    Kindra

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Assigning Codes Automatically (2000)

    There are some issues with the approach you suggest - finding the current maximum will work if your database is single user, but if multiple users ever try to add a Standing Order at the same time you can end up with problems. One alternative would be to create a field that is an autonumber field for your sequential numbers, a text field for the Letter portion of the SO_CODE, and simply concatenate the two when you display, query or whatever. Another approach would be to create a special one-record table that holds the next SO_CODE to be assigned and when a person adds a new record, get that value from the special table, increment it by 1, save it, and then assign that value to the new Standing Order record.

    An important point about your approach - I would not store the SO_CODE in both formats - if you do sooner or latter someone will edit one and not edit the other. Instead, either use string functions to extract the portion you want, or use two fields and concatenate the two when you want to display the full SO_CODE. Finally, you do not want to use fields names LETTER or NUMBER - those are too close to existing reserved words in Access, and may well cause you problems if you try to write VBA routines down the road.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning Codes Automatically (2000)

    <P ID="edit" class=small>(Edited by D Willett on 30-May-03 14:38. )</P>I've put together a very crude form for you to look at.
    I think it was what you were asking.
    It works from the AfterUpdate of the field "TITLE"
    Also look at the control source of the field with the code in.
    You can change the field names in that statement to suit your needs.
    ************************************************** **************************
    Sorry Wendell, we must have pressed the send button at the same time.
    My demonstration only shows how it can be done simply.
    As Wendell says, be careful of field names.
    ************************************************** ***************************
    ************************************************** *************************
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning Codes Automatically (2000)

    The biggest problem that I'm encountering is that we don't assign the SO_CODE sequentially according to the total number of records in the table, it's according to the alpahbetical sequence. For example:

    Time Magazine - T45
    Theological Resources - T46
    Abstract Art - A4
    Anthropological Times - A5

    etc. So if we get a new standing order that's called "Amazing Airplanes", I would need that to have a SO_CODE of A6. But it would have to look up the "A's" and then find the last largest number and add 1. I wish that we did assign them sequentially! That would be so much easier. But I inherited this system, so I have to make due with what I have [img]/forums/images/smilies/smile.gif[/img]

  5. #5
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning Codes Automatically (2000)

    I don't know whether this will be of help Kindra. We have to set references based on the first three letters of the surname, the first letter of the first name plus a 2 digit sequential number, e.g. the third J Smith would be SMIJ03.

    The following function takes first and last names and creates a reference number. Because there could be a problem with rude words, e.g. Peter Craddock, Thomas Shindler, there is a routine to check whether a reference is satisfactory and to substitute a Q if not. The function then loops through the table to find the last reference for that combination of letters and adds a sequential number.

    I'm sure you can modify the concept to work for you.

    Public Function RefNumber(LastName As String, FirstName As String) As String
    Dim dbs As Database
    Dim rstCount As Recordset
    Dim strSQL As String
    Dim intCount As Integer
    Dim strCount As String
    Dim RefName As String

    Set dbs = CurrentDb
    intCount = 0
    RefName = UCase(Left$(LastName, 3) & Left$(FirstName, 1))

    'Check whether reference is rude
    gintResponse = MsgBox("The allocated reference number for" & vbCrLf & FirstName & " " & LastName & " is " & RefName & vbCrLf & vbCrLf & "Is this suitable for a reference?", vbYesNo + vbQuestion, "M-Residents")
    If gintResponse = vbNo Then
    RefName = Left$(RefName, 3) & "Q"
    End If

    'Set up search string
    strSQL = "SELECT Residents.ResID, Left$([ResRefNo],4) AS RefNamePart FROM Residents WHERE ((Len([ResRefNo])>1));"

    'Check for number of records
    Set rstCount = dbs.OpenRecordset(strSQL)
    rstCount.MoveFirst
    While Not rstCount.EOF
    If rstCount![RefNamePart] = RefName Then
    intCount = intCount + 1
    End If
    rstCount.MoveNext
    Wend
    rstCount.Close

    'Increment intCount to add the next number
    intCount = intCount + 1

    'Add number to RefName
    strCount = Format(intCount)
    If Len(strCount) = 1 Then
    strCount = "0" & strCount
    End If
    RefNumber = RefName & strCount

    End Function

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Assigning Codes Automatically (2000)

    Ah - another words the number is not unique. Then what you need is a table with a record for each of the PrefixLetters, and the NextNumber to be assigned for that prefix. When you add a new SO, the user must specify which letter code is to be used, at which point you run code which opens a recordset that is the record for the specificed letter, increment it by one, save the record, and then assigned the original number as the remainder of the SO_CODE. You can do it with either DAO or ADO. DollyP's code is much along the lines you would need - but in her case with a multi-character prefix the risk of ending up with a duplicate is much lower, and it would be a real pain to create a table with that many entries anyhow. In your case it's only 26 records. Hope this helps.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning Codes Automatically (2000)

    What if I'm a bit scared of DAO/ADO? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I created a query that will use the [txtLetter] field (contains the first letter of the [txtTitle] field) and pulls only those records that are equal. This gives me a list of the Letters and Numbers separately. Can I use THAT query in order to do this somehow?

    This might be beyond my knowledge of Access -- if so I can live with that! Thanks for all of your help here <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Assigning Codes Automatically (2000)

    You can do that, as long as two people don't try to put in a new record at the same time with the same prefix letter. But you still have to figure out how to get that value and put the next number into the new record. Actually the code that DollyP posted is DAO code and with slight modifications should work for you. Unfortunately, I've got to take a driving trip for several hours, but if you can wait until tomorrow, I can create a small routine that will do the trick. Let me know if you would like to to do that.
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Assigning Codes Automatically (2000)

    If you don't want to use ADO or DAO, you can use Dave Willett's idea with a small modification. You already created a query that returns only those records that have the same first letter as the Title. Us can use the DMax function like Dave does, but based on this query instead of on the table. That will return the maximum number used for that letter. Add 1, and you have your new number.

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning Codes Automatically (2000)

    I would love that Wendell. Thanks so much! I won't be back in the office until Monday, so there's no rush.

    There will most likely only be one, or two at the most, people updating these codes anyhow, so multiple user problems shouldn't be too much of an issue...

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Assigning Codes Automatically (2000)

    Hi Kindra,
    Hope you had a great weekend - ours turned rainy so I'm catching up on inside things. The code I mentioned looks like this:<font face="Georgia">

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim myDB As DAO.Database
    Dim myRec As DAO.Recordset
    </font face=georgia>
    <font color=448800>' This code runs just before the new record is saved.
    ' It could be run on the AfterUpdate event for the combo box.
    ' However if you do that you will lose numbers where someone starts to enter a record and then doesn't

    ' This is typical DAO code for this kind of thing - you could also do it with ADO code that would look a bit different
    </font color=448800><font face="Georgia">
    Set myDB = CurrentDb() </font face=georgia><font color=448800>'Specify the database DAO is going to work with</font color=448800><font face="Georgia">
    Set myRec = myDB.OpenRecordset("SELECT * FROM tblNextNumberLookup WHERE (txtLetterCode = '" & Me!txtLetterPrefix & "');")</font face=georgia><font color=448800>
    ' The above line uses an embedded SQL string, but you could do it with a saved query that used the combo box as it's criteria
    '(Me!txtLetterPrefix is the combo box where you select the letter prefix)</font color=448800><font face="Georgia">
    Me!intNumberSuffix = myRec!intNextNumber </font face=georgia><font color=448800>'Set the numeric suffix for this record</font color=448800><font face="Georgia">
    myRec.Edit </font face=georgia><font color=448800>'Begin the update of the current lookup record</font color=448800><font face="Georgia">
    myRec!intNextNumber = myRec!intNextNumber + 1 </font face=georgia><font color=448800>'Increment to the next number</font color=448800><font face="Georgia">
    myRec.Update </font face=georgia><font color=448800>'Save the record

    ' This is cleanup code so you don't have memory leaks</font color=448800><font face="Georgia">
    myRec.Close
    Set myRec = Nothing
    myDB.Close
    Set myDB = Nothing

    End Sub</font face=georgia>

    I built a small sample database that I've attached so you can see how it interacts with the form and how the code works. If you want to play with it, set a breakpoint on the first line of code and step through it a line at a time when you add a record and you'll see how it works. I didn't bother with issues such as using the same form to edit records after the fact - if you do that you may want to put code in to determine if a new record is being added or an existing one is being edited. (One way would be to check and see if the NumberSuffix is Null.) Post back with any questions - in your situation the other suggested solutions would probably work just fine too.
    Attached Files Attached Files
    Wendell

  12. #12
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning Codes Automatically (2000)

    Unfortunately, when it gets to "Dim myDB As DAO.Database" it bombs out on me -- do I need to do something to "activate" DAO in Access?

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Assigning Codes Automatically (2000)

    Yes - you do need to set a reference to DAO - do that by opening the code in the VBA editor, and then do Tools / References and you should see Microsoft DAO 3.6 or something like that, and click the check box.
    Wendell

  14. #14
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Assigning Codes Automatically (2000)

    At the risk of sounding *too* needy -- how do I concantenate 2 text boxes together in order to form the SO_CODE? I've tried separating the letters/numbers into their own variables (CODE_LETTER/CODE_NUMBER) and then trying to "add" them together, i.e., "CODE_LETTER+CODE_NUMBER" it gives me an error. Wendell, how would you have gone about concantenating the two fields in order to get the SO_CODE?

    I'm so almost there! [img]/forums/images/smilies/smile.gif[/img]

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Assigning Codes Automatically (2000)

    The basics are fairly simple - you simply use the ampersand (&) symbol to indicate concatenation. The result will be a text string. You can do that in a text box, or you can do it in a query behind the form. In either event the expression looks something like:<font face="Georgia"><font color=blue>
    FullCode = CodeLetter & CodeNumber
    </font color=blue></font face=georgia>
    If you want the number to have a certain number of leading zeros, you could use the Format() function to do that, e.g.<font face="Georgia"><font color=blue>
    FullCode = CodeLetter & Format([CodeNumber],"00000")
    </font color=blue></font face=georgia>
    You could resort to storing the number in a text string, but that often causes things to sort in funny ways. Hope this answers your question - I didn't attempt to do it on the combo box, as what I envisioned was a form that was used strictly for input of new records. In general you don't want people changing the code once it has been assigned (though you may need that capability for maintenance purposes) on a form used to edit existing data. BTW, I meant to tell you in my previous post - the controls with an ugly <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Yellow</span hi> background are intended to be hidden so the user doesn't see them - I left them visible in the sample so you could see what was going on. Hope this clears things up a bit.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •