Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    17

    Angry Unanswered: Autonumber problem

    I am trying to get a datasheet Subform to automatically increment the value in a field "RecordID" by 1 for each new record that is input. I want the sequence to start at 1 each time, so Autonumber is no good for this. I have tried using DMax with no luck, I found the code below on this forum, but it gives me an error with the ADODB.Connection bit stating that the "User-defined type not defined" whatever that means? Any suggestions?
    or if anyone nows the best way to use dmax ie which event to assign the cosde to? OR is there some other way of doing what I want?

    Public Function GetNextAutoNumber()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set cn = CurrentProject.Connection
    rs.Open "SELECT Max(MyID) AS MaxValue FROM tblTest WHERE MyID Is Not Null;", cn, adOpenStatic, adLockReadOnly
    If Not rs.EOF Then
    If Trim(rs.Fields("MaxValue").Value) <> "" Then
    GetNextAutoNumber = rs.Fields("MaxValue") + 1
    Else
    GetNextAutoNumber = 1
    End If
    End If
    rs.close
    cn.close
    Set rs = Nothing
    Set cn = Nothing

    End Function

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I do not see why DMax does not work, as I have used it on a number of occations in the Form_BerforeUpdate event

    This is an example (with data validation).

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If IsNull(EnquiryName) Then
        MsgBox "A unique enquiry name for must be entered.", vbInformation, msgTitle
        Cancel = True
        EnquiryName.SetFocus
        Exit Sub
    ElseIf [RegionID] = 1 Then
        MsgBox "A Region must be selected.", vbInformation, msgTitle
        Cancel = True
        RegionID.SetFocus
        Exit Sub
    ElseIf IsNull(TenderDate) Then
        MsgBox "A tender date must be entered.", vbInformation, msgTitle
        Cancel = True
        TenderDate.SetFocus
        Exit Sub
    End If
    
    EnquiryRef.Visible = False
    
    If Me.NewRecord Then [EnquiryID] = DMax("EnquiryID", "A_Enquiries") + 1
    
    [LastUpdated] = Date  'Assign last update date
    
    End Sub
    Hope this helps


    MTB

  3. #3
    Join Date
    Sep 2004
    Posts
    17

    Red face

    Thanks Mike, i'll try this, I think I was using the wrong event for the code

    Ade

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by adrian123
    I am trying to get a datasheet Subform to automatically increment the value in a field "RecordID" by 1 for each new record that is input.
    Public Function GetNextAutoNumber()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set cn = CurrentProject.Connection
    rs.Open "SELECT Max(MyID) AS MaxValue FROM tblTest WHERE MyID Is Not Null;", cn, adOpenStatic, adLockReadOnly
    If Not rs.EOF Then
    If Trim(rs.Fields("MaxValue").Value) <> "" Then
    GetNextAutoNumber = rs.Fields("MaxValue") + 1
    Else
    GetNextAutoNumber = 1
    End If
    End If
    rs.close
    cn.close
    Set rs = Nothing
    Set cn = Nothing

    End Function
    Hi adrian123,
    You said you were trying to get the field RecordID to autoincrement. But I don't see that anywhere in your code. Shouldn't you reference the field RecordID in there somewhere to increment?

    just asking,
    Bud

Posting Permissions

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