Return values from Dynamic table/field selection

[liddlem]

We need to complete a number of 3rd-party application PDF document for each student at the beginning of each term.
For example,
We need to fill in a separate application PDF document for each of the following:
1. funding for each boarding student.
2. To request that Centerlink arrange (and pay for) travel to and from the school
3. That certain funds are paid directly to the school and not to the guardian.
etc. etc

In order to facilitate this, I have created a process as follows.

The user creates a 'template' based on each of the application forms.
This template records
- The document name (Govt Code)
- Page Number
- Section number
- SubSection number
- The question (as it appears on the offical document)
- an 'Answer' text box
If there is a standard answer 'IE the school's name' then its typed into the template.
If the answer can be derived from another table/field (IE 'Student name' or 'Guardian Address') then leave the 'answer' texbox blank,
and rather choose the source table name and the field name from 2 combo boxes. (CBO_Src_Table and CBO_Src_Field)
- a bunch of other fields

At 'runtime' (when a new document needs to be completed for the student), the user opens a form called 'applications'
The user
a. Selects the document, the student and guardian1 and guardian 2 (if applicable)
b. They then press a 'make application' button which copies the records from the 'template' document into the 'application' table and links the Student and guardian accordingly.

I would then like the system to populate all the fields where the data is sourced from another table/field accordingly.
I think that it would be best to do this in the 'on current' event as the use might change the guardian/student at a later date.
(I will prevent changes once the application is submitted.)

The following is (understandably) where I am running into trouble

If Not IsNull(Me.Fld_Name) Then
    MySql = "MyAnswer = DLookup(""" & Me.Fld_Name & """" & "," & """" & Me.Tbl_Name & """" & "," & """" & "ID_Student"" =" & Me.Parent.ID_Student & ")"
    Debug.Print MySql
    me.Answer = MyAnswer
end if

On investigating this problem, there have been suggestions to create a parameter query for each possibility, but the permutations are way too many for me to do that.
What is the most efficient / easiest way for me to return a value(s) dynamically, based on the options that the user selects in the CBO_Src_Table and CBO_Src_Field boxes?

I'm using Win10 / Office 2016 / SQL 2016

I probably won't get to see any responses before Monday 9/Oct/17
Thanks

 

[arnelgp]

remove the MySQL from your code:

Me.Answer = Nz(DLookup("FieldName", "TableName", "ID_Student=" & Me.Parent.ID_Student), "")


***
Where:
FieldName is the name of the field on TableName that
you want to return

TableName is the name of your table.

***

If the fieldname and tablename are both available
on the combos you mentioned:

Me.Answer = Nz(Dlookup([CBO_Src_Field], [CBO_Src_Table], "ID_Student=" & Me.Parent.ID_Student), "")

 

[liddlem]

Of course that's the answer!
I was obviously in 'over-analyse' mode again.
Thanks for your input.