If AREA ID not found in database need popup msg

[ganesh_6663]

Hi Team,
Below code need to add condition If AREA ID not found in database need popup msg showing "AREA ID not preset in Database2.accdb"

Private Sub CommandButton5_Click()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim qry As String, i As Integer
Dim n As Long


If Me.Reg1.Value = "" Then
        MsgBox "Please enter the AREA ID", vbCritical
        Exit Sub
    End If

    
qry = "Select * from DATABASE123 where Data='" & Me.Reg1.Value & "'"
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Database2.accdb"
rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

Reg1 = Me.Reg1.Value
Me.Reg2.Value = rst.Fields("a1").Value
Me.Reg3.Value = rst.Fields("a2").Value
Me.TextBox6.Value = rst.Fields("a3").Value


rst.Close
cnn.Close
            
End Sub

 

[Micron]

Reg1 is the variable for the Area ID value? Then perhaps do a DLookup on the table using Reg1 value as criteria. If not found, the return value of DLookup will be 0.
I was going to suggest getting the record count, but this is an ADO recordset, thus you'd have to deal with the real possibility that the count value is -1.
So maybe

If DLookup("Data", "Database123", "Data='" & Me.Reg1 & "'") = 0 Then
  Msgbox "Area ID not found. Exiting now.
  Exit Sub
End If

If you don't want to stop the procedure, modify the result code to do something else when the DLookup returns 0.

 

[ganesh_6663]

Think you Micron Sir....
getting below error while exciting code

Private Sub CommandButton5_Click()
'Error for get data button******************
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim qry As String, i As Integer
Dim n As Long
Dim varX As Variant


On Error GoTo errHandler:

If Me.Reg1.Value = "" Then
        MsgBox "Please enter the SAD ID", vbCritical
        Exit Sub
    End If
    

    
qry = "Select * from DATABASE123 where Data='" & Me.Reg1.Value & "'"
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Database2.accdb"
rst.Open qry, cnn, adOpenKeyset, adLockOptimistic


Reg1 = Me.Reg1.Value
Me.Reg2.Value = rst.Fields("a1").Value
Me.Reg3.Value = rst.Fields("a2").Value
Me.TextBox6.Value = rst.Fields("a3").Value


If varX = DLookup("Data", "Database123", "Data='" & Me.Reg1 & "'") = 0 Then
  MsgBox "Area ID not found. Exiting now."
  Exit Sub
End If



'lsmrtxt = rst.Fields("lsm_name").Value
'neidtxt = rst.Fields("sys_alias").Value
'oamiptxt = rst.Fields("Onm_IP").Value
'gwiptxt = rst.Fields("GW_IP").Value
'rsiptxt = rst.Fields("RS_IP").Value
'nodetxt = rst.Fields("Node_type").Value

'Me.scriptenbTextBox3.Value = neidtxt
'Me.scriptgwipTextBox6.Value = gwiptxt
'Me.scriptlsmrTextBox7.Value = lsmrtxt
'Me.scriptoanTextBox5.Value = oamiptxt
'Me.scriptrsipTextBox4.Value = rsiptxt
    
    
errHandler:
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CommandButton5"
            
rst.Close
cnn.Close
            
End Sub

​

 

[Micron]

I had to guess what your domain (table or query) name is, what your field name(s) is/are so you have to adapt, not just use what I post. You might want to state what the error number and message is when you're reporting that you get an error?

 

[ganesh_6663]

Thanks for reply ...
below code working

If rst.RecordCount = 0 Then
  MsgBox Reg1 & Chr(10) & "Area ID Not present in Database", 48, "Area ID Not Found"
  Exit Sub
End If

 

[Micron]

By working you mean you were able to raise that message box when the Area ID was invalid?

 

[ganesh_6663]

message box raise when Area ID not found in database.