Query not updateable, can't see why.

[bentheimmigrant]

I know this is a very common question, so of course I have come across the list of reasons this could be happening:
http://allenbrowne.com/ser-61.html

The setup is fairly straightforward. I have two tables, "Calc" and "Mol_fraction". There is a field in both called "Fluid_ID". The values of "Fluid_ID" in fact come from the table "Fluids", where it is the primary key.

I have a combobox on a form for the user to select the "Calc_ID", which is the primary key of the "Calc" table.

So the query is as follows (built using the query builder):

SELECT Mol_fraction.*
FROM Calc INNER JOIN Mol_fraction ON Calc.Fluid_ID = Mol_fraction.Fluid_ID
WHERE (((Calc.Calc_ID)=[Forms]![Main_Form]![Calc_box]))
ORDER BY Mol_fraction.Order;

However, when I try to type into the bound fields, it tells me "This Recordset is not updateable". I can only assume from the list that it's due to "The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields."

So I tried using "Fluids" instead, where there is already a pre-defined relationship between the "Fluid_ID" fields:

SELECT Mol_fraction.*
FROM Fluids INNER JOIN Mol_fraction ON Fluids.Fluid_ID = Mol_fraction.Fluid_ID
WHERE (((Fluids.Fluid_ID)=DLookUp("Fluid_ID",[Calc],"Calc_ID = " & [Forms]![Main_Form]![Calc_box])))
ORDER BY Mol_fraction.Order;

This also is not updateable.

The "Fluid_ID" in "Mol_fractions" is set to "Indexed (allow duplicates)". I assume this is correct?

Help! I am lost and confused!

The query properties box:

 

[Ranman256]

SOME joins on 2 tables are NOT updateable. Some are.
depends on your parent/ child relationship. (not the allow edit flag)

 

[plog]

Forms that allow users direct interaction with data (Add/Edit/Delete) should not be based on queries. Your forms should be based on tables.

If you want to edit data in Fluids, you should have a form based on Fluids, if you want to edit data in Mol_fraction, you should have a form based on it.

 

[arnelgp]

how about if you re-arrange it:

SELECT Mol_fraction.*
FROM Mol_Fraction LEFT JOIN Fluids Mol_fraction.Fluid_ID = Fluids.Fluid_ID
WHERE Fluids.Fluid_ID IN (SELECT Fluid_ID FROM [Calc] WHERE Calc_ID = " & [Forms]![Main_Form]![Calc_box])
ORDER BY Mol_fraction.Order;

 

[bentheimmigrant]

Forms that allow users direct interaction with data (Add/Edit/Delete) should not be based on queries. Your forms should be based on tables.

If you want to edit data in Fluids, you should have a form based on Fluids, if you want to edit data in Mol_fraction, you should have a form based on it.

I feel I am missing something here. How can I have a subform that only shows selected records from a table without using a query?

how about if you re-arrange it:

SELECT Mol_fraction.*
FROM Mol_Fraction LEFT JOIN Fluids Mol_fraction.Fluid_ID = Fluids.Fluid_ID
WHERE Fluids.Fluid_ID IN (SELECT Fluid_ID FROM [Calc] WHERE Calc_ID = " & [Forms]![Main_Form]![Calc_box])
ORDER BY Mol_fraction.Order;

This didn't quite work, so I fixed it. I assume it maintains your intentions:

SELECT Mol_Fraction.*
FROM Mol_Fraction LEFT JOIN Fluids ON Mol_Fraction.Fluid_ID = Fluids.Fluid_ID
WHERE (((Fluids.Fluid_ID) In (SELECT Fluid_ID FROM [Calc] WHERE Calc_ID = [Forms]![Main_Form]![Calc_box])))
ORDER BY Mol_Fraction.Order;

This brought up the correct data, but was still not updateable.

Could there be something in the table/field settings?

 

[arnelgp]

how about this:

SELECT Mol_Fraction.*
FROM Mol_Fraction LEFT JOIN Fluids ON Mol_Fraction.Fluid_ID = Fluids.Fluid_ID
WHERE ((([COLOR=Blue]Mol_Fraction[/COLOR].Fluid_ID) In (SELECT Fluid_ID FROM [Calc] WHERE Calc_ID = [Forms]![Main_Form]![Calc_box])))
ORDER BY Mol_Fraction.Order;

 

[bentheimmigrant]

Update: I have fixed it

I un-indexed "Fluid_ID" in "Mol_fractions". The query then worked. I don't know if that was down to arnelgp's rearrangement as well, but it's working so I'm not going to find out.

plog: I would still be very interested to understand what you mean by not using queries for editing data. This query is on a continuous subform, so it's not like a single data point where I can link a table and write the PK into a textbox when I open the form.

 

[plog]

I feel I am missing something here. How can I have a subform that only shows selected records from a table without using a query?

If you want to tie it to the main form, you establish a child/parent relationship: https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b

If the criteria doesn't involve the main form, you can apply a filter to the form: https://msdn.microsoft.com/en-us/library/office/ff194672.aspx?f=255&MSPPError=-2147217396

 

[bentheimmigrant]

If you want to tie it to the main form, you establish a child/parent relationship: https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b

If the criteria doesn't involve the main form, you can apply a filter to the form: https://msdn.microsoft.com/en-us/library/office/ff194672.aspx?f=255&MSPPError=-2147217396

I had assumed that was what filter was for, but I'd tried, and it just dumped the whole table into the subform. I must've had an error in my syntax or something.