Updating all form-stored SQL

[Soma_rich]

How would I update all SQL stored within all forms to change its syntax?
I am returning all the row-sources for all the comboboxes on all forms in an Access db. Most work fine but some have syntax errors, for example
double quotes instead of single.

Is there any way of finding these stored SQL statements and updating them on mass, as I am doing it manually at the moment!

 

[ByteMyzer]

You could try an approach like the following in a global module:

[COLOR="Navy"]Public Sub[/COLOR] formComboReplace()

[COLOR="navy"]Dim[/COLOR] frmName [COLOR="navy"]As String
Dim[/COLOR] ctl [COLOR="navy"]As[/COLOR] Control
[COLOR="navy"]Dim[/COLOR] X [COLOR="navy"]As Long
Dim[/COLOR] sRowSource [COLOR="navy"]As String

For[/COLOR] X = 0 [COLOR="navy"]To[/COLOR] CurrentDb.Containers("Forms").Documents.Count - 1
    frmName = CurrentDb.Containers("Forms").Documents(X).Name
    DoCmd.OpenForm frmName, acDesign, , , , acHidden
    [COLOR="navy"]With[/COLOR] Forms(frmName)
        [COLOR="navy"]For Each[/COLOR] ctl [COLOR="navy"]In[/COLOR] .Controls
            [COLOR="navy"]If[/COLOR] ctl.ControlType = acComboBox [COLOR="navy"]Then
                If[/COLOR] ctl.RowSource > "" [COLOR="navy"]Then[/COLOR]
                    ctl.RowSource = Replace(ctl.RowSource, """", "'")
                [COLOR="navy"]End If
            End If
        Next[/COLOR] ctl
    [COLOR="navy"]End With[/COLOR]
    DoCmd.Close acForm, frmName, acSaveYes
[COLOR="navy"]Next[/COLOR] X

[COLOR="navy"]End Sub[/COLOR]