Nested Query: Assigning to Fields "The value you entered is not valid for this field" ??

[ironfelix717]

Hi all,

I am using a nested query, kindly taught to me by forum contributors a few days ago.

I am encountering a strange issue with a "Load" routine, which loads queried data into the form from SQL string.

Here is the nested query:

  Set rs = db.OpenRecordset("SELECT * FROM MfgOperations WHERE (((MfgOperations.Index) In (SELECT TOP 1 Dupe.Index FROM MfgOperations AS Dupe WHERE Dupe.ID=MfgOperations.ID ORDER BY Dupe.Version DESC))) AND MfgOperations.Active = True AND MfgOperations.ID = '" & opID & "' ORDER BY MfgOperations.ID;")

And the code fails directly after when i begin assigning any one field in this query to a textbox item....

textbox1.value = rs.fields("ID")

The failure is The value you entered is not valid for this field.


Now, i made a specific code note of this issue back when I was using a double query instead of a nested query.

The double query was named "Versions" for example. So when I assigned the textbox value, it failed until I did this...

textbox1.value = rs.fields("Versions.ID")

However, because I am using this new nested query, i'm having trouble figuring out how to resolve this... I've tried...

textbox1.value = rs.fields("Dupe.ID")

and

textbox1.value = rs.fields("MfgOperations.ID")

No avail... This is present with any textbox object or any field I attempt to assign to within this query.

Thanks for any help!

[June7]

Why are you using code to populate controls? Why don't you bind RecordSource and ControlSource properties in form design?

Is ID a number type field? If so, don't use apostrophe delimiters in query criteria.

[ironfelix717]

Code is the proper way to populate a control in my opinion.

ID is a short text field and not the primary key. opID is not a field, but a VBA str var, and requires delimitation as it is a VBA str var.

Regardless, the query itself is not suspect. The query returns data fine. The issue lays somewhere within the nested query and VBA cannot distinguish between the parent table "MfgProcesses.ID" and the nested query "Dupe.ID". My reasoning for this is that i had to explicitly name the nested query last time with an SQL string that included a query of an existing query.


Regards,

[CJ_London]

However, because I am using this new nested query, i'm having trouble figuring out how to resolve this... I've tried...

. Since you are only returning data from one table and in your sql you refer to ID for that table there should be nothing to resolve

However you are missing a number of lines of code to show your whole process so not possible to advise further other than to suggest see what happens after you open the recordset if you put

debug.print rs.fields("ID")

do you get the expected value?

and I presume textbox1 is a name for a textbox control on your form?

[June7]

I have never built a database that uses code to load data. Always used bound forms and controls. This is a major advantage for using Access as frontend.

However, I have no problem assigning value to control from a query like yours:

Me.Text0 = rs.Fields("ID")

[ironfelix717]

Hi,

Solved the issue. Turns out a little devious routine was locking the textbox prior to this code, preventing being able to write to the control. Silly issue. Glad its solved!

Thanks all for the help.

Regards,