Output from a search with composite field yield incorect records

[Jack]

Hi,
I have search form which is a composite search form. Using various criteria
one can search for records which is displayed in a form. This form has record
navigation so that if there are multiple record as the result of the search
one can navigate through the records.

Now in the present circumstance I had to build a locking mechanism that will
prevent user to editing the displayed record if certain condition match. When
I am debugging and stepping through the code everything seems to be working
great. However in the final step when the search result records are displayed
those are not what should be. I cannot figure out why at the last step (after
coming out of loop) the records are different than what it ought to be. I
would appreciate any help or insight as to how to handle this. Thanks.
CODE:
Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click

Dim stDocName As String
Dim stLinkCriteria As String

Dim strBizUnit As String

DoCmd.OpenForm "frmCompositeSearch"
Forms!frmCompositeSearch.Visible = False

stDocName = "frmActionRequestFiltered"

stLinkCriteria = "[BizUnit]=" & "'" & Me![txtBusUnit] & "'"

If Me.txtOpenClosed <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Status]=" & "'" & Me![txtOpenClosed] & "'"
End If

If Me.cboplant <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Plant]=" & "'" & Me![cboplant] & "'"
Me.cboplant.Value = ""
End If

If Me.cboCust <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Customer]=" & "'" & Me![txtCust] & "'"
Me.cboCust.Value = ""
End If

If Me.cboOriginator <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[Originator]=" & "'" & Me![cboOriginator] & "'"
Me.cboOriginator.Value = ""
End If

If Me.txtStartDate <> "" And Me.txtEndDate <> "" Then
stLinkCriteria = stLinkCriteria _
& " And " & "[RequestDate]" & " Between " _
& "#" & Me![txtStartDate] & "#" & " And " _
& "#" & Me![txtEndDate] & "#"
End If

Debug.Print ("Right Here")

' DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, , , stLinkCriteria, OpenArgs:=stLinkCriteria

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click

End Sub



'The following is the code in the Open_form event of the form that displays
the records:

'Here we are processing after coming from the click of the composite search
button
Dim strwhere As String
strwhere = Me.OpenArgs


If Me.RecordsetClone.BOF = True Then
MsgBox "There are no records that match your criteria. Please try again."
DoCmd.Close acForm, "frmActionRequestfiltered"
Exit Sub
End If
'Now we capture the Me.OpenArgs value to build all Cars first


Debug.Print ("Right here")
'Now we build the recordset that will list all the IDNums

Dim rst_build As ADODB.Recordset
Dim rst_l_build As ADODB.Recordset
Dim strsql_build As String
Dim strGetID_build As String
Dim strsql_l_build As String
Dim var_out_l_build As String

Set rst_build = New ADODB.Recordset
Set rst_build.ActiveConnection = CurrentProject.Connection
rst_build.CursorType = adOpenForwardOnly
rst_build.LockType = adLockReadOnly

strsql_build = "select IDNUm FROM tblActionRequest LEFT JOIN tblOpNonOp ON
tblActionRequest.OpNonOp = tblOpNonOp.ID where " & strwhere & ""
rst_build.Open strsql_build, CurrentProject.Connection


'Now we use a do loop to extract the IDNUm in the above query in each loop
'do processing to open the record one by one
Do While Not rst_build.EOF
strGetID_build = rst_build(0)

Set rst_l_build = New ADODB.Recordset
Set rst_l_build.ActiveConnection = CurrentProject.Connection
rst_l_build.CursorType = adOpenForwardOnly
rst_l_build.LockType = adLockReadOnly



strsql_l_build = "Select locked from tblActionRequest where IDNUM = '" &
strGetID_build & "'"
rst_l_build.Open strsql_l_build, CurrentProject.Connection
var_out_l_build = rst_l_build(0)
rst_l_build.Close
If var_out_l_build = "Y" Then
Me.RecordsetType = 2

Me.frmSub7DPAEffect.Form.AllowEdits = False
Me.frmSubContainPlan.Form.AllowEdits = False
Me.qry6DPermAction_subform.Form.AllowEdits = False
Me.subfrmApprovals.Form.AllowEdits = False
End If
rst_build.MoveNext
Loop