PDA

View Full Version : [SOLVED] VBA to insert PART of a recset into a named range?



jsabo
09-16-2015, 03:04 PM
Hey guys,

I am wondering if there is a way to call a database, get the recset, then insert a value from recset DIRECTLY into a named range in the workbook? I ask because the SQL string I am using will return ONLY ONE record...


SQL_SCORES = "SELECT YEARQTR, PO_NUMBER, DESCRIPTION, VENDOR, BUYER, COST, SCHEDULE, QUALITY, WEIGHTED_SCORE FROM WTP_SSRM_VENDOR_SCORECARD_V WHERE PO_NUMBER = '" & Response & "' AND YEARQTR = '" & YEAR_QTR & "'"

So, the above SQL will have one entry for YEARQTR, one entry for PO_NUMBER, all the way down the line... so I want to insert the value captured for COST, SCHEDULE, and QUALITY directly into their respective named ranges in the Workbook without having to paste the recset into a sheet.

Any ideas? Thanks.

EDIT: Solved! I believe... Here's what I did:


SQL_SCORES = "SELECT YEARQTR, PO_NUMBER, DESCRIPTION, VENDOR, BUYER, COST, SCHEDULE, QUALITY, WEIGHTED_SCORE FROM WTP_SSRM_VENDOR_SCORECARD_V WHERE PO_NUMBER = '" & Response & "' AND YEARQTR = '" & Year_QTR & "'"
recset.Open Source:=SQL_SCORES, ActiveConnection:=con
End With
recset.MoveFirst
MsgBox recset!Cost


The msgbox is populated with the correct value in this case.