Bit of corruption with the cmd button. I deleted it and all the code and wrote it again.
Now the Docmd.runcommand accmdSaveRecord is the first thing on the cmd button only (there are dirtyflags on all the other fields if this makes a difference) the form opens by the problemID
Now the problem is this,
If I select a product that already has an entry in the problem table all, when clicking the cmd button all the fields go to #delete and a message about assigning a null value is shown (as the record doesn't save)
I have used the side bar with the pencil on it to test and if I select any product that already has an entry in the problem table the fields go to #deleted (however checking the SQL side they do actually stay in the database)
If I select a product that does not have an entry in the problem table it saves fine.
I can get round this by selecting a product I know is not in the database first, saving it using the bar with the pencil on and then changing it to the one I really want. Of course this is not the best way to do things! but it does save which really confuses me....
Do anyone know what is going on? I've not been into databases for long and this is confusing!
Thanks to everyone for their advice so far