Primary Key and reltionship set up

[QntmPg]

Hi Again - This is sort of a follow-up to my General Form and command button design question, but it's a new problem now. I was able to get a form to open based on a drop-down menu selecction with the following code:

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

This worked successfully to open the form I select from the drop-down menu. However, it does not open it to the correct record. I have TblDemographics to tblWhichStudy through DemographID (pk of tblDemographics), then I have TblWhichStudy to TblStudyA, StudyB, StudyC, etc. through WhichStudyID (primarykey of tblWhichStudy). Form WhichStudy is a subform on Form Demographics. Form StudyA, etc., is a separate form opened through the command button. DemographID successfully gets pulled into tblWhichStudy, but neither WhichStudyID nor DemographID get pulled into StudyA table. I have the row source for each of these values in StudyA table as:

SELECT [Demographics Table].DemographID FROM [Demographics Table];
and
SELECT [WhichStudy Table].WhichStudyID FROM [WhichStudy Table];

Can anyone identify what I am setting up wrong? Thank you!

[hyperpau]

Hi Again - This is sort of a follow-up to my General Form and command button design question, but it's a new problem now. I was able to get a form to open based on a drop-down menu selecction with the following code:

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

This worked successfully to open the form I select from the drop-down menu. However, it does not open it to the correct record. I have TblDemographics to tblWhichStudy through DemographID (pk of tblDemographics), then I have TblWhichStudy to TblStudyA, StudyB, StudyC, etc. through WhichStudyID (primarykey of tblWhichStudy). Form WhichStudy is a subform on Form Demographics. Form StudyA, etc., is a separate form opened through the command button. DemographID successfully gets pulled into tblWhichStudy, but neither WhichStudyID nor DemographID get pulled into StudyA table. I have the row source for each of these values in StudyA table as:

SELECT [Demographics Table].DemographID FROM [Demographics Table];
and
SELECT [WhichStudy Table].WhichStudyID FROM [WhichStudy Table];

Can anyone identify what I am setting up wrong? Thank you!

You must Add the WHERE statement in your SELECT Statement

ex:
SELECT [WhichStudy Table].WhichStudyID FROM [WhichStudy Table] WHERE [WhichStudy Table]![DemographID]=[Demographics Table]![DemographID]

[QntmPg]

Thank you! That worked :)

[hyperpau]

Thank you! That worked :)

No problem. Glad it worked.

[QntmPg]

Hi again,

I apologize, I jumped the gun there - it did not actually work. What it does it prompt me for the value that I specified in the Where condition. For example, I have DemographID set up as:

SELECT [WhichStudy Table].DemographID FROM [WhichStudy Table] WHERE [WhichStudy Table]!DemographID=[Demographics Table]!DemographID;

When I re-open the form, it does not go to the specific record. So I went to the table to look at what's in there, and neitehr DemographID nor WhichStudyID carried over. So I clicked on the DemographID field and it prompts me for [Demographics Table]!DemographID

The same thing happens for WhichstudyID, which I have set up as:
SELECT [WhichStudy Table].WhichStudyID FROM [WhichStudy Table] WHERE [WhichStudy Table]!DemographID=[Demographics Table]!DemographID;

Again, the relationship is set up that the WhichStudyID is the primary Key in the whichStudy Table, and the foreign key in the StudyA table, in a one-to-one relationship. When I tried it last week I think I entered a value in the parameter box, and then of course it opened correctly. But how do I get it to automatically read the WhichStudyID and DemographID from the previous form? Could you explain how the above statement is supposed to work (the logic behind it), then i might be able to figure out how to make the table know which value to pull in.

Thanks again, I really appreciate the assistance!

[hyperpau]

Hi again,

I apologize, I jumped the gun there - it did not actually work. What it does it prompt me for the value that I specified in the Where condition. For example, I have DemographID set up as:

SELECT [WhichStudy Table].DemographID FROM [WhichStudy Table] WHERE [WhichStudy Table]!DemographID=[Demographics Table]!DemographID;

When I re-open the form, it does not go to the specific record. So I went to the table to look at what's in there, and neitehr DemographID nor WhichStudyID carried over. So I clicked on the DemographID field and it prompts me for [Demographics Table]!DemographID

The same thing happens for WhichstudyID, which I have set up as:
SELECT [WhichStudy Table].WhichStudyID FROM [WhichStudy Table] WHERE [WhichStudy Table]!DemographID=[Demographics Table]!DemographID;

Again, the relationship is set up that the WhichStudyID is the primary Key in the whichStudy Table, and the foreign key in the StudyA table, in a one-to-one relationship. When I tried it last week I think I entered a value in the parameter box, and then of course it opened correctly. But how do I get it to automatically read the WhichStudyID and DemographID from the previous form? Could you explain how the above statement is supposed to work (the logic behind it), then i might be able to figure out how to make the table know which value to pull in.

Thanks again, I really appreciate the assistance!

You just have to specify the WHERE condition to be bound by the form instead of the table.

ex: WHERE DemographID = Forms![Name Of the Form]![Name of the control for the DemographID]

[QntmPg]

Thanks for the reply. When you say "[Name of the control for the DemographID]" - does that mean that demographID has to be a field on the form? DemographID is in all tables, as a primary key in the Demographics Table, and then a foreign key in all others, but it does not actually appear on the forms. Am I misunderstanding your comment?

Thanks again!

You just have to specify the WHERE condition to be bound by the form instead of the table.

ex: WHERE DemographID = Forms![Name Of the Form]![Name of the control for the DemographID]

[QntmPg]

Ok, 1 success, 1 failure remains...

I got the form to open to the correct record by using the OpenArgs property, as follows:

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
 

Previously I was using it in the Where Condition property and it did not open. So I understand why it's opening now. I still, however, cannot get the WhichStudyID to filter down into the table for StudyA. Could you please clarify why the OpenArgs property opens the form to the correct record through WhichStudyID, but then WhichStudyID doesn't appear in the table for that form? I tried using the following:

SELECT [WhichStudy Table].WhichStudyID FROM [WhichStudy Table] WHERE [WhichStudy Table]![DemographID]=[Demographics Table]![DemographID]

I'm having a hard time understanding exactly what this does though - if neither WhichStudyID nor DemographID are being pulled into StudyA table, how would the above WHERE statement work? Why is a Where statement needed at all?

Thank you again for your help, I appreciate the explanations as I try to understand what it is I'm coding :)

[hyperpau]

Thanks for the reply. When you say "[Name of the control for the DemographID]" - does that mean that demographID has to be a field on the form? DemographID is in all tables, as a primary key in the Demographics Table, and then a foreign key in all others, but it does not actually appear on the forms. Am I misunderstanding your comment?

Thanks again!

If you want to open the other form and go straight to that record currently showing in the form, you have to set the where condition or the open arguments to match the primary key of that record in the 1st form. When I say [Name of the Control of the DemographID], yes I mean it is a control on the form. You need to have that control on the form. If you don't want to show it, just change it's visible property set to NO / False, but it must be present on that form. (even if you can only see it in the design view of the form).

Again,
if you want to open another (2nd) form and go to that record which is related to the record in the 1st form using the Primary key of that record, which I believe is teh DemographID. got it? :)