SOLVED

Getting a field from another form

Copper Contributor

Hey Guys,

 

I have a survey attached to a user profile, and I am trying to connect it to a table with recommendations based on the user's input.  I am only just started learning about Access 3 weeks ago and am really struggling on this.  The recommendations are attached to a form which I would like the user to be able to open in their profile.  

 

To accomplish this, I am trying to pass the foreign key of the survey to the recommendations form to link the two.  I have attached some code of how I am trying to do this, but if anyone knows of a better alternative, I am all ears.  I have also attached the relationships table to give you an idea how it is set up if perhaps I may have made a mistake there.  

 

Edit:

I also added a picture of the profile if that will help with the visualization of the problem.  

 

Thank you in advance for your help.  I am eager to learn about Access and finish this project with your guidance.  

 

 

4 Replies
best response confirmed by ebenton (Copper Contributor)
Solution

@ebenton 

 

Your code passes a SQL Select statement to the second form. You ONLY need to pass the appropriate fields for the person and for the recommendation. 

In addition, the second line is redundant. Your first line opens the recommendations form already filtered to that person.

 

You can pass both filtering values in a single line, something like this.


SurID = DLookup ("SurID", "Surveys", "Survey.PersonID_fk = " & Me.PersonID)
DoCmd.OpenForm "Recommendations",,, "PersonID_fk = " & Me.PersonID & " AND SurveyID_FK = " & SurID

@George Hepworth 

 

George,

 

This worked perfectly.  Thank you so much for taking the time to help me out.  

 

Cheers,

Ebenton

Congratulations on solving the problem.

Continued success with your project.
1 best response

Accepted Solutions
best response confirmed by ebenton (Copper Contributor)
Solution

@ebenton 

 

Your code passes a SQL Select statement to the second form. You ONLY need to pass the appropriate fields for the person and for the recommendation. 

In addition, the second line is redundant. Your first line opens the recommendations form already filtered to that person.

 

You can pass both filtering values in a single line, something like this.


SurID = DLookup ("SurID", "Surveys", "Survey.PersonID_fk = " & Me.PersonID)
DoCmd.OpenForm "Recommendations",,, "PersonID_fk = " & Me.PersonID & " AND SurveyID_FK = " & SurID

View solution in original post