- Joined
- Apr 17, 2012
- Messages
- 1
- Reaction score
- 0
Hi all,
I'm working on a database to manage work orders on defective equipment. Each defect has a RODL ID which identifies the particular peice of equipment, but crucially may be reused for multiple work orders if the defect persists or causes additional problems.
Because multiple people are likely to be working with this database, I'm trying to set it up so that new work orders can be added with the RODL ID entered by hand in the appropriate field in the front end form with the user notified if that RODL ID is already in use, and if so display the work orders currently associated with that ID so the user can check to see if they are entering a work order that is already on the system.
I'm pretty green at all this. So far I have a duplication search setup by pinching code from this thread: https://www.pcreview.co.uk/forums/before-update-event-check-duplicate-data-field-t3828067.html I'm not sure how to go about augmenting this to report back on the specific instances of RODLID that it is finding in the system. Am I right in suspecting that it'll probably require the rewriting of the original code to move away from using IsNull?
If so, how do I collect that data and display it? I'm thinking it would probably be best to have a second form pop up over the form i'm using as a front end, display brief info about the existing records and offer a button to press to navigate the front end form to the record in question.
Thanks in advance!
edit: Just to make things simple, here's the code as I'm using it currently:
Private Sub RODLID_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[RODLID]", _
"DefectTable", _
"[RODLID] = """ & Me.RODLID.Text & """")) = False Then
Cancel = True
MsgBox "RODL already exists", vbOKOnly, "Warning"
Me![RODLID].Undo
End If
End Sub
I'm working on a database to manage work orders on defective equipment. Each defect has a RODL ID which identifies the particular peice of equipment, but crucially may be reused for multiple work orders if the defect persists or causes additional problems.
Because multiple people are likely to be working with this database, I'm trying to set it up so that new work orders can be added with the RODL ID entered by hand in the appropriate field in the front end form with the user notified if that RODL ID is already in use, and if so display the work orders currently associated with that ID so the user can check to see if they are entering a work order that is already on the system.
I'm pretty green at all this. So far I have a duplication search setup by pinching code from this thread: https://www.pcreview.co.uk/forums/before-update-event-check-duplicate-data-field-t3828067.html I'm not sure how to go about augmenting this to report back on the specific instances of RODLID that it is finding in the system. Am I right in suspecting that it'll probably require the rewriting of the original code to move away from using IsNull?
If so, how do I collect that data and display it? I'm thinking it would probably be best to have a second form pop up over the form i'm using as a front end, display brief info about the existing records and offer a button to press to navigate the front end form to the record in question.
Thanks in advance!
edit: Just to make things simple, here's the code as I'm using it currently:
Private Sub RODLID_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[RODLID]", _
"DefectTable", _
"[RODLID] = """ & Me.RODLID.Text & """")) = False Then
Cancel = True
MsgBox "RODL already exists", vbOKOnly, "Warning"
Me![RODLID].Undo
End If
End Sub
Last edited: