Avoiding runtime error if a record does not exist - HELP PLEASE

[luthrien]

Hi,

So i have a huge database that manages Mining Tenements (Titles)...

I have created a specific form with a drop down list of the list of tenements in the database. now i can either type or select the tenement then press the enter button and it brings up another form with all of the tenements details.... which works great.

but with this selection form. i usually type the tenement is as its a lot quicker then searching through a list of 3500... if i type in a number that does not exist in the database, i get a runtime error...

so my question is, what do i need to do to put a check inbetween pressing the button to load the new form to check if the tenement exits in the database and if it doesnt to bring up a message box to say so and go back to the selection form.

i know this probably seems easy to most of you guys.. but i'm learning VBA as i go along!! first time i've had to check for something like this..

your help is greatly appreciated

[NarenKeer]

Hi Luthrien,

Are you typing the tenement in a text box or something and trying to find it from a record set?

If you are just checking for a specific record in a record set, then please try the below:

rstRecSet.FindFirst "[Tenement_Field_Name] = "Tenement to be searched""

This would take you to the specific record where it appears first.

then use below to see the value from pointed record

rstRecSet.Fields(FieldNum).value

FieldNum - the position where the specific field(Tenement_Field_Name) appears in the record set

Note: If the Tenement that you are searching for is not found in the Tenement_Field_Name, then it wouldn't throw an error message, however the pointer stays in the first record of the recordset.

Thanks,
Naren

[luthrien]

naren,

thanks for your reply... basically the dropdown box is filled in with the tenement numbers from the table they are held in..

if i start typing then it auto-fills.. but sometimes i just continue typing and make a mistake and press the button to load hte form without even looking at what i typed... the recordset is already loaded in the drop down box...

[NarenKeer]

in that case, i think the best idea would be handeling it using afterupdate event of the combobox. Please see below for an idea

Private Sub cmbSearchTenement_AfterUpdate()
    On Error GoTo lblErr
    .
    .
    .
    Your Code that finds the record and display the next form
    .
    .
    .
    Exit Sub
lblErr:
    If Err.Number = 123 Then
       MsgBox "Please enter a valid tenement"
       NextForm.Visible = False
       CurrentForm.Visible = True
    Else
       MsgBox Err.Description & " - cmbSearchTenement_AfterUpdate"
    Endif
End Sub
 

as and when you get any error in the block of code between On Error statement and the Exit Sub statement, the control would come down to lblErr: part. Here you can handle all errors. I am just checking the error number of the specific error and showing a relevent message to the user. otherwise, if it is an unknown error, then i am showing the application generated error description to the user.

I use this sort of error handling in all functions and procedures I write. Showing function/Procedure name in the error message will make it easier when you want to know where an error occurs exactly.

Note: the Err.Number 123 is not the correct error number. Please check the error number for your error and use it.