[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Matching Record Not found

I sometimes get a msgbox "Matching Record was not found" when I try searching for BlockNo =1 and LotNo =1 using the code below. This msgbox is displayed only when the BlockNo input is 1 and LotNo input is also 1. All other inputs begining from 2 does not give the msgbox.

I believe that the major design problem is in the "cmFind" code

My codes are arranged as follows:

Private Sub Form_Open(Cancel As Integer)
 DoCmd.GoToRecord , "frmSewer", acNewRec  <<--- This On Open code is also placed in my "btnAddNew"
 '   Call QuestionBox                                                                                               ' Record button
    Me.txtAppEntryDate.SetFocus
' ***********
...and my cmdFind code is shown below:

Private Sub cmdFind_Click()
 Dim strSearch As String
                 
If Not IsNull(cboFindBlockNo) And Len(Trim(cboFindBlockNo)) > 0 Then _
strSearch = "BlockNo = " & cboFindBlockNo
If Not IsNull(cboFindLotNo) And Len(Trim(cboFindLotNo)) > 0 Then _
strSearch = strSearch & IIf(Len(strSearch) > 0, " AND ", "") & "LotNo = " & cboFindLotNo
If IsNull(strSearch) Or Len(Trim(strSearch)) = 0 Then
MsgBox "No search criteria was provided", vbCritical, "Validation Error"
Exit Sub
End If

Dim rst As DAO.Recordset
Dim frm As Form
Select Case OpenArgs
Case 0
num = 0
Case 1
num = 1
Case 2
num = 2
Case 3
num = 3
Case 4
num = 4
End Select
Set frm = Forms(num)
Set rst = Forms(num).RecordsetClone
rst.MoveLast                                      
rst.MoveFirst
rst.FindFirst strSearch

If rst.NoMatch Then
MsgBox "The matching record was not found", vbInformation, "No Record Found"        ' <<<-- Error line
Else
' set the form to the found record
Forms(num).Bookmark = rst.Bookmark
rst.FindNext strSearch
End If
If rst.NoMatch Then
rst.Close
Set rst = Nothing
Else
rst.FindPrevious strSearch
btnFindNext.Enabled = True
btnFindNext.SetFocus
CmdFind.Enabled = False
btnStop.Enabled = True
End If
End Sub
'************
0
billcute
Asked:
billcute
  • 4
  • 3
1 Solution
 
puppydogbuddyCommented:
Hi Bill,
I believe your problem is that you are using numeric syntax to find a text string.   For example,
"BlockNo = " & cboFindBlockNo                      'use if BlockNo is numeric

should be:
"BlockNo = '" & cboFindBlockNo & "'"                'use if BlockNo is text

the same applies to LotNo or any other field in your search string


PDB
0
 
billcuteAuthor Commented:
PDB,
The BlockNo and LotNo are both numeric not text
0
 
billcuteAuthor Commented:
PDB,
Can you post the correct code if the code below is not for numeric...

strSearch = "BlockNo = " & cboFindBlockNo
If Not IsNull(cboFindLotNo) And Len(Trim(cboFindLotNo)) > 0 Then _
strSearch = strSearch & IIf(Len(strSearch) > 0, " AND ", "") & "LotNo = " & cboFindLotNo
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
puppydogbuddyCommented:
Bill,
Then why are you using the trim function?  Check the data types you are using for the combo boxes. Maybe combo box is text and needs to be converted to value like this:

  "BlockNo = " & Val(cboFindBlockNo)                'use if BlockNo is numeric, but combo box is text
0
 
puppydogbuddyCommented:
code if blockno numeric and cboFind is text:

strSearch = "BlockNo = " & Val(cboFindBlockNo)
If Not IsNull(cboFindLotNo) And Len(Trim(cboFindLotNo)) > 0 Then _
strSearch = strSearch & IIf(Len(strSearch) > 0, " AND ", "") & "LotNo = " & Val(cboFindLotNo)

0
 
billcuteAuthor Commented:
PDB,
I will conduct a thorough test on your suggested amendment to my code and let you know later.

Regards
Bill
0
 
billcuteAuthor Commented:
puppydogbuddy,
Thanks for your innovative suggestion. I tested your suggestion, there was no compile error but the problem persist.

I then proceed to test both your code and my original one extensively using different scenerio and now believe that the opening of Me.DataEntry = True is causing this trouble. The form is more or less "locked" in dataentry mode and does not really reset the Dana Entry back to false from the other Event handlers.

So in order to resolve the problem, I rem out Me.DatEntry = True, then my original code and yours worked.

Regards
Bill
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now