multiple update statements

[kato68]

I am updating one table with data from another which works fine using append and update queries. But I was to change all the 'ROAD' AND 'CIRCLE' etc names in the address fields to abbreviations, such as 'RD' and 'CR'. I know this is super easy in different update queries, but is there a way to append/update the new data and change the address field to have the abbreviations at the same time. Or maybe link the queries, so that they all run at the same time. And I cannot use form to do this, which would also make it quite easy. You know how those 'users' can be.

-Kate

 

[Mile-O]

What about a subroutine in VBA?

 

[kato68]

Im not too familiar with VB programming, so I wouldn't know how to code that.

 

[Mile-O]

Is it a set amount of changes you wish to make or can it alter?

By changes I refer to Road to RD, Circle to CR - or are there more, is it variable each time?

Also, what version of Access are you using?

 

[kato68]

It is a set amount, I think there is like 8...

ROAD
CIRCLE
DRIVE
WAY
COURT
RUN
LANE
PIKE and
STREET

 

[kato68]

Oh, and Access 2002 SP1

 

[Mile-O]

Even though you say it is a set amount you might be best served to create a new table (it may change going forward or you may find more street abbreviations) called tblConversions with three fields:

ConversionID - (autonumber)
ConvFrom - (text)
ConvTo - (text)

I think you get the idea here: put the full word in ConvFrom and the abbreviation in ConvTo.


Then, put this code in a module:

Public Function ConvertInfo()

    Dim strSQL As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Record
    
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Record
    
    rs.Open "tblConversions", cn
    
    With rs
        .MoveLast
        .MoveFirst
        Do While Not .EOF
            strSQL = _
                "UPDATE MyTable SET MyField = " & rs.Fields("ConvTo") & """" & _
                "WHERE MyField = """ & rs.Fields("ConvFrom") & ";"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            .MoveNext
        Loop
    End With

    MsgBox "All instances fixed.", vbInformation, "Changes Made"

End Function

The code may not be perfect (and may need changed slightly).

I don't know how often you want to run it or how you want to run it.

If you want to run it from a macro you can use the RunCode command in a macro and set the code to: ConvertInfo