Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002

    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
    NSW Australia
    set your SQL as a string then set a variable to equal that sql string

    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
    Myvar = rst.RecordCount
    end if


  3. #3
    Join Date
    Dec 2002


    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

    missing database object in vb??


    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
    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
    Préverenges, Switzerland
    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.


Posting Permissions

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