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
'************
LVL 4
billcuteAsked:
Who is Participating?
 
puppydogbuddyConnect With a Mentor Commented:
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.