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).
DrftrBobAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
" 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?
0
DrftrBobAuthor Commented:
Doing this in VBA
0
Rey Obrero (Capricorn1)Commented:
try this

currentdb.execute "Update tblStates" _
   & " Set CountryID =" & lngFoundCountryID _
   & " WHERE Country = '" & strCountryToActOn & "'". dbfailorerror
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

DrftrBobAuthor Commented:
The last line needs to be (has an extra double quote)
   & " WHERE Country = '" & strCountryToActOn & "'. dbfailorerror
That runs, but it's not adding the data.
0
Rey Obrero (Capricorn1)Commented:
Update query will not add any record, it will update the content of the field..

and this is correct

 & " WHERE Country = '" & strCountryToActOn & "'", dbfailorerror
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrftrBobAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
can you upload a copy of your db..
0
DrftrBobAuthor Commented:
I hope I haven't killed it by removing too much.
ForShare.zip
0
Rey Obrero (Capricorn1)Commented:
this part of your query

            & " WHERE Country = '" & strCountryToActOn & "'", dbFailOnError

is not correct,

you don't have a field "Country" in tblStates..
0
DrftrBobAuthor Commented:
Thank you.  Yes, I now see.  I will be back on this to work-out my next attempt a bit later.
0
DrftrBobAuthor Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.