Assign Value from Query with sum to Textbox

Question

I am trying to assign the value from a SQL Query to a text box.

I have the function tied to a ComboBox After update.

I tested the SQL by running it.

How do I assign the result to the Txtbox named prepoffEIC?

Dim MyVar2 As Integer
MyVar2 = Me.SelectedExam.Column(0)
ExamViewQry = "SELECT Sum(tblentrys.entryhours) AS TotalHoursPerFunction FROM tBleExams INNER JOIN (tBlBankList INNER JOIN (tBlExaminers INNER JOIN (tBlEntrys INNER JOIN tBlActivity ON tBlEntrys.EntryActivityID = tBlActivity.FunctionKey) ON tBlExaminers.ExaminersKey = tBlEntrys.EntryExaminerID) ON tBlBankList.BankID = tBlEntrys.EntryInstitutionID) ON (tBlBankList.BankID = tBleExams.ExamBankID) AND (tBleExams.ExamID = tBlEntrys.EntryExamID) WHERE tBlEntrys.EntryActivityID=1 AND tblEntrys.EntryExamStageID=1 AND tBleExams.ExamID=" & MyVar2

Me.prepoffEIC.ControlSource = "ExamViewQry"
Me.prepoffEIC.Requery

Answer 1

Create a query using the sql you have, but slightly modded paste it here:

PARAMETERS eid long;
SELECT Sum(tblentrys.entryhours) AS TotalHoursPerFunction
FROM tBleExams
INNER JOIN (
    tBlBankList INNER JOIN (
        tBlExaminers INNER JOIN (
            tBlEntrys INNER JOIN tBlActivity ON tBlEntrys.EntryActivityID = tBlActivity.FunctionKey
            ) ON tBlExaminers.ExaminersKey = tBlEntrys.EntryExaminerID
        ) ON tBlBankList.BankID = tBlEntrys.EntryInstitutionID
    ) ON (tBlBankList.BankID = tBleExams.ExamBankID)
    AND (tBleExams.ExamID = tBlEntrys.EntryExamID)
WHERE tBlEntrys.EntryActivityID = 1
    AND tblEntrys.EntryExamStageID = 1
    AND tBleExams.ExamID = [eid]

lets call it qryGetHours (since i dont know what you need it for.) in the after update event (also use better naming, this is quick and dirty)

dim db as DAO.Database
dim qry as QueryDef
dim rs as DAO.Recordset

set db = currentdb
set qry = db.querydefs("qryGetHours")
'this is the name of the query you made above
qry.parameters("eid").value = me.SelectedExam.Column(0)
set rs = qry.openrecordset(dbopendynaset,dbseechanges)
'dbseechanges is more for if you have a sql server backend, but i usually do
if not ( rs.eof and rs.bof) then
    rs.movefirst
    me.prepoffEIC = rs.fields("TotalHoursPerFunction").value
    'This portion assumes that you only get one record back, 
    'or if you do end up with more than one, it only goes 
    'after the first one.
else
    msgbox "Errors... Errors everywhere."
    'You will definitely want to put something meaningful 
    'here relating to it not being able to find the data you 
    'were looking for.
end if
if not rs is nothing then
    rs.close
    set rs = nothing
end if
set qry = nothing
set db = nothing
'you will always want to do this portion where you properly 
'check if a recordset exists and then close it when you are 
'done, along with closing out the querydef and database variables.