Results 1 to 7 of 7

Thread: records

  1. #1
    Join Date
    Jan 2010
    Posts
    32

    Unanswered: records

    Is it possible to have a message or a flag of somesort pop up when a specific numbered record is entered? Say I want something to happen after record # 10 is entered.

    thanks

  2. #2
    Join Date
    May 2009
    Posts
    258
    Direct entry into a table, no; in a form, yes. It'd probably be something like this:
    Code:
    Private Sub Form_AfterInsert()
    	If Me.ID=10 Then
    		' run code
    	End If
    End Sub
    Ax

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This
    Private Sub Form_AfterInsert()
    If Me.ID=10 Then
    ' run code
    End If
    End Sub

    Will look at the ID field (or whatever your autonumber field value is) and won't take into account deleted records but will return the last autonumber value.

    If you want to count the exact # of records in the table, you could create a function in a module as such:

    Function NumOfRecords() as variant
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myTableName"
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    NumOfRecords = 0
    else
    NumOfRecords = rs.recordcount
    end if
    rs.close
    set rs = nothing
    End Funtion

    Then you'd call the function in your form coding as such:

    If NumOfRecords() >= 10 then
    msgbox "recordcount is equal to or greater than 10"
    end if

    or

    msgbox "# of Records = " & NumOfRecords()
    Last edited by pkstormy; 02-08-10 at 20:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2010
    Posts
    32
    Thanks PK...

    When you say "call the function in your form"...is this in the afterupdate?

  5. #5
    Join Date
    May 2009
    Posts
    258
    So based on pk's advice, you don't want to base it on a numbered record, but on the number of records in the table? That is different than what was stated, I apologize for the misunderstanding. Yes, you'd add that to the AfterUpdate.

    Ax

  6. #6
    Join Date
    Jan 2010
    Posts
    32
    Thanks Ax...

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If calling the function in an AfterUpdate of a field, you may want to add a Refresh command before calling the function (to make sure the newly added current record is refreshed/updated.)

    ex:
    Private Sub FirstName_AfterUpdate()
    Refresh
    msgbox "# of Records = " & NumOfRecords()
    End Sub

    Depending on your tab order of the fields on the form, the current (newly added) record may/may not be updated if calling it on a specific AfterUpdate event of a field and if not, wouldn't be counted when the function is called. Watch when your autonumber field value is actually generated/created on the form. If it doesn't have a new number generated for the new record being entered, the record isn't saved yet into the table and wouldn't be counted when calling the function.

    Thanks for the clarification Ax.
    Last edited by pkstormy; 02-09-10 at 08:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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