Solved

Find and replace not working In access after tables are linked to SQL Server

Posted on 2012-12-21
8
388 Views
Last Modified: 2012-12-26
We have a forms in access and Find and Replace works fine before the tables are migrated to the ms SQL.Now after I migrated the tables to SQL,Find and Replace takes too much time and I guess i would say it does not work.Please advise.Thanks
0
Comment
Question by:Josh2442
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

by:Barry62
Comment Utility
Were you using the find and replace tools in access, or were you running SQL queries?

If you are now using MSSQL, you'll need some queries.

What, specifically, are you trying to find and replace?
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
you need to run post migration DB maints: reindex;update stats; etc  and probably consider to review your code for optimization: add indexes , etc
0
 

Author Comment

by:Josh2442
Comment Utility
It's using the vba code and I am using find and replace tools in access.I dont know anything about the vba code.By the way we have forms and find and replace is a binocular things in access ,when user tries to put the account number,it displays the form for that account number but after I migrated to sql,its taking too much time though the records are at the forms.
0
 
LVL 8

Expert Comment

by:Barry62
Comment Utility
Sounds like you need to find a vb programmer to modify the code.  Can you access the vba code and copy and paste it here?  Maybe we can modify it for you.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Josh2442
Comment Utility
Option Compare Database



Private Sub Command85_Click()
On Error GoTo Err_Command85_Click


    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_Command85_Click:
    Exit Sub

Err_Command85_Click:
    MsgBox Err.Description
    Resume Exit_Command85_Click
   
End Sub

Private Sub Detail_Click()

End Sub
Private Sub Command86_Click()
On Error GoTo Err_Command86_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command86_Click:
    Exit Sub

Err_Command86_Click:
    MsgBox Err.Description
    Resume Exit_Command86_Click
   
End Sub

Private Sub Return_to_Main_Click()

End Sub
Private Sub Command87_Click()
On Error GoTo Err_Command87_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Collateral Control Switchboard Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command87_Click:
    Exit Sub

Err_Command87_Click:
    MsgBox Err.Description
    Resume Exit_Command87_Click
   
End Sub
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click


    DoCmd.GoToRecord , , acFirst

Exit_Command88_Click:
    Exit Sub

Err_Command88_Click:
    MsgBox Err.Description
    Resume Exit_Command88_Click
   
End Sub
Private Sub Command89_Click()
On Error GoTo Err_Command89_Click


    DoCmd.GoToRecord , , acLast

Exit_Command89_Click:
    Exit Sub

Err_Command89_Click:
    MsgBox Err.Description
    Resume Exit_Command89_Click
   
End Sub
Private Sub Command90_Click()
On Error GoTo Err_Command90_Click


    DoCmd.GoToRecord , , acNext

Exit_Command90_Click:
    Exit Sub

Err_Command90_Click:
    MsgBox Err.Description
    Resume Exit_Command90_Click
   
End Sub
Private Sub Command91_Click()
On Error GoTo Err_Command91_Click


    DoCmd.GoToRecord , , acPrevious

Exit_Command91_Click:
    Exit Sub

Err_Command91_Click:
    MsgBox Err.Description
    Resume Exit_Command91_Click
   
End Sub
Private Sub Command92_Click()
On Error GoTo Err_Command92_Click


    DoCmd.GoToRecord , , acNewRec

Exit_Command92_Click:
    Exit Sub

Err_Command92_Click:
    MsgBox Err.Description
    Resume Exit_Command92_Click
   
End Sub
Private Sub Command127_Click()
On Error GoTo Err_Command127_Click


    DoCmd.GoToRecord , , acNewRec

Exit_Command127_Click:
    Exit Sub

Err_Command127_Click:
    MsgBox Err.Description
    Resume Exit_Command127_Click
   
End Sub
Private Sub Exit_Database_Click()
On Error GoTo Err_Exit_Database_Click

    Dim stDocName As String

    stDocName = "Exit"
    DoCmd.RunMacro stDocName

Exit_Exit_Database_Click:
    Exit Sub

Err_Exit_Database_Click:
    MsgBox Err.Description
    Resume Exit_Exit_Database_Click
   
End Sub
Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click


    DoCmd.GoToRecord , , acNewRec

Exit_AddRecord_Click:
    Exit Sub

Err_AddRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddRecord_Click
   
End Sub
Private Sub Command138_Click()
On Error GoTo Err_Command138_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Collateral Control Shipping Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command138_Click:
    Exit Sub

Err_Command138_Click:
    MsgBox Err.Description
    Resume Exit_Command138_Click
   
End Sub
Private Sub Command139_Click()
On Error GoTo Err_Command139_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "OpenCollTasksForm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command139_Click:
    Exit Sub

Err_Command139_Click:
    MsgBox Err.Description
    Resume Exit_Command139_Click
   
End Sub
Private Sub Command142_Click()
On Error GoTo Err_Command142_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Loan Number Cross Reference FORM"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command142_Click:
    Exit Sub

Err_Command142_Click:
    MsgBox Err.Description
    Resume Exit_Command142_Click
   
End Sub

Private Sub Form_Load()

End Sub
0
 

Author Comment

by:Josh2442
Comment Utility
Actually for the find button its using command86--

Private Sub Command86_Click()
On Error GoTo Err_Command86_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command86_Click:
    Exit Sub

Err_Command86_Click:
    MsgBox Err.Description
    Resume Exit_Command86_Click
   
End Sub
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
Josh,

First thing you need to do is start using a naming convention for your controls.   When you add a control of any kind to a form, Access gives it a default name; in most cases, you should change that name so that when you look at your code, you can tell what the control is (Command86 is meaningless).  There are a number of good web sites with suggestions for your naming convention; I use a combination of the Reddick and the Leszynski methods.  I use this naming convention on all of my objects, all my form/report controls, and all of my variables, constants, and objects created via code.

Secondly, I would strongly recommend abandoning the DoMenuItem method in favor of the RunCommand method.  DoMenuItem is confusing, has many versions, and is hard to debug.  RunCommand has several hundred arguments, but they are all easily recognizable and the argument names are a good indication of what you are trying to accomplish.  There used to be a wonderful website with descriptions and code examples, but it appears to be down.  I'm researching a replacement and will post back.

Having said all that, when the line that sets the focus to your previous control is executed, does it highlight all of the text in that control?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
So, Josh, can you enlighten us on exactly what worked for you?

Did the RunCommand resolve the problem, or was it that you needed to select the text in that previous control after setting the focus to the control.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now