R
Roger via AccessMonster.com
Below is my code. I have a button that copies the allocation from one subform
to another subform on the main form. I'm trying to do this...
if sumAllocation total > 100 then a message pops up that the allocation
cannot be more than 100, and then exit the routine. But if sumAllocation
total is less than or equal 100 then proceed. My problem is that when it's
more than 100, it's not recognizing the routine and it's not hitting the
msgbox. Please help.
code......
Function CopyAllocations(FormFrom As Form, FormTo As Form, QuarterFrom,
QuarterTo)
Dim filtVal As String
filtVal = Forms![frmPUAllocationManagement]![fcst_MASTER_ALLOCATION_1]!Form.
sumALLOCATION_AMOUNT]
If filtVal > "100" Then
MsgBox "Your allocation cannot be more than 100. Please Correct."
Exit Function
Else
MsgBox " else... allocation not > 100"
End If
Call SetStartup
If QuarterFrom = QuarterTo Then
MsgBox "You cannot copy an allocation quarter to itself. Operation
Cancelled"
Exit Function
End If
Dim RSFrom As Recordset, RSTo As ADODB.Recordset
Set RSTo = New ADODB.Recordset
Set rs = New ADODB.Recordset
Set RSFrom = FormFrom.RecordsetClone
' Move to the first record in the recordset.
RSFrom.MoveFirst
strIn = RSFrom("OTIS_ALLOCATIONLU_ID")
'RSFrom.MoveNext
'Do While Not RSFrom.EOF
'strIn = strIn & "," & RSFrom(0)
'RSFrom.MoveNext
'Loop
strSQL = "DELETE fcst_MASTER_ALLOCATION " & _
"WHERE fcst_MASTER_ALLOCATION.QUARTER=" & QuarterTo & " AND
fcst_ALLOCATIONLU_ID=" & strIn & ";"
'MsgBox strSQL
rs.Open strSQL, conn, 3, 3
strSQL = "INSERT INTO fcst_MASTER_ALLOCATION (KEY,fcst_ALLOCATIONLU_ID,
ALLOCATION_AMOUNT, QUARTER, ACTIVE, ALLOCATION_TYPE ) " & _
" SELECT fcst_MASTER_ALLOCATION.KEY, fcst_MASTER_ALLOCATION.
fcst_ALLOCATIONLU_ID, fcst_MASTER_ALLOCATION.ALLOCATION_AMOUNT, " & QuarterTo
& ", fcst_MASTER_ALLOCATION.ACTIVE, ALLOCATION_TYPE " & _
" FROM fcst_MASTER_ALLOCATION " & _
"WHERE (((fcst_MASTER_ALLOCATION.fcst_ALLOCATIONLU_ID) In (" & strIn & "))
AND ((fcst_MASTER_ALLOCATION.QUARTER)=" & QuarterFrom & "));"
rs.Open strSQL, conn, 3, 3
FormTo.Requery
End Function
to another subform on the main form. I'm trying to do this...
if sumAllocation total > 100 then a message pops up that the allocation
cannot be more than 100, and then exit the routine. But if sumAllocation
total is less than or equal 100 then proceed. My problem is that when it's
more than 100, it's not recognizing the routine and it's not hitting the
msgbox. Please help.
code......
Function CopyAllocations(FormFrom As Form, FormTo As Form, QuarterFrom,
QuarterTo)
Dim filtVal As String
filtVal = Forms![frmPUAllocationManagement]![fcst_MASTER_ALLOCATION_1]!Form.
sumALLOCATION_AMOUNT]
If filtVal > "100" Then
MsgBox "Your allocation cannot be more than 100. Please Correct."
Exit Function
Else
MsgBox " else... allocation not > 100"
End If
Call SetStartup
If QuarterFrom = QuarterTo Then
MsgBox "You cannot copy an allocation quarter to itself. Operation
Cancelled"
Exit Function
End If
Dim RSFrom As Recordset, RSTo As ADODB.Recordset
Set RSTo = New ADODB.Recordset
Set rs = New ADODB.Recordset
Set RSFrom = FormFrom.RecordsetClone
' Move to the first record in the recordset.
RSFrom.MoveFirst
strIn = RSFrom("OTIS_ALLOCATIONLU_ID")
'RSFrom.MoveNext
'Do While Not RSFrom.EOF
'strIn = strIn & "," & RSFrom(0)
'RSFrom.MoveNext
'Loop
strSQL = "DELETE fcst_MASTER_ALLOCATION " & _
"WHERE fcst_MASTER_ALLOCATION.QUARTER=" & QuarterTo & " AND
fcst_ALLOCATIONLU_ID=" & strIn & ";"
'MsgBox strSQL
rs.Open strSQL, conn, 3, 3
strSQL = "INSERT INTO fcst_MASTER_ALLOCATION (KEY,fcst_ALLOCATIONLU_ID,
ALLOCATION_AMOUNT, QUARTER, ACTIVE, ALLOCATION_TYPE ) " & _
" SELECT fcst_MASTER_ALLOCATION.KEY, fcst_MASTER_ALLOCATION.
fcst_ALLOCATIONLU_ID, fcst_MASTER_ALLOCATION.ALLOCATION_AMOUNT, " & QuarterTo
& ", fcst_MASTER_ALLOCATION.ACTIVE, ALLOCATION_TYPE " & _
" FROM fcst_MASTER_ALLOCATION " & _
"WHERE (((fcst_MASTER_ALLOCATION.fcst_ALLOCATIONLU_ID) In (" & strIn & "))
AND ((fcst_MASTER_ALLOCATION.QUARTER)=" & QuarterFrom & "));"
rs.Open strSQL, conn, 3, 3
FormTo.Requery
End Function