Results 1 to 5 of 5
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Two questions about record sets in VBA

    I started a new procedure for a form, and modified an example that I found in another forum.
    Taking a few minutes to read that forum member's comments got me to thinking a bit deeper into the what of VBA.
    Below is the code as I modified it slightly.

    First, what's actually in rRs.BOF and rRs.EOF?
    If there is nothing returned for the record set, is it necessary to test both? And if so, why? (Okey, that's really three questions in one.)

    Second, is there any practical difference between using rRs.Fields("FldNm") or rRs!FldNm? (Excepting that one can use a variable)

    Thanks
    Attached Thumbnails Attached Thumbnails 20230116Rs1.jpg  

  2. #2
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    People often question another programmer's naming conventions, I'll answer before the inevitable questions arise.
    A Dim statement says what kind of variable it is, so one can always reference there.
    I avoid carrying around the extra bytes in front of variables just to say it's a strVariable or currVariable, etc.
    While helpful in writing one line of code to know what the type the variable is, I often know what it is by the usage.

    Rather, I find it more useful to know the "purpose" of the variable in the big picture of the routine.
    Hence, I use lower case letters in front of a variable to show how it might be used, such as:
    r = read, as in record set that was read in
    w = write, as in record set to write
    b (or w) = building or working, a variable that is used to build and use somewhere (as in the SQL statement)
    t = testing, as in Boolean or string comparisons
    a = argument, so I know I'm in a procedure that got its value from a calling routine

    The same for function and sub starting with a "f" or "s"

    For iterations I tend to always use the same lcnt1 and lstp1 for the counter and limit, changing 1 to 2 for nested iterations, (with a comment on each For line explaining its purpose).

    Personally, I use temp1, temp2, temp3, etc. (with comment if not obvious from its source), in short segments of code no more than ten lines so as to save and reuse memory space.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    To check if a recordset returns records I don't check the BOF and EOF, but simply its RecordCount:

    Code:
    If rs.RecordCount=0 then ..... 'usually I make this a one liner using GoTo to move to my cleanup label that comes before Exit Sub
    Do Until rs.EOF
        'stuff
        rs.MoveNext
    Loop
    As for the fields I use rs("Field") (Fields is the default collection) because it is easy to use a variable instead of the hardcoded name when needed.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    @Gicu Thanks for that answer. I see examples, and wonder, why this way? What's the downside to the other methods that I haven't experienced yet?
    Then I end up with a mix of varying code based on the particular example I emulated for the situation I faced and researched.
    It's easy to see why I miss the "old days" when there was one syntax for something, and only one syntax, and you could count on it always (excepting low level bugs) working one way.
    It was easy to code (IMHO):
    Code:
    Select filename {all the selection options] Then
     Eof = 0
     Loop
       Readnext ID Then
         Read record then
          do available record stuff
         Else
          do missing record stuff
         end
       Else Eof = 1
      Until Eof Do
       do more stuff if needed
     Repeat
    Else Debug !Where the hell is the file?
    For the purists, I know the extra code after the Do seems superfluous, but there were occasions where it came in handy, I can't remember now an example of what they were. Another more logical option was to move all the record stuff into the Do section of the Loop code.

  5. #5
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by Gicu View Post
    Code:
    If rs.RecordCount=0 then ..... 'usually I make this a one liner using GoTo to move to my cleanup label that comes before Exit Sub
    OMG!!! , you use GoTo in modern code?!!! How unstructured. How primitive! (If you've read some of my posts on this, then you know how much I'm laughing).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search Form that sets query criteria and sets Operands
    By mainerain in forum Programming
    Replies: 2
    Last Post: 04-29-2021, 10:27 AM
  2. Form and query return different record sets
    By Daryl2106 in forum Access
    Replies: 3
    Last Post: 12-11-2012, 09:41 PM
  3. Replies: 2
    Last Post: 04-22-2012, 04:19 PM
  4. Replies: 6
    Last Post: 05-04-2011, 06:17 PM
  5. Best Design for Multiple Y/N Questions on each record
    By DanielHochman in forum Database Design
    Replies: 0
    Last Post: 07-20-2009, 02:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums