Find Record appears to fail after 20*4 search cycles

[Phesunov]

Below is the code in question. The searching is using FindRecord. It works
but after the 20th change by the user the find function seems to fail without
error. Further code tracing reveals that during the four find record actions
it's stuck on the first server type. Closing the form does not correct the
issue. I have to fully exit Access and restart it to make it work again.
Then it fails again at the 20th user change. No memory-related issues are
encountered.

Wondering if anyone has seen a similar issue. I'm using Access 2003. On a
form I'm trying to enforce a rule that the user can only select one type of
server system (Base,CPU,SA,DEV) across multiple products.

I'm using a For Next loop to go to each record and within that I'm searching
the Base,CPU,SA and Dev of each product group to check current quantities and
moving them to the chosen type.

The searching is using FindRecord. It works but after the 20th change by
the user the find function seems to fail without error. Further code tracing
reveals that during the four find record actions it's stuck on the first
server type. Closing the form does not correct the issue. I have to fully
exit Access and restart it to make it work again. Then it fails again at the
20th user change. No memory-related issues are encountered.


'Edit S9 06 Choices is a form containing fields [CHK_Select],[Qty],[Product]
,[Category],[LGLabel]

'QSelect = Check Box ([CHK_Select]) True or False
'Several Products on the form.
'Each Product has 3-4 "Server" Categories
'These Server Categories have similar LGLabels within the Product
differentiated by the server type being Base, Dev, CPU and SA
'Business Rules states that only one server type can be selected and all
server types must be the same across the products.
'This code is supporting a manual method for a user to select which server
types and options to select as part of an advanced editing function.


If [Category] Like "*Server*" Then
' User chose server
ProductTest = [Product]
CategoryTest = [Category]
LGLabelTest = Switch([LG Label] Like "*Base*", "Base", [LG Label] Like
"*DEV*", "DEV", [LG Label] Like "*CPU*", "CPU", [LG Label] Like "*SA*", "SA",
True, "XX")
LGLabelTarget = Replace([LG Label], LGLabelTest, "^")

If QSelect Then

ProductTempTest = ""
' Go to top of list
DoCmd.FindRecord "Include Server", acAnywhere, acSearchAll, , acAll
'Determine Total Records
RC = DCount("*", "Edit S9 06 Choices")

'Check all Records
For H = 1 To RC
'Goto Record #H
DoCmd.GoToRecord acDataForm, "Edit S9 06 Choices", acGoTo, H

If [Product] <> ProductTempTest Then
'Reset Local Product Search
ProductTempTest = [Product]
TransferQty = 0
TransferTest = 0
LGLabelTarget = Replace(Replace(Replace(Replace([LG Label],
"Base", "^"), "DEV", "^"), "CPU", "^"), "SA", "^")


'Check Base and Reset to Zero if different than chosen base
type
LGTarget2 = Replace(LGLabelTarget, "^", "Base")
[LG Label].SetFocus
DoCmd.FindRecord LGTarget2, acAnywhere, False, acSearchAll,
yes, acCurrent, False

If LGLabelTest <> "Base" And [LG Label] Like "*Base*" Then
TransferTest = TransferTest + Switch([CHK_Select], 1,
Not ([CHK_Select]), 0)
TransferQty = TransferQty + [Qty]
[Qty] = 0
= False
End If


'CPU
LGTarget2 = Replace(LGLabelTarget, "^", "CPU")
[LG Label].SetFocus
DoCmd.FindRecord LGTarget2, acAnywhere, False,
acSearchAll, yes, acCurrent, False

If LGLabelTest <> "CPU" And [LG Label] Like "*CPU*" Then
TransferTest = TransferTest + Switch([CHK_Select], 1,
Not ([CHK_Select]), 0)
TransferQty = TransferQty + [Qty]
[Qty] = 0
[CHK_Select] = False
End If


'DEV
LGTarget2 = Replace(LGLabelTarget, "^", "DEV")
[LG Label].SetFocus
DoCmd.FindRecord LGTarget2, acAnywhere, False,
acSearchAll, yes, acCurrent, False
If LGLabelTest <> "DEV" And [LG Label] Like "*DEV*" Then
TransferTest = TransferTest + Switch([CHK_Select], 1,
Not ([CHK_Select]), 0)
TransferQty = TransferQty + [Qty]
[Qty] = 0
[CHK_Select] = False
End If


'SA
LGTarget2 = Replace(LGLabelTarget, "^", "SA")
[LG Label].SetFocus
DoCmd.FindRecord LGTarget2, acAnywhere, False,
acSearchAll, yes, acCurrent, False
If LGLabelTest <> "SA" And [LG Label] Like "*SA*" And
[Product] <> "Dashboard Development" Then
TransferTest = TransferTest + Switch([CHK_Select], 1,
Not ([CHK_Select]), 0)
TransferQty = TransferQty + [Qty]
[Qty] = 0
[CHK_Select] = False
End If


'Go to the chosen Base type and update with TransferQty
[LG Label].SetFocus
LGTarget2 = Replace(LGLabelTarget, "^", LGLabelTest)
DoCmd.FindRecord LGTarget2, acEntire, , acSearchAll, , acAll
'Confirm at correct record


If [LG Label] Like "*" & LGLabelTest & "*" Then
'If transfer Qty >0 then give it to this base type
If TransferTest > 0 Then
[CHK_Select] = True
[Qty] = TransferQty
End If
End If
End If ' ProductTempTest
Next H ' H Loop
End If ' QSelect = True

End If ' Server Test