Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Indianapolis, Indiana, USA
    Thanked 0 Times in 0 Posts

    Query output (AXP)

    Hello All,
    I know this may be a simple question........ How do you determine the number of records output from a query? I am using VBA code to run a simple query on a table: strSource = "Select * from badges where badge_id = [Enter Badge ID]"

    I want to determine if strsource has a record or not. How would I do that?


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Query output (AXP)

    Your SQL string looks as if it has a parameter [Enter Badge ID]. That is not ideal in VBA - when you try to get the record count, you must supply the parameter. It's best to get the parameter before running the code, for example from a text box on a form.

    One way to do it is to use DCount, but that doesn't use the exact source string

    DCount("*", "Badges", "Badge_ID = " & Forms!frmSomething!txtID)

    Another way is to open a recordset:

    Dim strSource As String
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Set cnn = CurrentProject.Connection
    strSource = "Select * from badges where badge_id = " & Forms!frmSomething!txtID
    rst.Open strSource, cnn, adOpenStatic
    MsgBox rst.RecordCount
    Set rst = Nothing
    Set cnn = Nothing

Posting Permissions

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