VB within Access question

Discussion in 'OT Technology' started by shastaisforwinners, Feb 10, 2004.

  1. shastaisforwinners

    shastaisforwinners OT Supporter

    Joined:
    Aug 31, 2003
    Messages:
    2,820
    Likes Received:
    4
    Location:
    Paramore
    ok, I'm trying to get a row count from a table, in order to use it in an If statement... something to the effect of this:

    if TABLE.RowCount = 1 then

    'do code

    else

    'do code

    I cannot figure out how to get a row count to use in VB though :(
    any ideas?
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    Code:
    Dim count As Integer
    count = DLookup("count(*)", "table1")
    
    if count = 1 then
       'code
    else
       'code
    end if
    
    replace "table1" with your table name
     
  3. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    damn, i beat 5Gen_Prelude on one of these Access questions :big grin:
     
  4. shastaisforwinners

    shastaisforwinners OT Supporter

    Joined:
    Aug 31, 2003
    Messages:
    2,820
    Likes Received:
    4
    Location:
    Paramore
    thank you!!

    god, I've been racking my brain trying to figure this out

    didn't know about DLookup :)
     
  5. shastaisforwinners

    shastaisforwinners OT Supporter

    Joined:
    Aug 31, 2003
    Messages:
    2,820
    Likes Received:
    4
    Location:
    Paramore
    ok, one more...

    any idea why tab isn't working in my Login box? I'm sure it's a form property that I have disabled, but I have no idea which one :(
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah, yeah, yeah... I do work sometimes :big grin:

    The proper way would be:

    Code:
    Function RecordCounter(Tablename as String) As Long
    
    Dim dbs As Database
    Dim recset As Recordset
    
    Set dbs = CurrentDb()
    Set recset = dbs.OpenRecordset(Tablename, dbOpenDynaset)
    
    With recset
    
        .MoveLast
        RecordCounter = .RecordCount
        .Close
    
    End With
    
    dbs.Close
    
    End Function
    But goddamn, that's a lot of work to do the same thing. Anywhere, there are occasions when dlookup doesn't quite work the way it should - this is the alternative.
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    What do you want the tab to do?
     
  8. shastaisforwinners

    shastaisforwinners OT Supporter

    Joined:
    Aug 31, 2003
    Messages:
    2,820
    Likes Received:
    4
    Location:
    Paramore
    just to tab between fields... I somehow managed to disable it, but not sure how exactly
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I can't think of anything unless there are no other controls that are enabled on the form or tabbed page to tab to. You can control how the records cycle however - that's on the "Other" tab for the properties of the form
     
  10. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    5gen, when is Dlookup ever a bad idea? I'm kinda curious now
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    dlookup can cause your program to come grinding to a halt IME. If it's a one time lookup, it's okay, if you start trying to use it in lou of a properly designed query, then it becomes a problem. Also when dealing with a LOT of records, it doesn't always return the result you think it should - this is more of a bug than a design issue though.
     
  12. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    hmm, would have thought that it just put together the query, and returned a scaler value... i wonder how they screwed it up? Wouldn't be too hard to write your own....
     
  13. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Beats me - I've had cases where I had to make a table before I executed a delete or update query.

    Here's what MS even says:

    Avoid using domain aggregate functions, such as the DLookup function to access data from a table that's not in the query. Domain aggregate functions are specific to Microsoft Access, which means that the Jet database engine can't optimize queries that use them. Instead, add to the query the table that the function was accessing, or create a subquery.
     

Share This Page