A problem related to a dialog box/input box that i created

[Betsvigi9]

Hello,
I am working on a database where i have created a dialog / input form to allow users to enter a key into a textbox to view particular data on a display form. I want the db to check the key entered and see if it matches the key already stored in a existing table if not then i want it to display an error msge. I am new to vba. Please can ne one look at the code and let me knw what i need to add or remove.

BELOW IS THE CODE FOR THE "OK" BUTTON AFTER THE USER WILL ENTER THE KEY(EMPNO)ON THE DIALOG/INPUT FORM. DB WILL USE THIS FORM TO MATCH KEY.

Private Sub Command2_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strEmpNo As String

strEmpNo = txtInsertEmp


Set db = CurrentDb()
Set rst = db.OpenRecordset("tblCaretakers")

rst.MoveFirst
Do Until rst.EOF
With rst
If !EMPNUMB = strEmpNo Then
DoCmd.OpenForm "CaretakerDetails"

Else
.MoveNext
End If
End With
Loop

Set rst = Nothing
Set db = Nothing
End Sub

BELOW IS THE CODE FOR THE "FIND EMPLOYEE CODE" BUTTON THAT WILL DISPLAY THE ABOVE DIALOG/INPUT FORM THAT IS GOING TO DISPLAY THE DATA THAT MATCHES THE KEY WHICH IS ENTERED BY USER.

Private Sub CmdFindCode_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strCode As String


Const vbinformation = 64

On Error GoTo CmdFindErr

DoCmd.ShowAllRecords




DoCmd.ApplyFilter "", "[tblCaretakers]![EMPNUMB]=[Enter Employee No]"
'DoCmd.OpenForm "DialogueEnterEmpNo"

CmdFind_Click_Exit:
Exit Sub

CmdFindErr:
Select Case Err
Case 2501 'Find Course Reference Action Cancelled
MsgBox _
"You Have Cancelled Finding a Employee by its No", _
vbinformation, "Action Cancelled"
Case Else
MsgBox "Error: " & Error & " (" & Err & ")"

End Select
Resume CmdFind_Click_Exit
End Sub

Can someone tell me what is wrong with this code and what i need to do make the db to check what is entered in the text box and match it with the key in a table. If a incorrect key is entered i want it to display a error msge.

I would be so grateful if ne one can help me on this one.

THANKYOU!! (",)

 

[pbail1]

There are a couple of ways of doing this.

First is to use the dlookup function to see if there is a record with that ID in the table.

EG:

If isnull(dlookup("empnumb","TBLCaretakers", "Empnumb = " & strempno) then

  msgbox "Employee Does Not exist

else

{Carry on with your code}


end if

Or you can create a recordset and check to see if it is empty:


For example

Set rst = db.openrecordset ("SELECT * FROM TBLCaretakers where empnumb = " & strempno & ";")

If rst.bof then

   msgbox "Employee does not exist"

else 

{Rest of code here}


end if

 

[Remou]

To add to pbail1's post, you can open a form with a Where statement that will limit the records to those found. I think your code should probably look a little like this:

Private Sub Command2_Click()
'It is usually best to indicate what type of data can
'be expected by the prefix, str=string,int=integer 
'and so on.
'
strEmpNo = txtInsertEmp

'Assuming that EmpNumb is a number
If IsNull(DLookup("EmpNumb","tblCaretakers", "EmpNumb = " & strEmpNo)) Then
      MsgBox "Employee Does Not exist
Else
      DoCmd.OpenForm "CaretakerDetails",,,"Empnumb = " & strEmpNo
End If
End Sub

Private Sub CmdFindCode_Click()
     DoCmd.OpenForm "DialogueEnterEmpNo"
End Sub