qryA access of result set in code

[swmagic]

I've heard that you can't get access in the vb code to any of the fields in an OpenQuery and that you have to do a DLookup. Yet here in some production code I found that the field [annualizedcharges] is being stuffed back into an update table. When I use "msg [annualizedcharges] the program just hangs as though it doesn't recognize the syntax.

Is the annualizedcharges field that was part of the qryA resultset available in the code and if so how?




DoCmd.OpenQuery "qryA"


strSQL = "UPDATE (tblFacultyBudgetHeader " & _
"INNER JOIN tblAnnualizedCharges ON tblFacultyBudgetHeader.FacultySSN = tblAnnualizedCharges.SSN) " & _
"INNER JOIN tblFacultyBudgetDetail ON tblFacultyBudgetHeader.BudgetNumber = tblFacultyBudgetDetail.BudgetNumber " & _
"SET " & strFieldName & "= [annualizedcharges] " & _
"WHERE (((tblFacultyBudgetDetail.AccountTypeCode)='PS-GC'))"

 

[nabiS2K]

If I understand your question correctly, you want the following:

Dim rst as DAO.Recordset
Set rst = CurrentDb.QueryDefs("qryA").OpenRecordset

strSQL = "UPDATE (tblFacultyBudgetHeader " & _
"INNER JOIN tblAnnualizedCharges ON tblFacultyBudgetHeader.FacultySSN = tblAnnualizedCharges.SSN) " & _
"INNER JOIN tblFacultyBudgetDetail ON tblFacultyBudgetHeader.BudgetNumber = tblFacultyBudgetDetail.BudgetNumber " & _
"SET " & strFieldName & "=" &  rst("annualizedcharges") & _
"WHERE (((tblFacultyBudgetDetail.AccountTypeCode)='PS-GC'))"

 

[swmagic]

Hi nabs2k:

I'm not sure. Could you demonstrate how I could access that annualizedNPI into a Dim variable?

Thanks very much for your help. It is much appreciate.

Paul

 

[swmagic]

Er, annualizedcharges, not annualizedNPI.

I'm also curious why DAO. The database is already open because this code is from a form.