Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Find and Replace 'Form' (Access 97 & 2000)

    I've run into a rather unique issue which I will throw out for any assistance. I have developed a db for a client that tracks tools. These are tools that are used in the manufacturing area and consist of high dollar air and computer controlled hand tools. This db tracks several different items associated with the tooling, including:

    1. Tools Sold
    2. Demo tools - tools that are loaned for use by a company prior to potential purchase
    3. Tools Repaired - includes a detailed Invoice form that tracks what was repaired, costs, warranty credits, etc.
    4. Tool Repair History - allows tracking of all repairs for each particular tool. Tracks by Customer Name, Tool Make/Model, and Serial #.
    5. Company Information - Name, address, phone #'s, contacts, etc.

    The common denominator throughout the whole db is the Customer Name. It is used in each of the above tables / entry forms. As with most db's there are several related queries and reports. My situation (and need for help) is that the Customer's Name (Company Name) sometimes changes, for example, through a buy-out, or merger, company XYZ, may now become company ABC. The problem that is being encountered is that when the client changes the company name, all associated records become "orphaned". Since they are related to each particular tools serial number they are no longer accessible. If they change the Company name back to the original name (XYZ), everything returns to normal.

    I know of two ways, both of which are not "friendly" to the client's user (not a high level of computer confidence) to make the changes. The first is to open the underlying tables and use the REPLACE function to update the changed field entrees. The second is to enter the Company ABC as a new company (Company Info form) and change each of the related records to the new company. Once completed, the "old" company XYZ can be deleted.

    However, as mentioned, it would be best if the client's user didn't access the tables directly, and the time to change each related record individually is not acceptable.

    Here is where I need the help.... I would like to create (if possible) a Find / Replace / Update form, where the user would select the "old" company name from one combo box, select the "new" (change records to) company name from a second combo box and press an OK button to Find and Replace all related records in all of the underlying tables at one shot. Yes it would mean having to enter the changed Company Name as a "new" company, but being able to find and replace all of the records at one time would be an excellent method of achieving this task. So....any ideas??

  2. #2
    2 Star Lounger
    Join Date
    May 2001
    Thanked 0 Times in 0 Posts

    Re: Find and Replace 'Form' (Access 97 & 2000)

    Smoke Eater:

    I have had this problem in the past, though it had to do with employee names (sometimes they change their names, etc.)
    You can use this code by creating two forms: one that is hidden and uses the table in which you want to update as its
    recordsource. (i used frmUpdate in the example for this) The other is where you select the name you want to change
    and type in the new name. (i used frmChangeName in the example for this). on frmUpdate, the only field you need
    is the one you are updating (i used CustomerName in this example).

    this may not be the best or most efficient way to do this, but it has worked for me. you can also search the customer table and simply change the customer from one to the other instead of creating a new customer.

    Insert this code into an event on the frmChangeName form after you have selected the old name and provided the new name:

    DoCmd.OpenForm "frmUpdate", acNormal, , , , acHidden

    Insert this code into the frmUpdate form.

    Private Sub Form_Load()
    Dim totalrecords As Integer

    DoCmd.GoToRecord , , acLast
    totalrecords = Me.CurrentRecord
    DoCmd.GoToRecord , , acFirst 'this bit of code finds out how many records need to be searched

    If totalrecords > 0 Then
    While Me.CurrentRecord <= totalrecords
    Call changenames
    If Me.CurrentRecord < totalrecords Then
    DoCmd.GoToRecord , , acNext
    DoCmd.Close acForm, "frmUpdate", acSaveYes
    Exit Sub
    End If
    End If

    DoCmd.Close acForm, "frmUpdate", acSaveYes

    End Sub

    Private Sub changenames()

    'this will check all records in the table you are searching. if the CustomerName
    'matches the name you want to change, then it will change it to the new name. If
    'it does not match, it will simply skip that record and move to the next one

    Dim sourceform As String, sourcecontrol As String, ctl As Control
    Dim sourcecontrol2 As String, ctl2 As Control
    sourceform = "frmChangeName"
    sourcecontrol = "txtOldName" 'the field with the name you want to change
    sourcecontrol2 = "txtNewName" 'the field with the name you want to change the old one to

    Set ctl = Forms(sourceform)(sourcecontrol)
    Set ctl2 = Forms(sourceform)(sourcecontrol2)

    If CustomerName = ctl.Value Then CustomerName = ctl2.Value

    End Sub

    Let me know if you have any problems with this. HTH

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Sint Niklaas, Belgium
    Thanked 0 Times in 0 Posts

    Re: Find and Replace 'Form' (Access 97 & 2000)

    If you create your relationship with the relationship window, you can Enforce Referential Integrity and Cascade Update Related Fields and Cascade Delete Related Records. This will maintain the link between your tables.
    In a general manner of speaking, it's a very bad practice to set relation on names. That's why ID are used for. If you use ID(numbers) to set your relations you can change your names like you want without affecting the relationship.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 40 Times in 39 Posts

    Re: Find and Replace 'Form' (Access 97 & 2000)

    If you had enforced referential integrity with cascading updates between the company table and the other tables it is linked to, then changing the company name in the company table would have changed its name in all the related tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts