Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using RecordCount in a Module (Access2K, Win2KPro)

    I am trying to set up an event after the user has enter some basic information in order to check if the entered record might already exist. This should be simple but I'm not familiar with setting up RecordSet and implementing RecordCount in an Access module. Am looking around in the Help file but thought somebody in the Lounge might be able to give me a quick run-down on the general procedure.

    I *am* very familiar with opening a db, setting up SQL strings and getting recordsets in ASP but I just haven't done this sort of thing in Access. I suppose I could just build a series of queries and run them in code to return the count but it would be better in the long run to be able to do this sort of thing completely in code.

    Anybody able to give me a little advice on how to implement a Recordset in code based on a SQL string and, at a minimum, return a count to?

    TIA <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

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

    Re: Using RecordCount in a Module (Access2K, Win2KPro)

    You have several options. If you want to check whether a value or a combination of values already occurs, you can
    1. <LI>Create an SQL string and open a DAO recordset, then test if it has records.
      <LI>Create an SQL string and open an ADODB recordset, then test if it has records.
      <LI>Create a Where condition and use one of the domain functions such as DCount or DLookup.
    1. The first option requires that you set a reference to the Microsoft DAO 3.6 Object Library (Tools | References... in the Visual Basic Editor)

    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    strSQL = "SELECT ... FROM ... WHERE ..."
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    If rst.RecordCount = 0 Then
    MsgBox "No match"
    Else
    MsgBox "We have a match!"
    End If
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

    2. The second option requires that you have a reference to a Microsoft ActiveX Data Objects 2.n Library (such a reference is set by default in an Access 2000 database)

    Dim strSQL As String
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    strSQL = "SELECT ... FROM ... WHERE ..."
    Set cnn = CurrentProject.Connection
    rst.Open(strSQL, dbOpenStatic)
    If rst.RecordCount = 0 Then
    MsgBox "No match"
    Else
    MsgBox "We have a match!"
    End If
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing

    3. The third option doesn't require extra references. The Where condition is the criteria part of an SQL string, without the keyword WHERE, for example "DepartmentID = 5 And PayRank < 7"

    Dim strWhere As String
    strWhere = "..."
    If DCount("*", "tblSomething", strWhere) = 0 Then
    MsgBox "No match!"
    Else
    MsgBox "We have a match!"
    End If

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using RecordCount in a Module (Access2K, Win2KPro)

    thanks. the DAO method seems to work *almost* - but the recordcount is always 1 no matter how many times a name shows in the table. Probably something wonky with the SQL? I am trying to keep potential duplicates from getting into a table and the form I am doing this on is based on that table. Is there an issue with running a query in code from an open form displaying data from the table being queried?

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

    Re: Using RecordCount in a Module (Access2K, Win2KPro)

    You stated that you wanted to verify if a record existed. If RecordCount = 0, the answer is no, otherwise it is yes, so I left it at that. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The explanation for the behavior you see is that DAO doesn't try to read all records immediately when opening a recordset, for efficiency, so RecordCount is either 0 or something positive (usually 1). If you want to get the real record count, you must force DAO to read the last record:

    Set rst = dbs.OpenRecordset(...)
    rst.MoveLast
    MsgBox "The record count is " & rst.RecordCount
    ' If you need to loop through the records, move back to the start
    rst.MoveFirst
    ...

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using RecordCount in a Module (Access2K, Win2KPro)

    thanks. I almost had it but used rst.MoveFirst. Now it worketh.

Posting Permissions

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