BUTTON RECODE TO COMPARE

[Guest]

IS there anyway that I can add code to an existing SAVE RECORD button. What
I’d like to add is a way to compare the Work Order: number to the Work Order
number on another table (Short Sheet). If there is no march, no popup
dialogue will appear and it would just save the record. If there is a hit on
the Short Sheet table, it will have a popup dialogue that says “This work
order appears on the Short Sheet report.â€.

I am terrible at code and I’ve never created a popup dialogue from scratch.
So I am unable to make it work myself. Can anyone help? Below I have the code
for the SAVE RECORD button that I’d like to rework.


Code for SAVE RECORD button

Private Sub SaveRecord_Click()
On Error GoTo Err_SaveRecord_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_SaveRecord_Click:
Exit Sub

Err_SaveRecord_Click:
MsgBox Err.Description
Resume Exit_SaveRecord_Click

End Sub

 

[Guest]

Please ignore this in favor of the new post. thanks.

 

[John Smith]

Something along these lines, but beware, air-code!

Private Sub SaveRecord_Click()
Dim SQL as String, ShortSet as Recordset
On Error GoTo Err_SaveRecord_Click
SQL = "SELECT Count(*) As Shorts FROM ShortSheet" _
& " WHERE WorkOrder = " & WorkOrder
Set ShortSet = CurrentDb.Openrecordset(SQL, dbOpenSnapshot, dbFailOnError)
With ShortSet
.MoveFirst
If !Shorts > 0 then
MsgBox "This work order appears on the Short Sheet report.", _
vbInformation, "Duplicate Entry"
Else
Me.Dirty = False ' Saves the record
End If
.Close
End With

Exit_SaveRecord_Click:
Set ShortSet = Nothing
Exit Sub

Err_SaveRecord_Click:
MsgBox Err.Description
Resume Exit_SaveRecord_Click
End Sub

I have assumed that the WorkOrder column is a Number, if it is Text you will
need to add quotes when building the SQL string:

& " WHERE WorkOrder = '" & WorkOrder & "'"