Results 1 to 15 of 15
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Speeding up code

    I only want the code to find and run on specific and if it meets the criteria for that row it goes to the next



    Code:
    If rV.BOF And rV.EOF Then
                    rV.Close
                Else
                    rV.MoveLast
                        rV.MoveFirst
        
        Do While Not rV.EOF
                rV.Edit
    
        For I = 1 To 3
            For J = 1 To 4
                 K = Chr(J + 64)
    If rV![Toe Src] = "06236K300" And rV![Para #] = "0109" Then
         If rV![Bumper # / Plat ID] Like I & "PLT " & K & "/" & INFBN(I) Then
             Units = I & " " & K & " " & INFBN(I)
               rV.Update
                    rV![Netbase] = Units
                            rV.MoveNext
    End If
          End If
            Next J
            Next I
    This is the first criteria:
    rV![Toe Src] = "06236K300" And rV![Para #] = "0109"

    Unless that is met then it should skip everything

    If met then it should look at
    If rV![Bumper # / Plat ID] Like I & "PLT " & K & "/" & INFBN(I)
    Example: 1PLT A/1BN2-222INF
    It seems that it keeps running though all the variations for each line even though the criteria is already met. How can fix this?

    Thanks

    Yes I know the naming conventions are not correct due to spaces and the pound sign. Not my database.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    unless one of your variables includes a wildcard then use = rather than Like

    If rV![Bumper # / Plat ID] = I & "PLT " & K & "/" & INFBN(I)

    I'm not going to spend time trying to work out what the code is doing, in addition to the confusing names, there is no documentation and the layout is awful

    But I would comment you have a movenext which is only applied if both conditions are true - maybe that is how it should be, maybe not

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Code:
    Sub Foo()
    
       If rV.BOF And rV.EOF Then
          rV.Close
       Else
          rV.MoveLast
          rV.MoveFirst
          Do While Not rV.EOF
             rV.Edit
             For i = 1 To 3
                For j = 1 To 4
                   K = Chr(j + 64)
                   If rV![Toe Src] = "06236K300" And rV![Para #] = "0109" Then
                      If rV![Bumper # / Plat ID] Like i & "PLT " & K & "/" & INFBN(i) Then
                         Units = i & " " & K & " " & INFBN(i)
                         rV.Update
                         rV![Netbase] = Units
                         rV.MoveNext
                      End If
                   End If
                Next j
             Next i
          Loop
       End If
       
    End Sub
    With the indenting set properly, does it look right to you?

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks AJAX,
    I have NO formal schooling for Access. Only what I have learned here and research.

    If the first is true and the second is met, then move next is correct.

    I counts 1 to 3, J sets up K to make A through D, INFBN(I) sets up INFBN(1)/(2)/(3) which equates to a name 1BN222INF/2BN222INF/3BN222INF as an example

    [Bumper # / Plat ID] may be 1PLT A/1-222INF ( Thats where there might be an issue) I have it set up as 1PLT A/1BN222INF

    Ruralguy - yes it does

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    If rV![Bumper # / Plat ID] Like i & "PLT " & K & "/" & INFBN(i) Then

    INFBN(i) doesn't look like [Bumper # / Plat ID] its format is:

    #-50# example
    1-501
    2-501
    1-505
    2-505
    1-508.......

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Code:
    If rV![Bumper # / Plat ID] Like i & "PLT " & K & "/" & INFBN(i) Then
        Units = i & " " & K & " " & INFBN(i)
        rV.Update
        rV![Netbase] = Units
        rV.MoveNext
    End If
    It looks to me like the order of these two lines:
    rV.Update
    rV![Netbase] = Units
    ...should be reversed to...
    rV![Netbase] = Units
    rV.Update

    although I have not really followed your code well.

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I have iterations [bumper....] in Paragraph number 0109 of the unit with a specific unit designation in column [TOE SRC]

    1PLT A/1-222
    1PLT B/1-222
    1PLT C/1-222
    1PLT D/1-222
    2PLT A/1-222
    2PLT B/1-222
    2PLT C/1-222
    2PLT D/1-222
    3PLT A/1-222
    3PLT B/1-222
    3PLT C/1-222
    3PLT D/1-222
    1PLT A/1-223
    1PLT B/1-223
    1PLT C/1-223
    1PLT D/1-223
    2PLT A/1-223
    2PLT B/1-223
    2PLT C/1-223
    2PLT D/1-223
    3PLT A/1-223
    3PLT B/1-223
    3PLT C/1-223
    3PLT D/1-223
    1PLT A/2-222
    1PLT B/2-222
    1PLT C/2-222
    1PLT D/2-222
    2PLT A/2-222
    2PLT B/2-222
    2PLT C/2-222
    2PLT D/2-222
    3PLT A/2-222
    3PLT B/2-222
    3PLT C/2-222
    3PLT D/2-222

    I am trying to get update Data.Netbase where that [Bumper...] from above will provide an output

    1 A 1BN222INF
    1 B 1BN222INF
    1 C 1BN222INF
    1 D 1BN222INF
    2 A 1BN222INF
    2 B 1BN222INF
    2 C 1BN222INF
    2 D 1BN222INF
    3 A 1BN222INF
    3 B 1BN222INF
    3 C 1BN222INF
    3 D 1BN222INF
    1 A 1BN223INF
    1 B 1BN223INF
    1 C 1BN223INF
    1 D 1BN223INF
    2 A 1BN223INF
    2 B 1BN223INF
    2 C 1BN223INF
    2 D 1BN223INF
    3 A 1BN223INF
    3 B 1BN223INF
    3 C 1BN223INF
    3 D 1BN223INF
    1 A 2BN222INF
    1 B 2BN222INF
    1 C 2BN222INF
    1 D 2BN222INF
    2 A 2BN222INF
    2 B 2BN222INF
    2 C 2BN222INF
    2 D 2BN222INF
    3 A 2BN222INF
    3 B 2BN222INF
    3 C 2BN222INF
    3 D 2BN222INF

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The previous lines are missing - my question is, what is "rv"? Why does rv" not contain the criteria so that only those records come into the loop?

    The movelast/first stuff. All you need is:
    Do Until rv.EOF
    rv.Edit
    .....
    rv.Update
    rv.MoveNext
    Loop

    Once you have the updating being done on the correct records and find that the updating itself is wrong (incorrect value on records) then we can look at the inner portion.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    It seems that it keeps running though all the variations for each line even though the criteria is already met. How can fix this?
    which lines are the variations you would not expect it to run through? the I loop?, J loop? while not .eof?

  10. #10
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ajax - it is a combination of I and J, J sets up K get the value of K
    so that it finds the bumper of i PLT K/1-222 it can update the Netbase column with the data developed from Units.
    Code:
    Option Compare Database
    Option Explicit
    Dim rV As DAO.Recordset
    Set rU = CurrentDb.OpenRecordset("Data")
    Dim i As Integer, J As Integer, K As String
    
     If rV.BOF And rV.EOF Then
          rV.Close
       Else
          rV.MoveLast
          rV.MoveFirst
          Do While Not rV.EOF
             rV.Edit
            If rV![TOE SRC] = "06236K300" And rV![Para #] = "0109" Then
             For i = 1 To 3
                For J = 1 To 4
                   K = Chr(J + 64)
                       If rV![Bumper # / Plat ID] Like i & "PLT " & K & "/1-222" Then
                         Units = i & " " & K & " 1BN222INF"
                       If rV![Bumper # / Plat ID] Like i & "PLT " & K & "/2-222" Then
                         Units = i & " " & K & " 2BN222INF"
                       If rV![Bumper # / Plat ID] Like i & "PLT " & K & "/1-223" Then
                         Units = i & " " & K & " 1BN223INF"
                            rV![Netbase] = Units
                           rV.Update
                         rV.MoveNext
                   End If
                Next J
             Next i
             End If
          Loop
       End If
      
    End Sub
    1PLT A/1-222 =1 A 1BN222INF
    1PLT B/1-222 =1 B 1BN222INF
    1PLT C/1-222 =1 C 1BN222INF
    1PLT D/1-222 =1 D 1BN222INF
    2PLT A/1-222 =2 A 1BN222INF
    2PLT B/1-222 =2 B 1BN222INF
    2PLT C/1-222 =2 C 1BN222INF
    2PLT D/1-222 =2 D 1BN222INF
    3PLT A/1-222 =3 A 1BN222INF
    3PLT B/1-222 =3 B 1BN222INF
    3PLT C/1-222 =3 C 1BN222INF
    3PLT D/1-222 =3 D 1BN222INF
    1PLT A/1-223 =1 A 1BN223INF
    1PLT B/1-223 =1 B 1BN223INF
    1PLT C/1-223 =1 C 1BN223INF
    1PLT D/1-223 =1 D 1BN223INF
    2PLT A/1-223 =2 A 1BN223INF
    2PLT B/1-223 =2 B 1BN223INF
    2PLT C/1-223 =2 C 1BN223INF
    2PLT D/1-223 =2 D 1BN223INF
    3PLT A/1-223 =3 A 1BN223INF
    3PLT B/1-223 =3 B 1BN223INF
    3PLT C/1-223 =3 C 1BN223INF
    3PLT D/1-223 =3 D 1BN223INF
    1PLT A/2-222 =1 A 2BN222INF
    1PLT B/2-222 =1 B 2BN222INF
    1PLT C/2-222 =1 C 2BN222INF
    1PLT D/2-222 =1 D 2BN222INF
    2PLT A/2-222 =2 A 2BN222INF
    2PLT B/2-222 =2 B 2BN222INF
    2PLT C/2-222 =2 C 2BN222INF
    2PLT D/2-222 =2 D 2BN222INF
    3PLT A/2-222 =3 A 2BN222INF
    3PLT B/2-222 =3 B 2BN222INF
    3PLT C/2-222 =3 C 2BN222INF
    3PLT D/2-222 =3 D 2BN222INF



  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    your problem is it is running slow and you think because 'It seems that it keeps running though all the variations for each line even though the criteria is already met. '

    I'm asking you to explain what this means

    you have provided some data in the last post - is this what you expect to get? if not what do you expect?

  12. #12
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I think I have it now. One of the end if was out of place.

    Code:
    Private Sub temp2_Click()
    Dim rV As DAO.Recordset
    Dim i As Integer, J As Integer, Units As String
    Set rV = CurrentDb.OpenRecordset("Data")
          Do While Not rV.EOF
             rV.Edit
                
     For i = 1 To 3
        For J = 1 To 4
          K = Chr(J + 64)
                If rV![Bumper # / Plat ID] = i & "PLT " & K & "/1-222" Then
                         Units = i & " " & K & " " & "1BN222INF"
                ElseIf rV![Bumper # / Plat ID] = i & "PLT " & K & "/1-223" Then
                         Units = i & " A 1BN223INF"
                ElseIf rV![Bumper # / Plat ID] = i & "PLT " & K & "/2-222" Then
                         Units = i & " " & K & " " & "2BN222INF"
                End If
     Next J
     Next i
                        rV![Netbase] = Units
                    rV.Update
                rV.MoveNext
          Loop
    End Sub
    I have to dig a little to verify and clean up my code formatting

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Thompyt View Post
    Thanks AJAX,
    I have NO formal schooling for Access. Only what I have learned here and research.
    In that case, here are some suggestions.

    Naming objects:
    ---------------
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.


    ---------------
    For code formatting, download and install Smart Indenter v3.5 (Office 2000/2002/2003 version)

  14. #14
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ssanfu,
    The field names are not of my design. I know as I have been beat about the head on here on it. LOL Thanks

    It works now - I found the error
    Units = i & " A 1BN223INF"
    should be
    Units = i & " " & K & " 1BN223INF"

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sorry for the additional bruises... lol


    But do check out Smart Indenter... I wouldn't be without it.

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

Similar Threads

  1. Speeding things up
    By aytee111 in forum Access
    Replies: 22
    Last Post: 09-22-2016, 07:06 AM
  2. Speeding up record search
    By Paul H in forum Forms
    Replies: 22
    Last Post: 11-03-2015, 06:44 PM
  3. speeding up queries
    By frustratedwithaccess in forum Access
    Replies: 13
    Last Post: 10-10-2014, 12:08 PM
  4. Speeding up Table Linking Times
    By cbh35711 in forum Access
    Replies: 2
    Last Post: 03-27-2012, 03:54 PM
  5. Speeding up Macros
    By salisbut in forum Programming
    Replies: 3
    Last Post: 07-19-2010, 04:02 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