Query is read-only, need workaround help (1 Viewer)

Hi,

I have a query which is essentially a quey of a junction table and its fields. It is the recordsource for a subform which displays its data in "continuous form" style. Of the 2 objects it is junctioning, one is a union query. In the junction table, I also have independant fields which I'd like to add and remove data from in the subform.

The problem I have is...the query has become "read-only". I believe the reason for this is because of the Union Query involved. I don't want to read or write data to the fields involved in the union Query, but i do need the values on the same line as the fields i want to read and write.

Does anyone have any ideas how I can achive this?

Here is the SQL:
SELECT tbl_WO_Parts_Junction.SF_Item_FK, tbl_WO_Parts_Junction.WONumber_FK, tbl_WO_Parts_Junction.WOParts_ID, tbl_WO_Parts_Junction.WOPartsQty, tbl_WO_Parts_Junction.Status, tbl_WO_Parts_Junction.WOPartsRFPNum, qryRFP_Items_UNION.SF_Item, qryRFP_Items_UNION.SF_ItemDescription
FROM (tbl_WO_Parts_Junction INNER JOIN tblWO ON tbl_WO_Parts_Junction.WONumber_FK = tblWO.WONumber) INNER JOIN qryRFP_Items_UNION ON (tbl_WO_Parts_Junction.dept = qryRFP_Items_UNION.Dept) AND (tbl_WO_Parts_Junction.SF_Item_FK = qryRFP_Items_UNION.SF_PK_Item);

I've included a screen shot what the form and subform look like to maybe give you a better visual of what i'm trying to do.

also,

I've included a screenshot of the query design for those of you (like me) that are more visual.

The subform should be based on the junction table only. Then, on the foreign key field, (probably SF_Item_FK in your case) put a combo box that looks up the other data from the item's data source. That way your updates to the subform are very simple, and all the lookup work is done by the combo, completely independent of your subform's record source.
Makes sense?

Yes, Makes sense. I don't want this to be selectable though. I guess I'd just disable the combobox??

Yeah, you can disable it.

I might need a little more help with this one... I just did what you said, however....
I'm not sure how to configure the Combobox query to show me a specific item.
as in,
I may have more than one item (row) in the subform. I want each row to show a different item until there are no more to show for the given Foreign Key.

The item shown by the combo should be the one having the SF_PK_Item primary key value that matches the SF_Item_FK value from the junction table. I would expect the SQL of the combo to be something like....
...as per your 'query snip.JPG'

Other combo properties that could matter...

ok, how do I get the combobox to display the value without having to click the down arrow first?

What's wrong with clicking the down arrow first? How do you add rows to the subform?

MarkK said:

What's wrong with clicking the down arrow first? How do you add rows to the subform?

Click to expand...

I add the Item by choosing it from a combobox on the main form. It doesn't absolutely need to be that way, but, the real problem is how to bind the combobox to each record and referecing the union query without it being in my main query.

I don't understand the problem. The control source of the combo should be the foreign key field, so the combo is bound to that. Then the row source of the combo looks up the data from the parts table. It's a very simple arrangement, and when you change the value in the combo, it changes the value of the field the combo is bound to, which is the ID of the part. AND, you can bind other textboxes on the subform row to the hidden columns ( because once the selection is made in the combo, you can only see one column ) in the combo, so the part description can appear in the subform--but not be saved with the junction table row.

Maybe post a sample db. Actually doing it is easier than describing how to do it. Then you can take a look.

Hope this helps,

MarkK said:

I don't understand the problem. The control source of the combo should be the foreign key field, so the combo is bound to that. Then the row source of the combo looks up the data from the parts table. It's a very simple arrangement, and when you change the value in the combo, it changes the value of the field the combo is bound to, which is the ID of the part. AND, you can bind other textboxes on the subform row to the hidden columns ( because once the selection is made in the combo, you can only see one column ) in the combo, so the part description can appear in the subform--but not be saved with the junction table row.

Maybe post a sample db. Actually doing it is easier than describing how to do it. Then you can take a look.

Hope this helps,

Click to expand...

Thank-you, you are right, it is simple. My problem was that i was using a compound key. I fixed that by combining them into one field in the union query

One more little issue, becuase my subform is a continuous form, how do I bind my "Item Description" Textbox to the hidden column of the combobox without it showing the same value on every row?

Set the Textbox.ControlSource property to show one of the columns from the combo like this... ...and you need to use the name of your combo, and the column numbers start at zero, so this example would show the data in the 3rd column.

Sounds like you are close to getting this working. Congrats on sticking with it.