How to update if data in table is changed?

I have a checkout form with two unbound combo boxes- frame ID and Project Name, and a button - Assign Frame.

The Assign Frame button has the following working code to update the StockFrames table:

Private Sub AssignFrame_Click()

CurrentDb.Execute "UPDATE StockFrames SET ProjectID = " & Me.Project & _
      " WHERE FrameID = " & Me.FrameID

Me.Project.Value = Null
Me.FrameID.Value = Null
End Sub

I want a message box to appear if a user tries to assign an unavailable frame to a project: "This frame is currently assigned to another project. Are you sure you want to override? Yes/No"

I know this needs to be an if/then statement involving something with StockFrames.ProjectID <> Null.

Also, should this be a BeforeUpdate event or better suited somewhere else?

Solution

Use DLookup or DCount to first check if frameID is already in use. Code can go in Click event.

If IsNull(DLookup("ProjectID", "StockFrames", "FrameID=" & Me.FrameID)) Then
    CurrentDb.Execute ...
Else
    MsgBox "your message here"
End If

A better option may be combobox RowSource that does not include FrameID values already in use so user can't even choose them.
SELECT FrameID, FrameDesc FROM StockFrames WHERE ProjectID Is Null;
Then requery combobox in Click event.
Be aware, conditional combobox does not work nice with bound form in Continuous or Datasheet view but since you appear to be using an UNBOUND form, that should not be an issue.