How do I get a value from a SQL statement into a recordset?
Posted on 2005-05-12
Dim rsIB, countIBGroup
countIBGroup = 1
Set CMD.ActiveConnection = Conn
This is my code, but it doesn't work. Even though my SQL statement should give me the value in the LOOKUP_ORDER field, but
when I debug my code, I don't get any value into the rsIB variable
The LOOKUP_ORDER field is a numeric field, it should give me the highest number in that field, but it doesn't...
Can you help?
CMD.CommandText = "SELECT TOP 1 LOOKUP_ORDER FROM LOOKUP_VALUES WHERE LOOKUP_NAME like 'IB_GROUP%' ORDER BY LOOKUP_ORDER DESC"
CMD.CommandType = adCmdText
'DMG (AH) -- Increment the highest LOOKUP_CODE value by one, so the new IB_GROUP records added to LOOKUP_VALUES are UNIQUE
Set rsIB = CMD.Execute
countIBGroup = IIf(IsNull(rsIB), 1, rsIB + 1)
CMD.CommandText = "Insert Into LOOKUP_VALUES (LOOKUP_NAME, LOOKUP_CODE, LOOKUP_DESC, LOOKUP_ORDER) Values('IB_GROUP', '" & CStr(countIBGroup) & "', '" & Replace(txtAddIBGroup.Text, "'", "''") & "', '" & CStr(countIBGroup) & "')"
Call AddAudit("FormAdminIBGroup", CMD.CommandText)