Results 1 to 12 of 12
  1. #1
    TrevorM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    9

    Find and Replace Macro

    Hi, Im trying to write a VBA macro that will do a find and replace a record with in multiable tables, I know you can do a query which will replace values with (Replace([YourFieldName], "%" , "" )) but I need to apply the record change over multiable tables where the record exists, Below is what I have done I'm familiar with it working in excel and have no issues there, but don't know how to select tables before running the macro search.replace function on it to test if it will work in the same scenario.



    ----------------------------------------------
    OldAsset = Me.txtOldAsset & ""
    NewAsset = Me.txtNewAsset & ""


    If OldAsset = "" Then
    MsgBox "No Old Asset Number"
    End
    Else
    If NewAsset = "" Then
    MsgBox "No New Asset Number"
    End
    Else

    MsgBox "Start Asset Number Change"

    Selection.Replace What:=OldAsset, Replacement:=NewAsset, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    End If
    End If


    End Sub
    -------------------------------------------------
    This is my current query with tables
    Click image for larger version. 

Name:	Tables.png 
Views:	25 
Size:	68.3 KB 
ID:	14759
    Last edited by TrevorM; 12-13-2013 at 07:31 PM. Reason: Added image

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Interesting. If you build a query that includes the fields you want to edit, does the query allow you to update and or edit the fields?

  3. #3
    TrevorM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    9
    If i use this code in the criteria, of one of the tables, Asset List.Asset for example

    Replace([Asset List]![Asset],[Forms]![Menu - Change HE Number]![txtOldAsset],[Forms]![Menu - Change HE Number]![txtNewAsset])

    And on my form I have old asset as HE 2000 (HE = Hired Equipment) and new asset as HE 2001, when attaching the query to a button to run the query, it doesn't seem to be having an affect

    I've got a quite good experience on excel VBA on excel and only have transitioned to access recently to try and gain more skills, taking my current 10 excel sheets and putting into this database is my first experiment which i'm grasping well as it has very similar principles, but when it comes to some definitive coding im not sure on have gotten away with alot as its seems to be fine, If I can find a way to tell the macro to select a particular table i should be able to find a way to run the find.replace

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by TrevorM View Post
    If i use this code in the criteria, of one of the tables, Asset List.Asset for example....
    I do not understand what you are trying to say here. Are you unable to create a query using the Access query builder?

  5. #5
    TrevorM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    9
    What happens is occasional I need to change a asset number assigned to a vehicle to do this i want a form to have a field Old HE and field for New He, and then a button which will go through all the tables, find the old HE number and replace it with the new HE number, the issue is there are 7 tables it needs to crawl through and complete the find and replace.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Well, if you are unable to create an updatable query to include the fields you wish to update, you will have to use something like DAO to "Crawl" through the various tables.

    Perhaps normalizing your data may help. Using Autonumber as a field data type for your primary keys will go a long way. When you have strange business rules like, "Reasigning asset numbers to vehicles", more columns in your tables will help. You do not want to reassign or update values in Key fields. This will defeat the purpose of RDBMS.

  7. #7
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Are you familiar with the realationships feature (under Database Tools in A2010) ? If you use the relationships to describe the data in the same way as in the query, and then turn on the cascade update property of each relationship using "Asset", then changing the "Asset" value in the Asset list table wil automatically update it in all the other related tables. No macros or coding required.

    HTH

    John

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You have a fundamental problem with your database if you have defined PKs (Primary Keys) that can be altered over time.

    PK's should be values that will ***NEVER*** change. In your case your ASSET PK should be an autonumber field, this field should be stored as the FK (foreign key) on any of your other tables. That way if you change the internal designation of an asset (say from ASS001-01 to ASS001-001) your relationships are preserved and you do not orphan any data (which is what you're doing right now)

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am also seeing a circular relationship between Asset List, Repairs, Repairs and Services.

    BTW, advise no spaces or special characters/punctuation (underscore is exception) in any names. Better would be Odo_Hr or OdoHr, SVSNum or SVS_Num.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    TrevorM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    9
    Hi John,

    No I haven't heard of that process, I can kinda understand what you mean, would you have any documentation or websites you can refer me to in respect to looking further into this option?

    Thanks for the help.

  11. #11
    TrevorM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    9
    Hi June7,

    Yeah the query does look that way but only built the query in order to try the Replace([Asset List]![Asset],[Fo....... but of code, but failed to work or couldnt work it out so i have scraped the query since. looking forther into the VBA script which im very familar with in excel, but still trying to work out how to tell it to select a particular table too see if i can search the table with in the selected table via VBA.

    Thanks.

  12. #12
    TrevorM is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2013
    Posts
    9
    Hi All,
    I have managed to sort this out, for all those in the future who might be as stumped as me, these two links helped me,

    1) There is a powerpoint video guide on this linke (http://office.microsoft.com/en-au/ac...104080847.aspx)
    2) Stepping guides on (http://office.microsoft.com/en-au/ac...010076527.aspx)

    thanks for your help though all involved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find and Replace
    By dweekley in forum Queries
    Replies: 3
    Last Post: 04-12-2013, 07:16 AM
  2. Replies: 5
    Last Post: 02-12-2013, 09:12 AM
  3. find and replace
    By rohini in forum Access
    Replies: 7
    Last Post: 05-17-2012, 05:23 AM
  4. Find and Replace Within a Macro
    By bmschaeffer in forum Access
    Replies: 1
    Last Post: 12-20-2011, 11:27 AM
  5. Find and Replace
    By Bedsingar in forum Access
    Replies: 1
    Last Post: 08-14-2011, 01:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums