Editing Recordsets after creating Unique numbers

The link below is my initial starting point of my post. I want to be able to edit the Recordset (in a Main / Subform / SubForm) DB, generated from codes in the referenced post below:


When a user changes any of the 4 most important fields, there must be an update that
       does the folowing:
      (a). UpDate the JobNo to reflect the changes made then,
      (b). Regenerate ALL JobNos in the tblTaps to ensure JobNos
           are in sequence.
      (c). UpDates (a) and (b) should be performed simultaneously.
            If possible, Lock the Database and then send message across the
            Network that an "UPDATE is being performed, please wait...)
      (d). When UPDATES are completed, the Message will disappear or another
            message informs ALL users "Update Finished"

There are two parts here to the JobNos issue
(A). Creating New JobNo (currently being addressed in another post cited above)
(B). Just before a new JobNo is created, code could "Do"
     (a)   FindFirst if Like Operant Exist, if found "Do" Record Count and MsgBox to inform
           User number of Like Operants found..Do you still want to add new JobNo?
     (b). If the answer is Yes, Add new JobNo - Increment +1
     (c). If the answer is "No", enable the cmdSearch button for User to search for "Like Operants"
           matching the "current JobNo the Users is about to create".

     A "ListBox" to display the "Like Operants" might be a good idea when cmdSearch is
     clicked. Then, double-clicking on any of the displayed "JobNo" should open another form
     (Pop-Up type) to reveal the details of each JobNo and the associated data from tblTaps
If the JobNo already exists (excluding the sequence number and date), either in New JobNos Mode or in the editing Mode, I want to prompt the user to see if he wants to create another JobNo described above.
Further Notes In the "Editing mode":
If after creating the JobNo, on the same day or some other day, User changes his mind and wish to make changes to any of the four fields, that's fine, but upon saving the data or when users tabs to the next field, there should be a message warning user, that fields changes would reflect in changes to the JobNo, Do you still want to Continue Yes/No. If he clicks Yes, then RUN Update query one after the other to update tblTaps and to any recordset the changes could affect including Regenerating all JobNos in the table making sure all numbers in the table increment accordingly and in a Unique manner.

Please note that the code for this particular post may not be possible untill solution for the above cited post has been achieved. This post is essentially part 2 of the first. Any help will be appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I will look into this in light of what you have implemented from the requirements you set out in your previous post and get back to you.
I have taken a second look at your posting and I got an idea on how to help but before I suggest anything, I need some more clarifications:
1. I have the impression from what you describe that you do not need the JobNo to be unique in the table but only unique for a certain address in the table. Is this correct?
2. If one or more of the four key values are changed, do you want to:
   a. Change the JobNo but keep the same sequence number.
   b. Change the JobNo and assign a new sequence number, then reassign sequence numbers to all JobNos for that address, starting from 01.
                                     Puppy Dog
billcuteAuthor Commented:

Yes, you can interprete the uniqueness based on the four fields
definition in the table. Each BlockNo and LotNo specifically
belong to a specifi property. For example:

BlockNo     LotNo      HouseNo     StreetName
11234         23        101-22        10th Street
143            101        92             4th Avenue

The above example shows that the BlockNo / LotNo combination
identify each property. You dont need the HouseNo / StreetName
to locate a property once you know it's combination of BlockNo / LotNo.

So, it is unique sort of. The main point here is "incremental value"
can be re-assigned if any of the four fields change and the JobNos re-aligned and increment re-assigned based on the changes.

2. If one or more of the four key values is changed, I want to:
   b. Change the JobNo and assign a new sequence number,
      then re-assign sequence numbers to all JobNos for that
      address, starting from 01.
Try this:
Option Compare Database
Option Explicit
Dim NewDate As String
Dim UniqueID As String
Public Function DuplicateRecordSearch()
On Error GoTo Error_Routine
Dim strJobNo As String
Dim strFieldName1 As String
Dim strFieldName2 As String
Dim strFieldName3 As String
'Dim strTableName As String    'now a global variable
'for debugging purposes
'Debug.Print strSQL
Dim cn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim StrSQL As String
Dim cnt As Integer
'get copy of the current connection
Set cn = CurrentProject.Connection

'beginning of search procedure which will do search for like operants
strFieldName1 = "[JobNo]"
strFieldName2 = "[SNID]"
strFieldName3 = "[zOperant]"
'set up sql string to extract records from strTableName based on operant value passed from form
StrSQL = " SELECT " & strFieldName1 & "," & strFieldName2 & "," & strFieldName3 & " FROM " & "qryLikeOperant"
StrSQL = StrSQL & " WHERE " & strFieldName3 & " Like '" & (subfrm("txtOperant")) & "'"

'open a recordset to do the search to determine if possible duplicate records exist
rst.Open StrSQL, cn, adOpenKeyset, adLockOptimistic
With rst
cnt = Nz(DCount((strFieldName3), "qryLikeOperant", strFieldName3 & " Like '" & (subfrm("txtOperant")) & "'"), 0)
If cnt > 0 Then   'likeOperants (potential duplicates) exist
    'let user decide if he wants to create a new job or cancel after viewing like operants
    Dim varContinue As Variant
        If varContinue = vbYes Then
            'want to create a new job request without checking existing job requests
            GenerateJobNo   ' regen existing jobs before assigning number to current job
            GoTo Exit_Continue
            'user wants to view the potential duplicate records before making a decision about the current job request
             subfrm("JobNo") = "Under Review"
             DoCmd.OpenForm "frmListBox", acNormal, , , acFormEdit, acWindowNormal
        End If
   'there are no potential duplicates meeting the search criteria;issue a jobno to current job
    GoTo Exit_Continue
End If
End With
'close recordset and clear variables
Set rst = Nothing
        Exit Function
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Function


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billcuteAuthor Commented:
This is awesome. It works flawlessly. Thanks for the efforts.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.