Updatable and non-updatable queries

[Kenln]

I have asked this question before (sorry) but this time I'm going to a different approach/solution.

I have two tables, one is not-updatable the other you can update.

In Access if you do a query joining these two tables then the entire query is not updatable. Even the data that should be...

If there is NO WAY to join and query the data then... is there any way to show two joined (???) tables on the same form. Both in Single and Datasheet views.


Some details and history:
The FE is Access, the BE is MSSQL Server (mostly). Mostly because I use linked tables to an IBM iSeries. These tables (on the IBM) are not updatable.

In Access I can create either linked tables or linked views to MSSQL. Yeah!!! No problems there. But if I built a query with (either a view or table) a table from MSSQL and a linked (non-updatable) table from MSSQL then bang! I can't update ANYTHING. sigh...

Guess what. If I build a view in MSSQL and look at it there (Management Studio) I can update the MSSQL tables just fine. But if I link to the very same view in Access no joy.

I really, really need this because without this I have to duplicate data from the IBM into MSSQL which really stinks (duplicating data I mean).

So... if no one knows how to create a query or recordset that is updatable when one of the joining tables is updatable, then

Does any one know how can I show data from two sources (tables) on the same form such that it is updatable?

Sorry for the long post. Thank you,

 

[boblarson]

Does any one know how can I show data from two sources (tables) on the same form such that it is updatable?

Use a Main form with the updateable table and use a linked subform for the non-updateable table.

 

[Kenln]

Unfortunately, this is not always convenient for the user.
This is a good idea sometimes it segregates that data such that is looks confusing or misplaced on the screen.

I may not have any alternative.

Any other ideas?

 

[boblarson]

Unfortunately, this is not always convenient for the user.
This is a good idea sometimes it segregates that data such that is looks confusing or misplaced on the screen.

I may not have any alternative.

Any other ideas?

Actually, it really doesn't have to. If you set the subform's Special Effect property to FLAT and then the border to TRANSPARENT, it can look just like the main form.

 

[Kenln]

??? Then can I over lay text boxes or combo boxes from the main form?

 

[boblarson]

??? Then can I over lay text boxes or combo boxes from the main form?

Try out some different things - you might be surprised at what you can actually come up with. I don't know exactly what your data is like and how you are hoping to present it, but I think (given your constraints) this may be your best shot at getting it all together in one place.

 

[Kenln]

This sounds like a lot of (extra) work.

But if it works it will save a lot of unnecessary updating of duplicate data.

This I gotta try.

 

[Kenln]

Okay, I think I still need help,

I can't place any object on top of the subform.

I have tried pushing the subform to the back. Bringing the objects to the front. The subform seems to be in the highest foreground.

Any ideas how I can place an object on top of a subform???

 

[boblarson]

Can you post a screenshot of what you have and then one of what you are wanting, if it were to work? I'm having trouble picturing this.

 

[Kenln]

I think I have a solution.

I can use multiple subforms.

Not as efficent but it works.

 

[boblarson]

I think I have a solution.

I can use multiple subforms.

Not as efficent but it works.

If it works, sometimes that is the most important thing

 

[Kenln]

This not the best image.

The red is data that can change. The blue is data that can not.

Depending on the screen the data intermingles. Sometimes in the same column.

But even here you can see how the data of both the form and subform are right next to each other.

I was hoping to cover the main form with my subform and the place text boxes on top of (but not in) the sub form.

did this help?

 

[boblarson]

If the subform on subform will work, then that is how you will have to go. If not, I think you will just have to modify your layout to something that will work and not keep the desired layout.

 

[Kenln]

In any form you can create objects that over lap. Sometimes on purpose such as turning one visible and another visible = false.

I would like to do the same thing with objects and a subform. But the subform is ALWAYS on top of any objects there by covering (hiding) them.

Do you know of any way of placing an object so that is covers (is in the foreground/front) of the subform object?

 

[boblarson]

Do you know of any way of placing an object so that is covers (is in the
foreground/front) of the subform object?

No, I do not.

 

[Kenln]

Sigh...

Thanks, though using multiple subforms or simply laying my forms out better may be the solution anyway.

I appreciate your help,