Hello All,
Long time reader, first time poster. I'm attempting to write a quick macro to determine the required build quantity for a work order (and later I will prevent the ticket from being submitted if the GOOD_QTY is over the required quantity as this drives some of our materials negative which is a cost layers nightmare).
The simple macro to display the required quantity works except I cannot get the SQL query to properly utilize the work order BASE_ID to return data from the workorder table. If I hard code the base id into the query, everything works.....I'm at my small wits end. See below:
Dim strCnn
Dim SQL
Dim rs
Dim REMAIN_QTY
Dim WOBASEID
Dim WOLOTID
Dim WOSPLITID
Dim WOSUBID
Dim strServerName
Dim strDatabase
Dim strUserName
Dim strPassword
strServerName = "Removed"
strDatabase = "Removed"
strUserName = "Removed"
strPassword = "Removed"
strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"
set rs = CreateObject("ADODB.Recordset")
set WOBASEID = NEW_BASE_ID
set WOLOTID = NEW_LOT_ID
set WOSPLITID = NEW_SPLIT_ID
set WOSUBID = NEW_SUB_ID
SQL = "SELECT ROUND((WORK_ORDER.DESIRED_QTY - WORK_ORDER.RECEIVED_QTY) ,1) AS R_QTY FROM WORK_ORDER WHERE WORK_ORDER.BASE_ID = ' " & WOBASEID & " ' AND WORK_ORDER.LOT_ID = ' " & WOLOTID & " ' AND WORK_ORDER.SPLIT_ID = ' " & WOSPLITID & " ' AND WORK_ORDER.SUB_ID = ' " & WOSUBID & " ' "
rs.open SQL, strcnn
if not rs.eof then
rs.movefirst
REMAIN_QTY = rs("R_QTY")
end if
rs.close
MACRO_SUCCESS = FALSE
MACRO_MESSAGE = REMAIN_QTY
