query result fields not available in expression builder`

[bbxrider]

not sure if this is general query question or for some reason unique to northwind traders sample db

I'm trying to add a new field to a form, "receiving subform for purchase order details", its query record source is, "purchase details extended"
I add a text box and invoke expression builder, with the intent to populate the text box with a field from the query results
and go to the query but none of the query fields show up? 1st pic
its probably related, the existing other fields on the form, populated by the same and only query don't have expressions like


[query-name]![field-name], in the expression builder they just have literally the field name from the query? 2nd pic

from working before with access I thought you can populate fields using expression builder and literally selecting the desired fields
from tables or queries? can't figure out what is different here
hopefully the attach pics illustrate what the problem is here that I'm having

[isladogs]

Click the down arrow next to the ellipsis button to get a list of all fields used in the form record source. Select from the list.
Use the ellipsis button where you want to build a value e.g

=Nz([OrderID],0)

Note that you can just drag a field onto the form and a textbox will automatically be created

[June7]

I never used the expression builder. I find it more annoying than helpful.

I just did a test and notice that my 2 queries involving nested SELECT TOP N do not show fields although a UNION query does.

[bbxrider]

thanks for the reply ridders, see attached pic, the down arrow gives an sql error, thinking if the query data fields would show up in the expression builder they would also show up in the down arrow, it shouldn't make any difference to get the available fields yes? maybe if i wanted to do some expression builder formatting or something like then I would use expression builder instead of using the down arrow and dragging. I added a new table to the original query with the query UI to get some more data fields available and let access generate the sql it wanted for that. when I added the table it was very slick making the connections to the keys in the tables already in the query
I go back to my original attachments, when selecting the query in the expression builder, if all was right I should see the available fields from the query in the box just to right of the box where the query (or form, or table, etc) was selected.

[June7]

Are you able to open the query and view records?

[isladogs]

Just opened my copy of Northwind database to the same form as you, added a textbox and clicked the down arrow.
It behaves correctly giving me a list of available fields



Have you altered the form record source and possibly created the ambiguous join error?
If so, have a look at this post from yesterday which may help ....
https://www.accessforums.net/showthr...356#post390356

If not are you still using the web version of the Northwind database as in this thread?
https://www.accessforums.net/showthread.php?t=70958

If not that either, suggest decompiling, recompiling then compacting ... or just download the database again.

[bbxrider]

ok got it working, per using the fields available on the dropdown next to the ellipse, my bad, when I added the new table to the original query and quickly saw the join line I never checked, the join properties needed to be adjusted to make the new added table a 'reference' table based on the common key fields. kinda surprised the UI let me configure an added table that resulted in bad sql

However, still not sure why the query fields are not available in the expression builder when I have selected the record source query. I'm pretty sure that was how it worked in access previous versions. when I selected say a table or some other queries and the fields show, and then dbl click the field and you get an generated expression like this, =[Purchase Order Details]![Unit Cost] (or you could just type it in the box)
of course you can only select the object (table, query, etc) that is named as the record source for the form or report

[June7]

Actually, I am able to select from tables and queries that are not included in the RecordSource. Of course, the result makes no sense and generates #Name? error in the control.

[isladogs]

I've just tried replicating your issue using the same form in access 2016.
Clicking the ellipsis also works fine for me.

I then added an extra table Purchase Orders to the form query & deliberately failed to create a join.
I again clicked the ellipsis & I could still add fields from the form query