The way I usually do is transfer the query result to a listbox and then access the value from the listbox (assign to the text box), I am aware it does not sound 'clean' programming, but it works OK so far. What do you think of this way ?
Actually, that was only an example. In my real application, what I need to do is to let users change label caption as they wish, so the label caption is saved in a table.
Name : (label 1)
Address (label 2)
so users might want to change the label 'Address' to 'Full Address' for example. So what I do is transfer the table to a listbox and assign first item in the list box to label1.caption, second item to label2.caption and so on. And if users wanna change a label, a form will pop up, the form is based on the table, and i just use docmd.applyfilter to display only the label they want to change. Any better way ?
In your example, why do you choose ID in the select statement ? And the code (Me![Field_Name].Value = recNames("ID").Value), should it be recNames("Name").value ? If the ID is not a key, so maybe 2 or 3 names result in the query, what will happen if you assign it to the textbox ? Is it better than to use listbox ?
For your first question, my opinion is that if it works, and doesn't cause other problems then go with it--there are probably way to many revisions that could be made to any application to handle all of them perfectly, and time is usually an issue....
The solution that I gave is just the one that I have found to be productive. This is using DAO methods, and if DAO gets unsupported in the future, as I have heard that it will, it may not be the best solution.
As Far as your last Question, those were errors on my part--you would select the name from the recordset, and set the [Field_Name].value to recNames("Name").value--and if I am not mistaken, the value that will be assigned to the textbox will be the first record returned by recNames--You can use an order by clause or include other criteria to further limit the records returned.
Note that you can use this query to populate a listbox with values as well.
I know there were some other questions in there as well, but I did not completely follow all of them, and have to sign off.