How to show Msg if Record not found in table

[Timmm]

I am new to VBA and would appreciate some help. I am trying to develop a spreadsheet which I want staff to use, in order to track the status of several thousand customer survey forms as they are returned. Each customer has a unique Cust_id. I have designed a Userform to allow easy recording of info for each customer.

The form will display relevant info for each valid Cust_id entered, however I can't get it to show "Record Not found" if no record is found matching the Cust_id entered. I only want it to appear after they have entered a Cust_id in Textbox1 and pressed Enter. I want the form cleared after each Search and display. This is my code:

Private Sub TextBox1_Enter()

Dim cust_id As String
cust_id = Trim(TextBox1.Text)
lastrow = Worksheets("Main").Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To lastrow

If Worksheets("Main").Cells(i, 3).Value = cust_id Then
TextBox2.Text = Worksheets("Main").Cells(i, 5).Value
CmbSFStatus.Text = Worksheets("Main").Cells(i, 11).Value
CmbComment.Text = Worksheets("Main").Cells(i, 15).Value
TextBox3.Text = Worksheets("Main").Cells(i, 18).Value
TextBox4.Text = Worksheets("Main").Cells(i, 6).Value
TextBox5.Text = Worksheets("Main").Cells(i, 9).Value
TextBox6.Text = Worksheets("Main").Cells(i, 12).Value

End If
Next

rem Clear form

TextBox1.Text = ""
TextBox2.Text = ""
CmbSFStatus.Text = ""
CmbComment.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""

TextBox1.SetFocus

End Sub

Any assistance would be greatly appreciated.

 

[SanjayGMusafir]

I'm an absolute novice...

Before End If try adding these two lines, though not sure it has to MsgBox or else...

Else
MsgBox "Record Not found"

And see if it works for you...

 

[myall_blues]

For starters if you want to enter a value and press ENTER, then you don't want a TextBox_Enter event. Textbox_Enter is activated when you put the cursor inside the textbox (i.e when the textbox gets focus). Instead you need to put a button on your form so that the user can enter the customer id and then click the button when they are ready to search.

You then need to set some flag (probably a boolean variable) to check while inside your loop to see if a value has been found or not, and give an appropriate message if not.

For example (untested)

Private Sub cmdButtonOK_Click()
    Dim cust_id As String
    Dim found As Boolean
    
    cust_id = Trim(TextBox1.Text)
    lastrow = Worksheets("Main").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 3 To lastrow
        If Worksheets("Main").Cells(i, 3).Value = cust_id Then
            found = True
            TextBox2.Text = Worksheets("Main").Cells(i, 5).Value
            CmbSFStatus.Text = Worksheets("Main").Cells(i, 11).Value
            CmbComment.Text = Worksheets("Main").Cells(i, 15).Value
            TextBox3.Text = Worksheets("Main").Cells(i, 18).Value
            TextBox4.Text = Worksheets("Main").Cells(i, 6).Value
            TextBox5.Text = Worksheets("Main").Cells(i, 9).Value
            TextBox6.Text = Worksheets("Main").Cells(i, 12).Value
            Exit For
        End If
    Next
    If Not found Then MsgBox "Customer ID " & cust_id & " not found"
    '
    ' Rest of code
    '
End Sub

As an alternative to the MsgBox you could add a label to the form and update the text of the label if the customer ID is not found, which is probably neater.

Regards

Murray

 

[myall_blues]

@SanjayGMusafir your method would work, but because it is inside the For-Next loop, then for every value of i where the cust_id is not found, it will trigger the MsgBox. In other words, if you have 100 records and cust_id is only in one of them, then you would need to click OK on the MsgBox for the other 99 times.

Regards

Murray