This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:
- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples
Find and replace not working In access after tables are linked to SQL Server
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
If you are now using MSSQL, you'll need some queries.
What, specifically, are you trying to find and replace?
you need to run post migration DB maints: reindex;update stats; etc and probably consider to review your code for optimization: add indexes , etc
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.
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.
Option Compare Database
Private Sub Command85_Click()
On Error GoTo Err_Command85_Click
Screen.PreviousControl.Set
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.Set
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
Private Sub Command85_Click()
On Error GoTo Err_Command85_Click
Screen.PreviousControl.Set
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.Set
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
Actually for the find button its using command86--
Private Sub Command86_Click()
On Error GoTo Err_Command86_Click
Screen.PreviousControl.Set
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 Command86_Click()
On Error GoTo Err_Command86_Click
Screen.PreviousControl.Set
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_Command86_Click:
Exit Sub
Err_Command86_Click:
MsgBox Err.Description
Resume Exit_Command86_Click
End Sub
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?
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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial