Form in datasheet view fed from 2 queries

[Autoeng]

I have data in my db that is not updateable and I want to add some fields to enhance it. Presentation to another group of people to data entry is where I am stuck. Can I present it in datasheet view keeping in mind that the data must come from two queries (one set that is not updateable and one that are fields that I added)?

 

[CraigDolphin]

Assuming that there is some keyfield that you can use to join the two sets of data, then just create a new select query that pulls the desired fields from each of the other queries. (If you don't already known about them, you may want to learn about outer joins vs inner joins depending on whether you want data from one table to be returned even if there is no matching data in the other table).

Then bind a form to the new query and view it in datasheet mode. Incidentally, if the record-source is non-updateable, the user will not be able to change any of the displayed data via the form either.

If there is no key field to join the data appropriately, then I'd create an unbound form with two subforms without any master-child links between the form and subforms. Each subform would be bound to one of the two source queries, and shown in datasheet view.

Hope it helps.

 

[Autoeng]

Data from one table is not updateable. It is linked data from an SQL table. When I combine it with the data from another table that is updateable I find that all of the data becomes not updateable no matter what view it is in. The only way I have found around this is to create a single record form that the updateable data is displayed via subform from a completely separate query that the non updateable data.

So am I missing something?

Would it be possible to push the updates back from an Excel spreadsheet to Access. Note that the supplying query would contain data from both non updateable and updateable tabels.

Autoeng

 

[CraigDolphin]

When I combine it with the data from another table that is updateable I find that all of the data becomes not updateable no matter what view it is in.

That is correct. If even one field in a recordset (a source table or query) is non updateable, the entire recordset become non-updateable. As I stated in the last post:

if the record-source is non-updateable, the user will not be able to change any of the displayed data via the form either

Now in your original post you indicated that the form was intended for presentation (viewing) purposes so my post was an answer to the question you asked. If you now wish to edit the data via the form, in those fields that come from the updateable query, then that's a completely different question.

If that's the goal then I suspect your options are limited (though other people on the forum might have a better solution to offer). The two subform approach, one bound to each of your two queries might be the best option for you. But if you insist on having all the fields in one non-updateable recordsource and still want to be able to edit the values, then you're likely going to have use vba to obtain the user input when they click in a control, and then run SQL statements to directly update the data in the underlying tables, and then refresh the data in the form to reflect the change.