Find and replace

[cbuechner]

Somewhat new to this, but learning.

I built a complex database, and due to the size, I have to create lots of tables, that are joined by a common "ID" and "Serial_Number". In some cases, the serial number may change. So I built form based on a query. When I open it, I enter the serial number and it will show me the serial number from all of the tables.



I want to have a button that will open the find and replace prompt set the lookin "Current document". I have found some ideas on other post, but none that appears that are set to lookin the current document.

A different way, or ideally, I would have two textboxes, that I can enter in the old value (textbox1), and the new value (textbox2) , and hit a button to make the changes.

[cbuechner]

The form is called "Rename_Serial_Number". I was able to set up a onclick update

Private Sub Serial_Number_Serial_Number_Click()
Me.Serial_Number_Serial_Number = Me.NEWSN
End Sub

This may be the best option, but not sure if I want any accidental clicks.

[Micron]

The easiest way to prevent an accidental click is to first present a message prompt asking for confirmation. Would likely soon become an annoyance, so the next best option might be to provide for an undo operation. Your comment about lots of tables that seem similar indicate to me that your db is not normalized at all. I'd bet your money on it. You might want to research db normalization and see if I'm right, because if I am, you are going to struggle all the time.

[cbuechner]

I am very certain it is not a normalized database. It is only the 2nd database I created. The problem I had was I could not fit all of the columns (fields) into one table. I then created something like this...

New record in the primary table, would append (ID, Serial_Number) to all of the other tables. When I need the data, I ran a query and connected the relationship (LEFT JOIN) from the ID with the Serial_Number table being the primary. Which lead to this other problem, that if I had to change the serial number, I would have to change the serial number on all of the tables at the same time.

I ended up using the onclick event for each field in the form.

Thank you!

[Micron]

Unless you have a very compelling reason to not normalize it (like a boss who says "don't") then you really ought to stop now and fix that problem. As you noted, changing something is an issue and how can you be certain that it propagated everywhere unless you visually check each record in each table? That situation is untenable and it's only going to get worse IMO.

[orange]

As Micron suggested, if your database is not normalized, you'll be in a continuous process of work arounds.

Tables and relationships are based on your business and its rules/facts. Tables are not just an arbitrary collection of fields.

Since you are relatively new to database and Access, I recommend your review the Database Planning and design link in my signature. For Database design, the tutorials from RogersAccessLibrary mentioned in the link are a great resource - Work through 1 or 2 to experience database design and normalization.
Good luck with your project.