Debug Indicates a Specified Table is not defined

[Welshgasman]

Why is just the table Security table causing the error the other two tables, mtShrs and mtSelectedRates, accepted?

I do not know any other way of saying this.



I believe it is not SECURITY.Invested but Security![Invested] that is the issue.

[isladogs]

Me too. Please see the last line of code in post #12.

[CJ_London]

Either way, it is likely the query is not updateable given the 3 tables involved.

It really isn't clear to me what the OP is trying to achieve.

Do they want to open a recordset which they can then edit using VBA?

Or do they want to create a new table and then open the new table to edit it in VBA?

Or something else?

[June7]

And we need sample data. Can build (Advanced posted editor) or copy/paste tables into post or attach file (again, Advanced post editor).

Just looked at the original SQL again. Linking two primary keys to the same foreign key makes no sense, especially if those primary keys are autonumber.

[Ed Bitzer]

June7 I am not helping you or the others help me. I had corrected to MySet = MbDb.OpenRecordset(strSql) which I thought was for non-action queries. However you indicate I use "Set". Appreciate if you would give me syntax.

Sorry about not responding to duplicating which I am not doing. I move through the records modifying the myset.pays field depending on the Myset![SecType] to either MySet![Rate] * 0.01 * Security.Invested * MySet![Shrs] or MySet!Pays = MySet![Rate] * 0.01 * MySet![FaceValue] * MySet![Shrs]. But I have to get past the error that External Name is not defined for Security"

Ed

[June7]

Your original code used SET which is correct for opening a DAO recordset. The SQL statement was not valid.

As already explained by others, line should be:

MySet![Pays] = MySet![Invested]

Which appears to be simply duplicating Invested into Pays.

Nothing you provided shows any calculations are taking place nor any conditional decision.

However, cannot populate a recordset field without first putting recordset into edit mode.

Example of code for opening, looping, editing DAO recordset:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("some valid SELECT query")
Do While Not rs.EOF
    rs.Edit
    rs!somefield = somevalue
    rs.Update
    rs.MoveNext
Loop

Alternative to looping a recordset would be UPDATE sql:
CurrentDb.Execute "UPDATE tableORquery SET somefield = something"

Saving calculated data is usually not advisable, calculate the value when needed.

If you need more assistance, suggest providing db for analysis.

Without clarification of data structure and business process this procedure should support, I may have to back away.

[CJ_London]

I’m dropping out anyway as the OP is not clear about what they are trying to do, only the way they think it should be done

[Ed Bitzer]

I am discouraged that I have not made clear by objective as noted by London and June7. Apparently the code examples I gave to not make clear that I am trying to update the Pays field in field for two types of investment, "S"tocks and "B"onds. To this end I must cycle through the records and make the proper calculation based on I am doing this by cycling thru the fields displayed in the query and modifying Rate based on records from the other three tables. June 7 clearly points out that omision which is in a couple dozen lines of code before the Update and Next ctr (actually the months).
Be patient, have to hit the hay now (remember I am 92) and I will try again to answer every one of June7 questions in response #8, which I obviously did poorly in #20. I suspect I should offer the complete code (and I have found the # symbol on the main menu of Quick Reply). If I do not do that clearly I believe I will have to modify the code to create a make table and then walk through that table in a way I am more familiar.

Ed

[Welshgasman]

Could you just use an update query and a UDF to calculate the new value?

[Ed Bitzer]

I now find you have given me all the answers but accompanying far to much other information and questions for this old mind to handle. A lot of that extra stuff was becuase my original question not clear and including a mistaken quote (I was testing the alternative approach of creating a temporary table rather than work directly with a recordset created on the fly from SQL. I actually was using a select query, I was testing this update query which when properly opened still created an error with the reference to the Security tabe.

I problem from the start was that I created a recordset and then incorrectly referred to to the Security table. It should have been MySet![Pays] = MySet![Invested] as noted by June7 in in reply #21 - me bad. By the way that line of code may not make much sense but was included just to create the error early in a sub that is several pages that include necessary references later.

All is well that ends well.

Ed

[Ed Bitzer]

I do not know how to mark this thread as solved. No option under Mark Tools.

Ed

[Welshgasman]

I do not know how to mark this thread as solved. No option under Mark Tools.

Ed

Thread tools, top right on green thread toolbar.
There is even a button on the very top toolbar that shows you how?