Editing Recordsets after creating Unique numbers

Posted on 2005-03-29
Medium Priority
Last Modified: 2008-03-06
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.
Question by:billcute
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 38

Expert Comment

ID: 13664192
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.
LVL 38

Expert Comment

ID: 13667103
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

Author Comment

ID: 13667254

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.
LVL 38

Accepted Solution

puppydogbuddy earned 2000 total points
ID: 13667833
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


Author Comment

ID: 13668043
This is awesome. It works flawlessly. Thanks for the efforts.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question