asked on

need to set a table field

I am reworking an old database which involves finding data in one table to put into another table. I am having trouble with the second part.
I get variable not defined for tblStates when I use;

Update tblStates
   Set CountryID = lngFoundCountryID
   WHERE Country = strCountryToActOn

Open in new window

I'm sure my troubles far exceed this one, but I am lost (again).

Rey Obrero (Capricorn1)

" tblStates" should be the name of the table that you want to update..
do you have a table named " tblStates" ?

are you doing this in a query grid or in VBA?

DrftrBob

ASKER

Doing this in VBA

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrftrBob

ASKER

The last line needs to be (has an extra double quote)
& " WHERE Country = '" & strCountryToActOn & "'. dbfailorerror
That runs, but it's not adding the data.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrftrBob

ASKER

Thanks - the change to a comma took care of the extra quote (forgive my simplicity). Also, should be dbFailOnError. It's still not adding that data in the existing records. It could be something unrelated to our topic.

Rey Obrero (Capricorn1)

can you upload a copy of your db..

DrftrBob

ASKER

I hope I haven't killed it by removing too much.
ForShare.zip

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrftrBob

ASKER

Thank you. Yes, I now see. I will be back on this to work-out my next attempt a bit later.

DrftrBob

ASKER

I haven't cleaned this up any for publication, but I have it working with this:

    For i = 1 To lngCountOfSourceItems
        varOriginatingRecord = State_rst.Bookmark
        'Debug.Print CStr(varOriginatingRecord)
        strOriginatingSearchText = State_rst.Fields!State
        strSearchData = strOriginatingSearchText

        Customers_rst.FindFirst "CustStateOrProvince = '" & strSearchData & "'"
        If Customers_rst.NoMatch Then
            MsgBox "State Record not found"
            Else: varFirstSearchedRecord = Customers_rst.Bookmark
            strFirstSearchedText = Customers_rst.Fields!CustCountry
        End If
        
        Country_rst.FindFirst "Country = '" & strFirstSearchedText & "'"
        If Country_rst.NoMatch Then
            MsgBox "Country Record not found"
            Else: varSecondSearchedRecordID = Country_rst.Fields!CountryID
        End If
     
        CurrentDb.Execute "Update tblStates" _
        & " Set CountryID = " & varSecondSearchedRecordID _
        & " WHERE State = '" & strOriginatingSearchText & "'", dbFailOnError

        State_rst.MoveNext
    'Debug.Print Customers_rst.Fields!CustStateOrProvince & ", " & Customers_rst.Fields!CustCountry

    Next

Open in new window