I have been assigned the task of fixing a problem with an Access 2010 Application build by a previous employee. Here is the design, There is a main menu that has a button to search by Req Number. Clicking this button will open a pop up form where you enter the ID number to search for. After entering the number and clicking the search button the code is supposed to open another form named frmDataEntry filtered by the ID number entered in the search box. The code runs without an error. The problem is it opens the DataEntry form and shows all records. I have included my code block beloe
Code:
Case "Req" Me.cmdDone.SetFocus
GotoSearchTextbox
If IsNull(Me.txtSearchTerm) Then 'Blank, show them all!
StrSql = "SELECT tblOrders.*, tblfleetvendors.PVAdr1, tblfleetvendors.PVAdr2, tblfleetvendors.PVCity, tblfleetvendors.PVState, tblfleetvendors.PVZip"
StrSql = StrSql & " FROM tblOrders INNER JOIN tblfleetvendors ON tblOrders.VendorId = tblfleetvendors.VendorID"
StrSql = StrSql & " WHERE (((tblOrders.Org)=DLookUp(""[DivisionOrg]"",""tblSetup"")) AND ((tblOrders.VendorID <> 774)))"
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryOrders")
qdf.SQL = StrSql
DoCmd.OpenForm "frmDataEntry"
DoCmd.Close acForm, Me.Name
DoCmd.Close acForm, "frmMainMenu"
Else
Dim Req As Integer
Req = Me.txtSearchTerm
StrSql = "SELECT tblOrders.*, tblFleetVendors.* FROM tblOrders INNER JOIN tblFleetVendors ON tblOrders.VendorId = tblFleetVendors.VendorID"
StrSql = StrSql & "WHERE (((tblOrders.Org)=DLookUp(""[DivisionOrg]"",""tblSetup""))"
StrSql = StrSql & "AND ((tblOrders.VendorId)<>774) AND ((tblOrders.OrderID)=" & Req & "));"
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryOrders")
qdf.SQL = StrSql
Set rst = db.OpenRecordset("qryOrders", dbOpenDynaset, dbSeeChanges)
intRecords = rst.RecordCount
If intRecords <= 0 Then
MsgBox "There are no records that match your search"
rst.Close
Set rst = Nothing
Set db = Nothing
rst.Close
DoCmd.Close acForm, Me.Name
Exit Sub
Else
Form!frmDataEntry.RecordSource = StrSql
DoCmd.OpenForm "frmDataEntry"
DoCmd.Close acForm, Me.Name
DoCmd.Close acForm, "frmMainMenu"
End If
End If
Can someone help me out and tell me why this code does not work as ?