I am currently working on a database for tracking equipment and equipment maintenance and I am currently having some issues on a form. On a form named frmEquipTrack I have a button called btnAddMfr. The user will be shown a dialog box that prompts them to add a new manufacture to the database when they click the button. The user will input the name of the new manufacture and when they click ok on the dialog box, the manufacture is added to a table called tblMfr.
I have experienced some success with getting this done. When the code is:
Code:
Private Sub btnAddMfr_Click()
Dim Message, Title, strMfr
Dim dbsMfr As DAO.Database
Dim rstMfrName As DAO.Recordset
Set dbsMfr = CurrentDb
Set rstMfrName = dbsMfr.OpenRecordset("tblMfr")
Message = "Please Add New Manufacture"
Title = "Add Manufature"
strMfr = InputBox(Message, Title)
rstMfrName.AddNew
rstMfrName("Mfr").Value = strMfr
rstMfrName.Update
End Sub
everything works fine and I am able to add the string that is entered by the user to the tblMfr table.
However, I quickly realized that I could have duplicate entries since I wasn't checking the tblMfr table to see if that manufacture was already on the list. So i decided to try and search the record set and check if the string the user entered into the dialog box was already on the tblMfr table. I changed my code to:
Code:
Private Sub btnAddMfr_Click()
Dim Message, Title, strMfr
Dim strTest As String
Dim dbsMfr As DAO.Database
Dim rstMfrName As DAO.Recordset
Set dbsMfr = CurrentDb
Set rstMfrName = dbsMfr.OpenRecordset("tblMfr")
Message = "Please Add New Manufacture"
Title = "Add Manufature"
strMfr = InputBox(Message, Title)
With rstMfrName
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
strTest = rstMfrName!Mfr
If (StrComp(strMfr, strTest, vbTextCompare)) = 0 Then
MsgBox "Mfr Already In Database", vbOKOnly, "Warning"
Else
rstMfrName.AddNew
rstMfrName("Mfr").Value = strMfr
rstMfrName.Update
End If
.MoveNext
Wend
End If
.Close
End With
ExitSub:
Set rstMfrName = Nothing
Exit Sub
ErrorHandler:
Resume ExitSub
End Sub
and now the message box that should warn me that the manufacture that was just entered on the table pops up four times even if the manufacture isn't already on the table. Then I have four new record on the tblMfr table when I refresh the tblMfr table. I think my mistake is in my while loop but I am not sure what that might be.