How to update form and subform at the same time

[mdavid]

Hi, I want to update the content of a form and it's subform at the same time. The updates to the form work ok but updating the subform fields I receive:

"You cannot add or change a record because a related record is required in table"

How can I update both forms simultainusly?


Thanks for any help
David

[Bob Fitz]

Unlike you, your computer can not do two things at exactly the same time. The message you are getting is usually displayed when you have two tables with a one to many relationship, represented as a main form (the one side) with a subform (the many side) when you have tried to enter a record in the subform without a matching record in the main form.

[mdavid]

Thanks for the explanation.
What do I need to do between adding to the main form table and the sub form table so that both tables get updated?

Thanks
David

[Bob Fitz]

Thanks for the explanation.
What do I need to do between adding to the main form table and the sub form table so that both tables get updated?

Thanks
David

You shouldn't need to do anything if the main and subform are set up correctly.
Can you post a copy of your db as a zip file.

[mdavid]

Hi I've succeeded to update the tables with this code:

Private Sub btnCopyMemp_Click()
Dim FPComp, FPPrep As DAO.Recordset
Dim strSQL As String
Set FPComp = CurrentDb.OpenRecordset("FPComplaints", dbOpenDynaset)
If Forms!MempBercMainFrm1!FloraPComplaintWithPreparations.Form.txtTimesRecorded < 0 Then
             FPComp.AddNew
             FPComp("SpeciesID") = Me![txtSpeciesID]
             FPComp("ComplaintID") = Me![txtComplaintID]
             FPComp("Complaint") = Me![txtComplaint]
             FPComp("TimesRecorded") = 1
             FPComp.Update
End If
FPComp.Close
Set FPPrep = CurrentDb.OpenRecordset("FPPreparation", dbOpenDynaset)
FPPrep.AddNew
FPPrep("ComplaintID") = Me![txtComplaintID]
FPPrep("BercMempSpeciesID") = Forms!MempBercMainFrm1.txtSpeciesID
FPPrep("BercMempCompID") = Me![txtComplaintID]
FPPrep("Complaint") = Me![txtComplaint]
FPPrep("Preparation") = Me![txtPrep]
FPPrep("Administration") = Me![txtAdmin]
FPPrep.Update
FPPrep.Close


Set FPComp = Nothing
Set FPPrep = Nothing
End Sub

My problem now is to see the updates on the form - single form - and suform - continuous form. I tried:

strSQL = "SELECT [SpeciesID], [ComplaintID], [Complaint], [System], [TimesRecorded], [Actions], " & _
         "[InformationSource], [ScientificStudies], [MempComplaintID], FPComplaints.[BercComplaintID] " & _
         "FROM FPComplaints Where [ComplaintID] = " & Me![txtComplaintID].Value
 DoCmd.RunSQL strSQL

but I understand runSQL is only for action sql statments.

So what would be the best way for me to update these 2 forms?

Also appreciate any comments on the update code above - I think there might have been a simpler way to do it?

Thanks for any help
David

[Minty]

As Bob stated - this is completely unnecessary if the main form and sub form are set up correctly.
Access will take care of this automatically by using the Master Child relationship properties in the sub form container control.

Edit - you also appear to be storing the same information in two related tables - which means your table design isn't right.

[mdavid]

Hi Minty, Thanks for the reply.
Could you give me an outline of what should be in the btnCopyMemp_Click routine above - if my Master Child relationships are set up properly?

As far as storing the same info in two tables, the reason is that this system is for copying/editing data for a web site so I want to keep the original data which is read only and the second table can be edited before being loaded to the website.

Thanks for any help.
David

[Minty]

It sounds more like you need a data manipulation than a form / subform task.
Would you not be better to use a list multi select list box to select the records you want to edit , bulk copy them to your "editing" table and then reverse the process once done?

[mdavid]

Thanks Minty, appreciate your suggestions - I get too bogged down in my ideas to see the big picture.
Guess I should explain a little about the project, so we both have a better understanding.

I have a data base of medicinal plants - around 800 plants with 4,000 medicinal uses - this is an ethnobotanical database documenting the plant details, their use by traditional healers and any scientific research. The medicinal complaints are grouped by species, and the same complaint is often reported several times by different healers each with his method of application. We want to keep all this information but group similar complaints under one complaint heading and under this heading list all the different preparations, applications, traditional healers etc.

That's the basic outline of what I'm trying to do. I think it's a good idea to select the main medicinal complaint and all it's related complaints and copy them as a group instead of each one separately.
I want to keep both the original data and the edited data, so that I have the possibility of comparing and undoing any mistakes, so I'm not interested in reversing the process after the editing is done.
Hope this is not to much information, but I think we both need to understand what I'm trying to do.
Now could you suggest (in code if possible) how to bulk copy a group of medicinal complaints - the main complaint is in a single form and all its related complaints appear in a subform - continuous form.

Thanks for any help
David