Data Control via VBA (1 Viewer)

[Zippersabrat]

I am creating a database to centralize the supply orders for an animal hospital. Currently I am working on the order list itself. Employees will be able to add items to the order list (the Item Name field is controlled via a separate Items table). Once an item is put on a purchase order the item is deleted off the order list. I have set the Item Name as a non-duplicating Index to prevent people from adding repeat items. However, I do not like the message given...many of the people at the hospital are scared of computers and I want more user friendly messages. I came up with the following code; however, it is flagging everything as being on the order list...can someone help, please!

Thanks a bunch!


Private Sub Item_Name_BeforeUpdate(Cancel As Integer)

Dim Variable As Integer

If Me![Item Name] = [Forms]![OrderList].[Item Name] Then
Variable = MsgBox("This item is already on the order list!", vbExclamation + vbOKOnly, "Oops")
Me.Undo

Else


End If


End Sub

 

[John Big Booty]

Try;

Private Sub Item_Name_BeforeUpdate(Cancel As Integer)



If Me![Item Name] = [Forms]![OrderList].[Item Name] Then
     MsgBox("This item is already on the order list!", vbExclamation + vbOKOnly, "Oops")
     Me.Undo
     Cancel = True

End If


End Sub

 

[Zippersabrat]

This has the same issue of labeling everything as already being on the order list.

 

[Zippersabrat]

I finally figured this out...this is what I used.



Private Sub Item_Name_BeforeUpdate(Cancel As Integer)

'Create Recordset of items on order list and then check if new item is already listed
Dim Msg As Integer
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim ToBeOrdered As New ADODB.Recordset
ToBeOrdered.ActiveConnection = cnn1

ToBeOrdered.Open "SELECT [Item Name] FROM OrderList"

While ToBeOrdered.EOF = False

If Me![Item Name] = ToBeOrdered.Fields("Item Name") Then

Msg = MsgBox("This item is already on the order list!", vbExclamation + vbOKOnly, "Oops")
Me.Undo
Cancel = True

End If

ToBeOrdered.MoveNext

Wend

ToBeOrdered.Close
cnn1.Close

Set ToBeOrder = Nothing
Set cnn1 = Nothing

End Sub