Results 1 to 5 of 5

Thread: Recordset help

  1. #1
    Join Date
    Mar 2004
    Location
    Massachusetts
    Posts
    16

    Unanswered: Recordset help

    This is what I'm trying to do:

    When a command button is CLICKED I want to:

    1. Execute a QUERY that will return single record & column that will have a count.

    2. I want to use that count in a conditional statement to determine what I do next.

    My question is::

    How do I get that count from my QUERY?? Or RecordSet?

    If the answer is going to be ADODB, then I'll need help with the setting that up.

    I'm hoping it will be simpler then that.

    Here's what I've got:

    My Query:

    SELECT count(*) AS TOTAL_COUNT
    FROM [check-table-dates], [chk-overship-dates]
    WHERE [check-table-dates].strdate=[chk-overship-dates].strdate;

    NAMED: "CHECK-OVERSHIP-b4-IMPORT"

    My Codewithout the ?????)

    Private Sub Command25_Click()
    On Error GoTo Err_Command25_Click

    Dim stDocName As String
    Dim strSQL As String
    Dim chkcntr As Integer

    stDocName = "Check-OverShip-b4-Import"
    DoCmd.OpenQuery stDocName, acViewNormal, acEdit

    ??????????????????????????

    If chkcntr > 0 Then
    intErr = MsgBox("a " & chkcntr)
    Exit Sub
    Else
    intErr = MsgBox("b " & chkcntr)
    stDocName = "imxport-overship"
    DoCmd.RunMacro stDocName
    End If

    Exit_Command25_Click:
    Exit Sub

    Err_Command25_Click:
    MsgBox Err.Description
    Resume Exit_Command25_Click

    End Sub

  2. #2
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    Dim strSql As String
    Dim con As New ADODB.Connection
    Set con = CurrentProject.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    strsql = "select * from tableA where ...."
    rs.Open strSql, con, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic
    my count = rs.recordcount
    rs.close

  3. #3
    Join Date
    Mar 2004
    Location
    Massachusetts
    Posts
    16

    getting error message

    Method 'Connection' of object'_CurrentProject' failed

    I did add:

    Option Compare Database

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

    ----------------------------

    Private Sub Command25_Click()
    On Error GoTo Err_Command25_Click

    Dim chkcntr As Integer

    Dim con As New ADODB.Connection
    Set con = CurrentProject.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    strSql = "SELECT count(*) AS Total_Count FROM [check-table-dates], [chk-overship-dates] WHERE [check-table-dates].strdate=[chk-overship-dates].strdate;"

    rs.Open strSql, con, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic
    chkcntr = rs.RecordCount
    rs.Close

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Eh... what are you connecting too? Is there anything preventing you from using domain functions like DCOUNT()?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2004
    Location
    Massachusetts
    Posts
    16

    Don't know about domain functions

    I'm working on an XP PC, connected to XP network(where ACCESS 2002 is) but database is on C:

Posting Permissions

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