Code not recognizing subform field

  • Thread starter Thread starter Roger via AccessMonster.com
  • Start date Start date
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 clarify,
sumALLOCATION_AMOUNT is a text box that i'm using on the subform , in the
footer to sum the total of allocation_amount. I need to reference the text
box total , if it is greater than 100.

roger
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("fcst_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
Click to expand...
 
Back
Top