I have a navigation page with several forms attach. I am trying to develop a search/edit/update button where a user can put in a number to search if a record exists. If it already exists update form with record information so edits can be made. Once edits have been completed, update the record in the table and save entry. I have started by checking to see if the user has enter any information in the text box with the following code:
'Search for Record to Edit and Update
Private Sub cmdSearch_Click()
Dim strAssessionNumber As String
Dim strSearch As String
'Check txtSearch for Null value or Nill Entry First
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter Assession Number!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
End Sub
Here if the user failed to put in a number, the message box fires and reminds the user to enter data. This works, now I would like to search the table for a field AssessionNumber that the user places in the text box. If it exists populate the form with the record so changes can be made. If record does not exist allow user to input a new record. I found this code and have tried it in several locations in the cmdSearch_Click() with no luck.
If DCount("*",tblDelay","[AssessionNumber] =#" & Me.[txtSeach])> 0 Then
MsgBox "This record exists, please update record.
Cancel = True
End If
Here is a view of my form and tblDelay includes field MRN, AssessionNumber, CaseNumber, and several other fields. I think if I can get this working I can add the remainder fields. Once record has been updated I need to save the update.
Thanks again in advance,
Kerry