This one stumps me, not sure how to classify the question (1 Viewer)

[cyberman55]

Hi all, I have a very large Access application and use a hidden "main switchboard" to store data such as the current customer ID. Prior to the advent of tempvars, I embedded a key control into many query's underlying combo boxes, list boxes and forms criteria: "Forms![main switchboard]![currentcustomerid].

The problem arises when the user quits the application on with forms using this in their underlying query(ies). As far as I can tell, the application closes forms in the order in which they were opened, so the "main switchboard" closes first. Then, for some reason, the current form has a requery and the user is presented with a prompt to enter the parameter "forms![main switchboard]![currentcustomerid]. I tested and found I could replace the criteria reference to a tempvar to "fix" the problem. However, I've got over 3500 queries to look through and wonder if there is a faster way to fix annoyance. I wonder if there would be a way to open every query in design view and do a search and replace on the criteria statement. Otherwise, if anyone can offer a better solution, well it would be appreciated!

 

[pbaldy]

Google a free utility called V-Tools which has a deep search/replace tool.

3500 queries?!?

 

[cyberman55]

Thanks much - yep - after 20 years, this is a massive application (a facility management core) to which I'm adding other applications such as inspection software. I'll look at vtools, although I've started to play around with this:

strOldCriteria = "[Forms]![Main Switchboard]![CurrentCustomerID]"
strNewCriteria = "CLng([TempVars]![tvCurrentCustomerID])"

Set db = CurrentDb()

For i = 1 To db.QueryDefs.Count - 1
Set qd = db.QueryDefs(i)
strSQL = qd.SQL
If InStr(strSQL, "[Forms]![Main Switchboard]![CurrentCustomerID]") Then

strSQL = Replace$(strSQL, "strOldCriteria", strNewCriteria)
Debug.Print strSQL
End If
qd.Close
Next i

Set qd = Nothing

It works, but now I have to figure out how to save this as a new query definition. I suspect I'm going to have to use the create query definition technique, but just getting into it. I do know there is method to delete (in memory or the actual definition) and if that dead-ends, I'll look into whether or not they can be opened in design view and then saved with modifications.

 

[pbaldy]

I would think you can just set the SQL:

qd.SQL = strSQL

 

[cyberman55]

Hi again. I wish it were that easy! The instance opened that way is changed and it is a nice way to "run" a query. But, it's not the actual query, just a copy of it in memory and it doesn't save.

The code below opened all the queries of concern, and I'm just going to manually edit them now. I'd guess perhaps 40-80 of them opened and I can copy and paste in the replacement criteria faster than figure this out:

strOldCriteria = "[Forms]![Main Switchboard]![CurrentCustomerID]"
strNewCriteria = "CLng([TempVars]![tvCurrentCustomerID])"

Set db = CurrentDb()

For i = 1 To db.QueryDefs.Count - 1
Set qd = db.QueryDefs(i)
strSQL = qd.SQL
If InStr(strSQL, "[Forms]![Main Switchboard]![CurrentCustomerID]") Then
qryName = qd.Name
DoCmd.OpenQuery (qryName), acViewDesign, acEdit
strSQL = Replace$(strSQL, "strOldCriteria", strNewCriteria)

'qd.SQL = strSQL

End If
qd.Close
Next i

Set qd = Nothing

 

[pbaldy]

Curious, as I did a brief test and the query saved with the new SQL. I wasn't looping though.

 

[cyberman55]

That is funny - not sure why it wasn't saving for me, and I saw other forum references to the same issue. Now, I'm using my old technique to cycle through the forms (570 or so) and check if I used the query builder to embed the sql in list boxes and combo boxes. It turned out there were only two forms needing changes. But the weird thing is the debug statement is showing the criteria, so the instr statement works, but the replace function isn't working and the debug statement shows the original criteria - go figure...

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
Dim cControl As control
Dim strFormName As String
Dim strSQL As String
Dim strObjectName As String
Dim strNewCriteria As String
Dim strOldCriteria As String

strOldCriteria = "[Forms]![Main Switchboard]![CurrentCustomerID]"
strNewCriteria = "CLng([TempVars]![tvCurrentCustomerID])"

For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acViewDesign, , , acDesign
strObjectName = obj.Name

For Each cControl In Screen.ActiveForm.Controls
If TypeName(cControl) = "listbox" Then
strSQL = cControl.RowSource
If InStr(strSQL, strOldCriteria) Then
strSQL = Replace$(strSQL, "strOldCriteria", strNewCriteria)
Debug.Print strSQL & " in form " & obj.Name & " control: " & cControl.Name
'cControl.RowSource = strSQL
End If
ElseIf TypeName(cControl) = "combobox" Then
strSQL = cControl.RowSource
If InStr(strSQL, strOldCriteria) Then
strSQL = Replace$(strSQL, "strOldCriteria", strNewCriteria)
'cControl.RowSource = strSQL
Debug.Print strSQL & " in form " & obj.Name
End If
End If


Next cControl
DoCmd.Close acForm, obj.Name, acSaveYes
Next

I'll probably compact/repair next and reboot. Since only two forms need fixing, will do that manually.

Incidentally, variations on the technique are handy for mass-changing everything from tooltips to fonts and the like...

 

[cyberman55]

Update - just noticed the extraneous quote marks surrounding some of these strings, may explain some of the issues.

 

[pbaldy]

I hadn't noticed that; it would certainly cause the Replace() to fail.

 

[cyberman55]

Thanks - as a follow-up. This is what I learned.

1. Use a tool such as the one mentioned. It's just easier.
2. Access "instr" function can be problematic - it couldn't resolve strings with bang operators properly or consistently (not sure which).
3. Watch how many queries you open at one time in design view. I ran out of memory quickly trying to do too many.
4. Biggest mistake I made was to use the same name for the tempvar (tvCurrentCustomerID) as was in the string I was searching for: a control named currentcustomerid - since instr found them both. I ended up using the loop counter to start at a higher number each time I finished a group of queries.