Search - Missing Number

[CumbrianCanary]

I have tried to search for an answer to this but no no avail! Please could someone help?

I have a start-up form, which has a combo box search feature which opens up a new form with the user defined record. As well as providing all present numbers users can type in their own number. The number it is searching is an AutoNumber. I tried to put in some error messages if the user selected a number greater than the maximum value using DMax

If cboSearch > intMax Then
    MsgBox "Not there", vbCritical + vbOKOnly, "Does not exist!"
    Exit Sub

This works really well. The problem I am having is that if entries have been deleted then there is a gap in the numbering, which in itself isn't a problem. But if the user enters a number not present then it just opens the correct form but with the first record. Is there a way of bringing up a message box informing the user that the value they have entered does not exist in the form they are searching?

Many thanks,

CC

 

[Matt Greatorex]

I assume the record numbers are stored in a table? If so, you can use DCount to see if that number is already present. If it isn't, you can skip the open part. For example

If DCount("some field from the table","table name","[record number] = " & {entered number}") > 0 Then

open report

else

msgbox "No such record"

end if

Would that help?

 

[CumbrianCanary]

Thanks Matt, that has worked a treat!

 

[Matt Greatorex]

Thanks for letting me know.
Glad to help.