Intermittent "Search Key was not found in any record"

[Morphies]

Morning all,

We are having an intermittent issue with our system

We have a main form where the completion details of a record are entered. We log a number of items one of which is a report number. This is a free text field as we denter duplicates on multiple records, but when a new number is required we have the following code to generate a new number from a seperate ReportNumber table:

Private Sub addNewId()
Dim strPrefix As String, varResponse As Variant

If Mid(Me.[Report No] & vbNullString, 3, 1) = "/" Then
    MsgBox "This Report No has already been generated", vbInformation + vbOKOnly
    Exit Sub
End If

If OnStop = True Then
    MsgBox "Client is set as On Stop. Report cannot be generated", vbInformation + vbOKOnly
    Exit Sub
End If

strPrefix = Format(Now(), "yy") & "/"
Me.[Report No] = nextIdString("ReportNo", "TBLREPORTID", strPrefix)

DoCmd.OpenForm "FRMREPORTID", acFormDS, , , acFormAdd, acHidden

Forms!frmreportid!ReportNo.SetFocus

strPrefix = Format(Now(), "yy") & "/"
[Forms]![frmreportid]![ReportNo] = nextIdString("ReportNo", "TBLREPORTID", strPrefix)

DoCmd.Close acForm, "frmreportid"




End Sub

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String) As Variant

    nextIdString = Nz(DMax("[" & nisFieldName & "]", nisTableName, "[" & nisFieldName & "] Like '" & nisPrefix & "*'"), nisPrefix & "0")
    ' nisPrefix & "0" gives you a default value if one is not found in the table

    nextIdString = Val(Mid(nextIdString, Len(nisPrefix) + 1)) + 1
    ' Get next numerical value by looking at the highest value number after the prefix and adding 1

    nextIdString = nisPrefix & Format(nextIdString, "00000") ' create next string Id
    ' Create new ID string by concatenating the formated number to te prefix
End Function

Now for some reason, somewhat intermittently, all of the order completion information is not stored in the database and the report number is blank. When you try and complete this information again it will store everything expect the report number, where it will throw the error "no search key was found in any record"

Any clues where to start diagnosing this?

TIA

Chris.

 

[Gasman]

Why do you run the NextID twice?
Being intermittent is a little harder, but, perhaps some logging to a table for those values for those subs/functions and then view them when it occurs again.?

If you can repeat the error when it occurs, then I would be walking through the code line by line.

Where do you update the last number used?

 

[Morphies]

HI Gasman,

You'll have to excuse me, I fumble my way through these things and this wasn't an area of our system I was involved in putting together.

The last number used is updated to tblreportId

The next number is called from a button on our main form:

Private Sub Command264_Click()
addNewId
If Me.Dirty Then
  Me.Dirty = False
End If
    Dim xlTmp As Excel.Application
    Set xlTmp = New Excel.Application
    Dim stDocName As String
    
If Customer = "BPC001" Then
If OnStop = True Then
Exit Sub
Else
Forms![job register and report log]![Report No].Requery



    stDocName = "Macro1"
    DoCmd.RunMacro stDocName
 
    If Text537 = "ISO 17025" Then
    xlTmp.Workbooks.Open "C:\RPTTMP\ISO17025\PDF Report.xlsM"
    xlTmp.Visible = True
 DoCmd.RunCommand acCmdAppMinimize
    Else
    xlTmp.Workbooks.Open "C:\RPTTMP\ISO19001\PDF Report.xlsM"
    xlTmp.Visible = True
    DoCmd.RunCommand acCmdAppMinimize
    End If

End If
End If
end sub

Not sure where i'm running nextid twice, and how would I go about adding some logging to a table?

TIA

Chris.

 

[Gasman]

Well I can only assume that some sub called AddNewID does that, as the rest of that code does not appear to be relevant.
If you can Degug.Print and walk through the code with F8, that would be the easist option, else you would create a new table to hold whatever tou think you need, strPrefix, Me.ReportNo etc

Here is where you apepar to call it twice

Me.[Report No] = nextIdString("ReportNo", "TBLREPORTID", strPrefix)

and then

[Forms]![frmreportid]![ReportNo] = nextIdString("ReportNo", "TBLREPORTID", strPrefix)

They should be the same if the field has not been updated.
What happens in a multi user environment?

I have to assume that [Forms]![frmreportid]![ReportNo] is bound to a ReportNo field?

 

[Morphies]

Hi Gasman,

This is a multi user environment, generally up to 6 people concurrently using the system

not sure on the duplicate, chat GTP suggested this tidied version

Private Sub addNewId()
    ' Check if the Report No has already been generated
    If Mid(Me.[Report No] & vbNullString, 3, 1) = "/" Then
        MsgBox "This Report No has already been generated.", vbInformation + vbOKOnly
        Exit Sub
    End If
    
    ' Check if the client is set as On Stop
    If OnStop = True Then
        MsgBox "Client is set as On Stop. Report cannot be generated.", vbInformation + vbOKOnly
        Exit Sub
    End If
    
    ' Generate the prefix for the new Report No
    Dim strPrefix As String
    strPrefix = Format(Now(), "yy") & "/"
    
    ' Assign the next available ID string to the Report No field in the main form
    Me.[Report No] = nextIdString("ReportNo", "TBLREPORTID", strPrefix)
    
    ' Open the FRMREPORTID form and assign the next available ID string to its Report No field
    DoCmd.OpenForm "FRMREPORTID", acFormDS, , , acFormAdd, acHidden
    Forms!frmreportid!ReportNo = nextIdString("ReportNo", "TBLREPORTID", strPrefix)
    DoCmd.Close acForm, "frmreportid"
End Sub

yes Forms]![frmreportid]![ReportNo] is bound to reportno field.

thanks,

 

[Gasman]

Ok, if nextIdString gets the next available number then that happens for whatever form you are on for Me.ReportNo using the line
Me.[Report No] = nextIdString("ReportNo", "TBLREPORTID", strPrefix)

Then you call it again for form Forms!frmreportid!ReportNo = nextIdString("ReportNo", "TBLREPORTID", strPrefix)

Now if the number has not been saved in between, then you will get the same number, but if it has then you will end up with an incremented number, so one could be different from the other. In a multi user system, you generally would get the number at the very last moment and commit it for the next time. Even then you could get a conflict.

Could that be the issue, where the reportNo on one form differs from that on the other form.

This is not an easy problem to track down. I would find where that error occurs and test for that error and display the key it is looking for. That should start you down the road to where it is all going wrong, if you have the errant value to start with.