Find and Replace ‘Form’ (Access 97 & 2000)

Topic

New Reply

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??

Reply | Quote

Replies

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
Else
DoCmd.Close acForm, "frmUpdate", acSaveYes
Exit Sub
End If
Wend
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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote