Access VBA

Discussion in 'OT Technology' started by eric_stlouis, May 11, 2004.

  1. eric_stlouis

    eric_stlouis New Member

    Joined:
    Oct 10, 2003
    Messages:
    147
    Likes Received:
    0
    Hey, everyone.

    I'm having troulbe with the doCmd.findRecord command in Access VBA.

    I'm trying to go to a record in a form where two of my checkbox objects are not checked. So, I need to place some kind of query into the findRecord or gotoRecord method, but I'm not sure how to do this.

    I'd like to be able to do something like:

    doCmd.findRecord "chkOne = True and chkTwo = True".

    Right now I'm just going through every record in the table with a for/next loop searching for the record, but the sequential search takes FOREVER.

    Any suggestions?
     
  2. 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
    The findrecord can't have two fields to look for. A filter can however, and that may be the way to go.

    One other possibility is to create another field that is evaluated on the fly which is a combination of the two boolean fields:

    BothTrue = iif(chkOne and chkTwo,true,false)

    Then findrecord on that field.

    The next way would be to find a record where chkOne is true and check to see if chkTwo is true as well. As long as you check from where you are and not from the start, it should keep looking until both conditions are met:

    docmd.gotocontrol chkone
    docmd.findrecord true

    do while chktwo = false
    docmd.findrecord true, , , , , , false
    loop

    You'll need to put some way of stopping it if it never finds it though.
     
  3. eric_stlouis

    eric_stlouis New Member

    Joined:
    Oct 10, 2003
    Messages:
    147
    Likes Received:
    0
    Thanks so much 5Gen! I created a BothTrue checkbox on my form and the find worked perfectly.

    The only problem is that when I run the routine a second time, it doesn't work. I'm thinking that I need to use the docmd.findnext command. I'll give that a try tonight.

    Nice car, by the way. I have a 2003 RSX Type-S.
     
  4. 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
    One of the options in the findrecord command allows you start from the beginning or from where you are.
     
  5. eric_stlouis

    eric_stlouis New Member

    Joined:
    Oct 10, 2003
    Messages:
    147
    Likes Received:
    0
    Yep, that did it. The strange part of it was that in order to keep the loop working I had to use SendKeys "{ENTER}" to kick off the routine. If I just called the Sub it didn't work, but everything's working great now.

    Thanks much!
     
  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
    You have to docmd.gotocontrol before you start the find - especially when you want to search through a subform.
     

Share This Page