Prevent data entry if table contains record with same field value

[skwilliams]

I have a table (tblOOS) that contains fields (Stock) and (Status) as well as others. I also have a data entry form to enter a new record.

I would like to prevent the data entry if a record with that stock number already exists and the status is not "Complete". If the status is "Complete" then the stock number can be entered again.

Any ideas??

 

[pbaldy]

This is cut out of a function, but perhaps gets you started?

  strSQL = "SELECT RONum FROM tblRONumbers WHERE car_no = " & CarNum & " AND ROStatus <> 'Completed'"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If rs.EOF Then
    CheckForOpenRO = "None"
  Else
    CheckForOpenRO = rs!RONum
  End If

 

[skwilliams]

That didn't seem to do anything but error out. The debug tells me that the recordset is empty.

 

[pbaldy]

What does your code look like? Mine was just the relevant portion; it wouldn't stand alone.

 

[skwilliams]

Private Sub Stock_BeforeUpdate(Cancel As Integer)
Dim strSQL As String
Dim rs As Recordset

strSQL = "SELECT [Stock] FROM [tblOOS] WHERE [Stock] =" & [Stock] & " AND [Status] <>'Complete'"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.EOF Then
CheckForDup = "None"
Else
CheckForDup = rs!Stock
End If

Set rs = Nothing

End Sub

 

[pbaldy]

Like I said, mine was cut out of a function, so you can't keep the part setting the function value. You also haven't declared or set the db variable. How about

If Not rs.EOF Then
  MsgBox "Whatever you want to say"
  Cancel = True
End If

 

[skwilliams]

I was able to resolve this issue by requiring a stock selection from the combo box then filtering the combo box list this way.

SELECT tblOOSStocks.STOCK, tblOOSStocks.TYPE
FROM tblOOSStocks
WHERE (((Exists (SELECT qryStockStatus1.Stock FROM qryStockStatus1 WHERE qryStockStatus1.Stock = tblOOSStocks.STOCK))=False));

qryStockStatus is a list of stocks not completed.