Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    CANADA
    Posts
    2

    Unanswered: Retrun number of rows as variable?

    I am not a VB or VBA junkie... nor am I that great with databases either, but I am learning out of neccessity at the moment. Please bear with me.

    I need to check if a record exists in a certain table and return a value if it does or doesn't in VBA. I know how to use COUNT and a SELECT query, but how do I return the value as a variable if there was any records or not?

    Prolly a dumb question, but I have been thrown into this and I am learning as I go...

  2. #2
    Join Date
    Oct 2002
    Location
    NSW Australia
    Posts
    61
    set your SQL as a string then set a variable to equal that sql string

    eg.
    dim dbs as database
    dim rst as recordset
    dim strSQL as String
    dim MyVar as Variant
    set dbs = Currentdb()
    strSQL = "SELECT MyField,etc,etc from mytable;"
    set rst = dbs.openrecordset("strSQL")

    if IsNull(rst.RecordCount) then
    MyVar = 0
    else
    Myvar = rst.RecordCount
    end if



    HTH
    :0)

  3. #3
    Join Date
    Dec 2002
    Location
    CANADA
    Posts
    2

    Thanx

    Thank you for your response.

    I was kinda getting there myself, but slowly. I picked up a couple of books on VB, VBA, designing Database Applications and another one on SQL. Looks like this will be my major focus at work from now on, so you will be seeing my posts here a lot until I figure things out.

    Thanks again for the help.

  4. #4
    Join Date
    Nov 2002
    Posts
    35

    missing database object in vb??

    Hello,

    I am trying to do a similar thing (checking for a recordcount) and ran into this code so I figured I would try it but when I try to use the database object in my VB, I get the following message:

    Compile Error:
    User-defined type not defined


    This is my code (within a Private Sub):
    Dim sqlString As String
    Dim dbs As database
    Dim rst As Recordset


    Set dbs = CurrentDb()

    ' check for required fields
    If IsNull(providerID) Then
    errMsg = "- Provider ID" & vbNewLine
    Else
    sqlString = "SELECT pin from tblSurvey WHERE pin = " & providerID & ";"
    Set rst = dbs.openrecordset("sqlString ")

    If IsNull(rst.RecordCount) Then
    MsgBox "THis survey has already been entered, somehow...."
    Exit Sub
    End If
    End If

    Using Access 2000 on Windoze 2000. Any advise??

    ~ Joyce

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if all you need is a count (i.e. you are not about to do something else with the recordset) why mess with a recordset.

    domain aggreagate functions do all the single-answer stuff you need on a domain: count, max, min, sum etc etc etc

    if its a stored query:
    myVar = Dcount("[aFieldToCount]", "myQueryName")
    ...this doesn't count aFieldToCount=null

    or perlgurl's question:
    myVar = Dcount("[pin]", "tblSurvey", "[pin] = 1234")
    ...i leave you to sort out the concatenation of a variable into the criteria section - my brain is not in gear on new years day.

    izy

Posting Permissions

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