Subform not showing new records (1 Viewer)

[kevnaff]

Hi All.

I was working on a database in the old .mdb/.mde formats. I have created a new database and imported all objects in to the new database and saved as .accdb and created .accde front end.

I have a form that contains 3 subforms within it, 2 of which are working as they should. The other one is only showing previously added records, and does not allow any additions to be made. I have checked in the property sheet and edits and additions are allowed. I have opened the subform independently and it does not allow any records to be added.

When clicking the command button to open the Master Form, a message pops up in the bottom left corner saying 'This recordset is not updateable'.

It seems as though this problem has only occurred since importing all of the objects in to a new database.

Does anybody have any ideas what may be causing this issue?

 

[arnelgp]

the answer lies within your form/subform.
if it is based on query, check if it is updateable or not.

 

[Pat Hartman]

Open the query directly. Does it allow updates? Did you change the query to include any kind of grouping?

 

[kevnaff]

Hi Both,

I have opened the query and it does not allow updates, see below:

I have no option to add a record. When I try to edit a record 'This recordset is not updateable' pops up in the bottom left corner.

Below are the relationships in my query:

The Contracts part of our system is something that I've never personally had to use. I took over the database 18 months ago. It is actively used by our Administator and they have only had this issue in the past week or so, which is when I imported everything in to a new database. When I done the import I got a message along the lines of 'some functions are not compatible and may not be available'. I have then since saved this .accdb back down to .mdb as I am having network troubles when using the .accdb.

Thanks

 

[CJ_London]

is that your recordsource to your subform?

The rule is one form one table, you have three.

Assuming Contracts is the recordsource to your main form, you don't need it in the subform - the subform linkchild/master properties provide the link/relationship (both should be contractID)

As to the other two tables, depends on what you want to add new - I suspect you will need to base it on contractsubform and in that have another subform for importequipment (If contractsubform needs to be continuous, put that subform in the footer or header)

One other thing to check (tho ' I doubt it will work) is to change the recordset type from dynaset to dynaset - inconsistent updates

It seems as though this problem has only occurred since importing all of the objects in to a new database.

Maybe, but importing this same setup would have had the same problem in the original db, so the likelihood is a change has been made since you imported - go back to the original db and see what you have there.

 

[arnelgp]

you right-click on the Joining line between
ContractSubform table and ImportEquipment table.
Edit the relationship and choose "all records from ContractSubform".
this will create a Left Join.

 

[kevnaff]

Hi Both,

Thanks very much for your help.

CJ, I opened up an archived version of the DB and it was working OK. I therefore changed the recordset to dynaset - inconsistent updates as you recommended and it the subform now allowed me to add a new record.

arnelgp - I then changed the subform back to Dynaset and created a left join and this also solved the issue.

Now the question is, which fix should is the one I should use?

 

[arnelgp]

it's not about dynaset inconsistent or not.
it' the way you Joined your tables.
Using Inner Join will render your recordset non-updateable when
there is no Matching record on one-side.

you check the Original recordset, how it was joined.

 

[kevnaff]

Hi arenlgp,

The archived DB which works fine has the following join between the ContractSubform table and ImportEquipment table.

I will change the join on the new database as it works OK. Thanks

 

[Pat Hartman]

The relationship to ImportEquipment is WRONG. Relationships are from a data field to the PK of the related table. NOT data field to data field. Either make {Code no] the PK OR use EquipID rather than CodeNo in the other table.

Another word to the wise. Be consistent in your naming. It will save you from so many typos and so much mind strain, it will be worth it to learn to be disciplined.

The rule is one form one table, you have three.

This isn't an actual "rule". It is perfectly fine to include lookup tables in your queries. Just be careful to set the locked property of their controls to Yes to prevent accidental u[dating.

The "rule" is that a form should not update more than one table but it's query can contain as many lookups as are needed. Just don't be sloppy about them by allowing lookup values to be updated. Access will allow this logic error so YOU have to prevent it.

 

[kevnaff]

The relationship to ImportEquipment is WRONG. Relationships are from a data field to the PK of the related table. NOT data field to data field. Either make {Code no] the PK OR use EquipID rather than CodeNo in the other table.

Another word to the wise. Be consistent in your naming. It will save you from so many typos and so much mind strain, it will be worth it to learn to be disciplined.


This isn't an actual "rule". It is perfectly fine to include lookup tables in your queries. Just be careful to set the locked property of their controls to Yes to prevent accidental u[dating.

The "rule" is that a form should not update more than one table but it's query can contain as many lookups as are needed. Just don't be sloppy about them by allowing lookup values to be updated. Access will allow this logic error so YOU have to prevent it.

Hi Pat. Thanks for your advice.

As I have taken over the DB I will have to try and correct all of these relationship issues as I have come across a few more. I have just had the same issue whereby a query that worked OK in the previous version, but since exporting the database in to a new 2010 .accdb, and then exporting it out as a 2003 .mdb, it has stopped working. I change the recordset type on the form from Dynaset to Dynaset (Incosistent Updates) and this solves the issue, however I wanted to look in to the relationships as you pointed out.

As you can see below, there is a data field to data field relationship again. However I have noticed that from the image above, it was showing the primary key of ImportEquipment as the EquipID field. Yet this relationship does not show that to be the case, and having checked any other query, it seems that this is the same. Despite me not editing the backend where the tables are stored, the primary key symbol has disappeared.

Would the solution to the above be to do the following:

Add EquipID to the ScrapNotify table

Create a join between the primary key EquipID in the ImportEquipment, to the EquipID in the ScrapNotify table


Would this solve the issue?

I am more bamboozled by the fact the primary key symbol is not showing next to EquipID in ImportEquipment.

 

[Pat Hartman]

That sometimes happens if the database becomes corrupted. Try a C&R to see if that fixes it. Then put the PK back if needed. If you have to, create a new, empty database and import the tables and relationsips. Rebuild any that are missing.

Is [Code No] in the ImportEquipment table unique? Perhaps there used to be a unique index on this field. Try to add one now.