Solved "Record not found" Prompt (1 Viewer)

[Newbie_me]

Hi Guys,

What do i need to add from below codes in order to prompt "Record not found" in case of no match on the database? I feel its an IF then Else statement but dont know how to apply

-------------------------------------------------------------------------------------------------------------------------------------

Private Sub btnCompoSearch_Click()

Dim SQL As String

SQL = " SELECT T_Parts.Component, T_Parts.[Machine1], T_Parts.[Machine2], T_Parts.[Machine3] FROM T_Parts WHERE [T_Parts.Component] = '" & Me.txtCompoKeyword & "' "


Me.SubComponentSearch.Form.RecordSource = SQL

Me.SubComponentSearch.Requery


End Sub

 

[MajP]

maybe create a recordset from the sql. If it is at end of file means not records .

Private Sub btnCompoSearch_Click()
  Dim SQL As String
  dim rs as dao.recordset
  SQL = " SELECT T_Parts.Component, T_Parts.[Machine1], T_Parts.[Machine2], T_Parts.[Machine3] FROM T_Parts WHERE     [T_Parts.Component] = '" & Me.txtCompoKeyword & "' "
  set rs = currentDb.openrecordset (SQL)
  if not rs.eof then
    Me.SubComponentSearch.Form.RecordSource = SQL
    me.SubComponentSearch.Requery
  else
    msgbox "Message here"
  end if

End Sub

 

[Newbie_me]

maybe create a recordset from the sql. If it is at end of file means not records .

Private Sub btnCompoSearch_Click()
  Dim SQL As String
  dim rs as dao.recordset
  SQL = " SELECT T_Parts.Component, T_Parts.[Machine1], T_Parts.[Machine2], T_Parts.[Machine3] FROM T_Parts WHERE     [T_Parts.Component] = '" & Me.txtCompoKeyword & "' "
  set rs = currentDb.openrecordset (SQL)
  if not rs.eof then
    Me.SubComponentSearch.Form.RecordSource = SQL
    me.SubComponentSearch.Requery
  else
    msgbox "Message here"
  end if

End Sub

I will try it later

 

[MajP]

You can also use a dcount on T_parts with the criteria

 

[Newbie_me]

I will try it later

It worked. Thank you so much! I really appreciate it Have a great day. . . . .

 

[Newbie_me]

maybe create a recordset from the sql. If it is at end of file means not records .

Private Sub btnCompoSearch_Click()
  Dim SQL As String
  dim rs as dao.recordset
  SQL = " SELECT T_Parts.Component, T_Parts.[Machine1], T_Parts.[Machine2], T_Parts.[Machine3] FROM T_Parts WHERE     [T_Parts.Component] = '" & Me.txtCompoKeyword & "' "
  set rs = currentDb.openrecordset (SQL)
  if not rs.eof then
    Me.SubComponentSearch.Form.RecordSource = SQL
    me.SubComponentSearch.Requery
  else
    msgbox "Message here"
  end if

End Sub

It worked. Thank you so much! I really appreciate it Have a great day. . . . .