Hi. Assuming your form is open (see the PS below), I suspect that this is a Jet Database Engine problem, as there is a known bug with the use of direct form field references as part of a Where clause. Such references often work in other circumstances yet fail unexpectedly when used in a different way. To take an example, a Select query with a subform reference such as your one will fail if the Select query is converted to a crosstab. Jet fails to recognise the subform control reference as a valid field name, yet in the Select form it correctly recognises the subform reference.
To avoid this problem I use a function placed in a public code module which can return a control value given the name of the form and the field reference. In the query I then refer to the function, not the form field directly. I have had no failures with this approach.
The function I normally use is intended for main form controls, but is easily modified to cope with subforms:
- Public Function SubFormFieldValue(FormName As String, SubformName As String, FieldName As String)
-
SubFormFieldValue = Forms(FormName).Controls(SubformName).Form.Controls(FieldName)
-
End Function
(Please note that as an artifact of this post there are spaces shown between the l and the s in the Controls(FieldName) part - please remove them when pasting)
Copy this to a public code module (in the the Modules section of your database), or if you do not have any create a new module, paste the function in and store the module under any suitable name.
In your query replace the direct form field references with
- SubformFieldValue("find projects", "Find Projects Subform", "Budget")
This should resolve the problem with the lack of recognition of the subform field reference in your XML exports.
-Stewart
PS - I just thought - the form is open at the time you run the XML export, isn't it? If it isn't, the query will not be able to fetch the value of the controls concerned, and will ask you for the values - and my function will fail as the form is not open.
1
