OpenArgs overwriting each record

[QntmPg]

Hi all,

I have read through what I could find on previous questions regarding using the OpenArgs property, but I'm still not able to get my form to open correctly. I'm not sure exactly where the problem is, so I'll try to describe what I'm doing in as much detail as I can. Any help is greatly appreciated!

I am building a database in Access 2003 to keep track of who participates in a study in our lab, and what study/ies they participate in. There are three main tables:
tblDemographics
DemographID (PK)
Name, etc.

tblWhichStudy
WhichStudyID (PK)
DemographID(foreign key from tblDemographics)
Study
ProjectName
EnrolledDate, etc.

tblStudyX (there are multiple of these tables, each for a specific study)
StudyXID (PK)
WhichStudyID (foreign key from tblWhichStudy)
various details on that study encounter


tblDemograph and tblWhichStudy are connected One to Many (one person can participate in many studies). tblWhichStudy and tblStudyX are connected one to one (each StudyID will refer to only one set of specific enounter details for that study - note that a person can participate in a study of the same name multiple times, but each time will have a new WhichStudyID and separate study encounter details)

A participant's information is entered on the form Demographics. The WhichStudy form is a continuous subform on the Demographics form - this way you can open a participant's record and see a list of all the studies they have participated in. Each record on the WhichStudy subform has a command button that opens a new form, specific to the study selected from the Study field, and specific to the WhichStudyID, coded through an event procedure in the OnClick property of the command button:

Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = Me![Study]
    stLinkCriteria = Me.WhichStudyID
 
 
   DoCmd.OpenForm stDocName, , , , , , stLinkCriteria
 
 
Exit_Command7_Click:
    Exit Sub
 
Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click
End Sub

The form that it opens has the following code:

Private Sub Form_Load()
Me.WhichStudyID = Me.OpenArgs
End Sub

The form opens, and does so correctly to the study specified in the drop-down box. But the record it opens is the last one that was edited. So if I have two records in tblWhichStudy, ie two entries on the WhichStudy subform, and I hit the command button for the first and enter encounter details, when I hit the command button for the second entry (which is the same study name as the first entry, so it opens the same formX, but a different WhichStudyID because it's a separate encounter detail), the form opens with the data from the first record. If I change the data, it then overwrites the data from the first record with the data from the second record.

I've also noticed that when I enter a record on the subform, then hit the button and enter details, the close the formX, I get the error: "You cannot add or change a record because a related record is required in table "tblWhichStudy"" - I don't know if this is related to the issues above, or if it is separate entirely (or if I'm not understanding how a subform works).

Any advice is welcome - please let me know if I have been unclear on any of the above or if you need more details. I've learned a lot from these forums and really apprecite the help!

QntmPg

[Denburt]

It sounds to me like you are editing the existing record as you stated unless i am mistaken you simply need to go to a new record to start adding the new info.

DoCmd.OpenForm stDocname, , , ,acFormAdd , , stLinkCriteria

[QntmPg]

Thanks for your reply - that sounds like a good idea, but when the form opens from the command button, I enter some data, then I try to close the form, I get the following error:

"You cannot add or change a record becuase a related record is required in table 'tblWhichStudy'"

If I look at tblWhichStudy, however, the record is there.

This is the same error message I get when I enter values for a record in the subform and then hit the button (if, alternatively, I enter values, go to a new subform section, ie a new record on the subform, then go back and hit the button for the first record, it does not give me that error)

Also, would adding acFormAdd mean that each time I hit the button, it goes to a new form, instead of to the record specified in the OpenArgs?

Thanks again - I think I must just be missing some crucial basic element that's holding this up!

[Denburt]

Thanks for your reply - that sounds like a good idea, but when the form opens from the command button, I enter some data, then I try to close the form, I get the following error:

"You cannot add or change a record becuase a related record is required in table 'tblWhichStudy'"

If I look at tblWhichStudy, however, the record is there.

This is the same error message I get when I enter values for a record in the subform and then hit the button (if, alternatively, I enter values, go to a new subform section, ie a new record on the subform, then go back and hit the button for the first record, it does not give me that error)

Also, would adding acFormAdd mean that each time I hit the button, it goes to a new form, instead of to the record specified in the OpenArgs?

Thanks again - I think I must just be missing some crucial basic element that's holding this up!

This definitely sounds like a structuring issue please define your tables and relationships for this form so I can try to help with this.

acFormAdd would take you to a new record not the one in the openargs. Are you trying to use openargs for filtering the form so you see 1 record in particular?

[QntmPg]

Hi Denburt,

Yes, I am trying to open a form to a specific record with OpenArgs, unless that record does not exist yet, in which case it needs to be a new record. Let me see if I can explain this better -

FormDemographics is bound to tblDemographics. FormWhichStudy is bound to tblWhichStudy, and is a continuous subform on FormDemographics. tblDemographics and tblWhichStudy are connected one to many through DemographID (PK of tblDemographics, FK of tblWhichStudy).

tblWhichStudy is connected to tblStudyX one to one through WhichStudyID (PK of tblWhichStudy, FK of tblStudyX). (there will be many tblStudyXs - tblStudyA, tblStudyB, etc., all connected the same way, so for simplicity I'm only working with one right now). FormStudyX is bound to tblStudyX (PK StudyXID).

Essentially a participant enters a study - demographic info (name, DOB, etc.) goes on FormDemographics. Each study they participate in (one person can participate in any number of studies) is listed on the continuous subform FormWhichStudy with the essential details about the participation - date they enrolled, sub-project they participated in. The purpose of the command button is to bring up a form specific to the study they participate in (StudyX) and enter details about that encounter - comments about the participant, whether certain tests were used, etc. So when a person participates in a study, that study gets entered on the subform FormWhichStudy and they get a WhichStudyID assigned, then I hit the command button to go to StudyX and I want that WhichStudyID to carry over to tblStudyX. The next time I hit that button on that record, I want to go specifically to FormStudyX with the record for that participant, defined by WhichStudyID.

I hope that has made sense, please let me know if I can clarify any of that. Thanks again for your help!