SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Aug 2000
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Auto Increment Access HELP!

    I need to create an auto increment field in Access but
    it has to be in the format Y000000. WHen a record is
    added the field would become Y000001 and then Y000002,
    etc. Any ideas or help with this would be greatly
    appreciated!!!

    Thank you

  2. #2
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    would this be used with an asp? cos im not sure u can do that, but with a small function in asp, you could make it look like that when u display the info to count the number of numbers, then make up the rest with 0's and then add a Y infront of it, or u could write a function to insert it into the database as this, tho it wouldnt autoincrement, it would have to be set as a text field

    those are jus my ideas, if you want, i could write teh function if u supply the info

  3. #3
    SitePoint Addict
    Join Date
    Aug 2000
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A function that would insert into the database in that format would be ideal dhtmlgod. Is there a way to make the record unique and auto incrementing through ASP? I don't think it's possible to auto increment in that format within ACCESS because I tried for hours without success so ASP would be the key. HELP!

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    The easiest way I could think of to do this would be to set up your table with a primary key of a type and an autonumber. I mean like this(syntax could be wrong, doing it off top of my head):

    Code:
    CREATE TABLE tablename (indextype varchar(1), indexcounter id counter, field1 varchar(3), field2 varchar(4), PRIMARY KEY (indextype, indexcounter))
    Then all you would need to do is
    Code:
    INSERT INTO tablename (indextype, field1, field2) 
                         VALUES ('Y', 'abc', 'def')
    This will then give you Y0001, Y002, etc. What I DON'T think it will do is give you Y001, Y002, X001, X002, Z001, Z002. If you are looking for that then you will need to do something like this (you'll need the same table setup I did above):

    Code:
    strSQL = "SELECT Max(indexcounter) AS IndexCount " & _
                 "   FROM tablename " & _
                 " WHERE indextype = 'Y'"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.open strSql, my_Conn
    
    if rs.EOF or rs.BOF then
       IndexCount = 0
    else
       IndexCount = rs("IndexCount")
    end if
    rs.close
    set rs = nothing
    
    strSQL = "INSERT INTO tablename (indextype, indexcount, field1, field2)" & _
                "                       VALUES('Y', " & indexcount & ", 'abc', 'def')"
    my_conn.execute strSQL
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, guess someelse did it

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Ooops. I just noticed a mistake. In the insert statement, change & indexcount & to & indexcount + 1 & or you'll just try to insert the same value again...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePoint Addict
    Join Date
    Aug 2000
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am so lost in this it's PITIFUL

  8. #8
    SitePoint Addict
    Join Date
    Aug 2000
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't get the code posted by DaveMaxwell to work. I am using Ultradev and this is what I have so far:

    Code:
    <%@LANGUAGE="VBSCRIPT"%>
    <!--#include file="Connections/conn1.asp" -->
    <%
    
    set RSWarriorCode = Server.CreateObject("ADODB.Recordset")
    RSWarriorCode.ActiveConnection = MM_conn1_STRING
    RSWarriorCode.Source = "SELECT MAX(ID) FROM Mailing1"
    RSWarriorCode.CursorType = 0
    RSWarriorCode.CursorLocation = 2
    RSWarriorCode.LockType = 3
    RSWarriorCode.Open()
    RSWarriorCode_numRows = 0
    
    Warrior =(RSWarriorCode.Fields.Item("Expr1000").Value) + 1
    
    %>
    <%
    ' *** Edit Operations: declare variables
    
    MM_editAction = CStr(Request("URL"))
    If (Request.QueryString <> "") Then
      MM_editAction = MM_editAction & "?" & Request.QueryString
    End If
    
    ' boolean to abort record edit
    MM_abortEdit = false
    
    ' query string to execute
    MM_editQuery = ""
    %>
    <%
    ' *** Insert Record: set variables
    
    If (CStr(Request("MM_insert")) <> "") Then
    
      MM_editConnection = MM_conn1_STRING
      MM_editTable = "Mailing1"
      MM_editRedirectUrl = "thank.html"
      MM_fieldsStr  = "First_Name|value|Last_Name|value|company|value|Address|value|Unit|value|City|value|ST|value|Zip|value|Email|value|Phone|value|fax|value|emailmeYes|value|emailmeNo|value|jobtitle|value|industry|value|Travelfrequency|value|LenghtofStay|value|RoadWarriorCode|" & Warrior
      MM_columnsStr = "First_name|',none,''|Last_Name|',none,''|company|',none,''|Address|',none,''|Unit|',none,''|City|',none,''|ST|',none,''|Zip|none,none,NULL|Email|',none,''|Phone|',none,''|fax|',none,''|emailmeYes|none,1,0|emailmeNo|none,1,0|jobtitle|',none,''|industry|',none,''|Travelfrequency|',none,''|LenghtofStay|',none,''|RoadWarriorCode|" & Warrior & "," & Warrior & "," & Warrior
    
      ' create the MM_fields and MM_columns arrays
      MM_fields = Split(MM_fieldsStr, "|")
      MM_columns = Split(MM_columnsStr, "|")
      
      ' set the form values
      For i = LBound(MM_fields) To UBound(MM_fields) Step 2
        MM_fields(i+1) = CStr(Request.Form(MM_fields(i)))
      Next
    
      ' append the query string to the redirect URL
      If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
        If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
          MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
        Else
          MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
        End If
      End If
    
    End If
    %>
    <%
    ' *** Insert Record: construct a sql insert statement and execute it
    
    If (CStr(Request("MM_insert")) <> "") Then
    
      ' create the sql insert statement
      MM_tableValues = ""
      MM_dbValues = ""
      For i = LBound(MM_fields) To UBound(MM_fields) Step 2
        FormVal = MM_fields(i+1)
        MM_typeArray = Split(MM_columns(i+1),",")
        Delim = MM_typeArray(0)
        If (Delim = "none") Then Delim = ""
        AltVal = MM_typeArray(1)
        If (AltVal = "none") Then AltVal = ""
        EmptyVal = MM_typeArray(2)
        If (EmptyVal = "none") Then EmptyVal = ""
        If (FormVal = "") Then
          FormVal = EmptyVal
        Else
          If (AltVal <> "") Then
            FormVal = AltVal
          ElseIf (Delim = "'") Then  ' escape quotes
            FormVal = "'" & Replace(FormVal,"'","''") & "'"
          Else
            FormVal = Delim + FormVal + Delim
          End If
        End If
        If (i <> LBound(MM_fields)) Then
          MM_tableValues = MM_tableValues & ","
          MM_dbValues = MM_dbValues & ","
        End if
        MM_tableValues = MM_tableValues & MM_columns(i)
        MM_dbValues = MM_dbValues & FormVal
      Next
      MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"
    
      If (Not MM_abortEdit) Then
        ' execute the insert
        Set MM_editCmd = Server.CreateObject("ADODB.Command")
        MM_editCmd.ActiveConnection = MM_editConnection
        MM_editCmd.CommandText = MM_editQuery
        MM_editCmd.Execute
        MM_editCmd.ActiveConnection.Close
    
        If (MM_editRedirectUrl <> "") Then
          Response.Redirect(MM_editRedirectUrl)
        End If
      End If
    
    End If
    %>
    This gives me an insert on the field named RoadWarriorCode that is autoincrementing based on the value of the last primary field number inserted.
    My problem now is Adding the Y0000 in front of it so that it will insert like Y00001, Y00002, etc.

    I have tried adding this to the the following line:
    Code:
    'Original Line
    Warrior =(RSWarriorCode.Fields.Item("Expr1000").Value) + 1
    
    'Changed Line
    Warrior ="Y0000" & (RSWarriorCode.Fields.Item("Expr1000").Value) + 1
    But this gives me a type mismatch error.

    Anyone have any clues???? or hints?

  9. #9
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It might be that it's returning Null if the table's empty.

    It might be simpler to use just the one column (text field) as the ID field & primary key.

    The GetNextId function below will get the last id in the table and build and return the next id.

    You'll need to change the table, fields and connection string info in the GetLastId, which is called within the function GetNextId.


    Code:
    Function GetLastId()
    	
    	On Error Resume Next
    	Err.Clear
    	
    	Dim strReturn
    	Dim objRS, objConn, strSQL
    	
    	strSQL = "SELECT MAX(WarriorId) AS [max_id] FROM Warriors"
    	
    	Set objConn = Server.CreateObject("ADODB.Connection")
    	objConn.Open DB_CONN_STRING
    	
    	Set objRS = objConn.Execute(strSQL)
    	If	Not objRS.EOF Then
    		strReturn = Trim(objRS.Fields(0).Value)
    		If IsNull(strReturn) Then
    			strReturn = ""
    		End If
    	Else
    		strReturn = -1
    	End If
    	
    	objRS.Close
    	Set objRS = Nothing
    	objConn.Close
    	Set objConn = Nothing
    		
    	If Err.Number <> 0 Then
    		strReturn = -1
    	End If
    	
    	GetLastId = strReturn
    
    End Function
    
    Function GetNextId()
    	
    	Dim intLastIdNo
    	Dim intNextidNo
    	Dim strNextId
    	Dim strLastId
    	
    	Const ID_LENGTH = 6
    	Const ID_PREFIX = "Y"
    	
    	'Get the last id from db table
    	strLastId = GetLastId
    	
    	'If last id = -1 something went wrong
    	If strLastId = -1 Then
    		GetNextId = -1
    		Exit Function
    	End If
    	
    	
    	'Get the number part of the last id
    	If Len(CStr(strLastId)) = ID_LENGTH Then
    		intLastIdNo = Mid(CStr(strLastId), 2)
    		'If the number part is a number then increment it
    		'otherwise set it to 1
    		If IsNumeric(intLastIdNo) Then
    			intNextIdNo = CLng(intLastIdNo) + 1
    		Else
    			intNextIdNo = 1
    		End If
    	Else
    		intNextIdNo = 1
    	End If
    	
    	
    	
    	'Build up the next id with the prefix (Y) and the number part
    	'Pad the number part with zeros before appending to id
    	strNextId = ID_PREFIX & PadString(intNextIdNo, ID_LENGTH - 1, 0, 1)
    
    	GetNextId = strNextId
    
    End Function
    
    Function PadString(strText, intLength, strPadCharacter, intAlign)
    	
    	Dim strReturn
    	Dim strPadString
    	
    	If Len(CStr(strText)) >= intLength Then
    		strReturn = strText
    	Else
    		strPadString = String((intLength - Len(Cstr(strText))), CStr(strPadCharacter))
    		Select Case intAlign
    			Case 0: strReturn = strText & strPadString
    			Case 1: strReturn = strPadString & strText
    			Case Else:  strReturn = strText & strPadString
    		End Select
    	End If
    
    	PadString = strReturn	
    	
    End Function


Bookmarks

Posting Permissions

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