Subform Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form that displays accounts, each with an initial $ amount
[Initial_Amt]. The form includes a subform that allocates money to each
account. Each account can be allocated many times.

I would like 2 things to happen within my subform:

1. the user must enter at least one amount [Allocation_Amt] otherwise it
will not allow you to save the record.
2. the [Allocation_Amt] must be equal to the [Initial_Amt] on the form,
whether they enter one or many. I would like a check here because I want to
ensure that the user doesn't enter more than supposed to. I was thinking of
adding a text box that adds the amounts of the subform then checks it to the
[Inital_Amt] on the form.
 
1. Make the Validation Rule property of the text box on the subform
Len([TxtboxName]) > 0 OR set the underlying Table property Required = True
for this field.

Do the same for the required field on the parent.

2. Create a recordsetclone. The recordsetclone in the before update event of
the subform. The recordsetclone will loop through until .EOF and add values.
If the values + the new value (not saved yet because we are in the before
update) > ParentForm value, then disallow the save and prompt the user.

If you need code, I'm sure I can whip it up, but this should get you
started.

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


I have created a form that displays accounts, each with an initial $ amount
[Initial_Amt]. The form includes a subform that allocates money to each
account. Each account can be allocated many times.

I would like 2 things to happen within my subform:

1. the user must enter at least one amount [Allocation_Amt] otherwise it
will not allow you to save the record.
2. the [Allocation_Amt] must be equal to the [Initial_Amt] on the form,
whether they enter one or many. I would like a check here because I want to
ensure that the user doesn't enter more than supposed to. I was thinking of
adding a text box that adds the amounts of the subform then checks it to the
[Inital_Amt] on the form.
 
I've tried setting both the len and the required field of the table to true
and it doesn't seem to promt me that I can't save the record. It continues
to let me move to the next record. Any ideas why?

Also how do I create a recordsetclone? I guess that's the code you could
whip up and I would add the code to the BeforeUpdate property.

Thanks..
 
1. Access should have prompted you at that point. If it is a required field
in the table, you *cannot* leave it blank. You are certain that the field
property in the table is set to Required = Yes??? You set it in the correct
Table(s)???

2. RecordsetClone (completely untested code below!)

Dim rst As DAO.Recordset 'You'll need a reference to DAO for this
Dim x As Double
Dim dblDiff As Double

Set rst = Me.RecordsetClone

With rst
If Not .EOF And Not .BOF Then
.MoveFirst
Do Until .EOF
x = x + !Allocation_Amt
.MoveNext
Loop
Else
x = txtAllocation_Amt
End If
End With

'Compare x to the parent form
If x > CDbl(Me.Parent.txtInitial_Amt) Then
dblDiff = x - CDbl(Me.Parent.txtInitial_Amt)
MsgBox "Total Amount(s) entered will exceed Initial Amount available by
" & dblDiff & _
". Please correct the amount entered."
'If you want to correct it for them, uncomment next line
'txtAllocation_Amt = CDbl(txtAllocation_Amt) - dblDiff
txtAllocation_Amt.SetFocus
Cancel = True
End If


--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


I've tried setting both the len and the required field of the table to true
and it doesn't seem to promt me that I can't save the record. It continues
to let me move to the next record. Any ideas why?

Also how do I create a recordsetclone? I guess that's the code you could
whip up and I would add the code to the BeforeUpdate property.

Thanks..
Troy said:
1. Make the Validation Rule property of the text box on the subform
Len([TxtboxName]) > 0 OR set the underlying Table property Required = True
for this field.

Do the same for the required field on the parent.

2. Create a recordsetclone. The recordsetclone in the before update event
of
the subform. The recordsetclone will loop through until .EOF and add
values.
If the values + the new value (not saved yet because we are in the before
update) > ParentForm value, then disallow the save and prompt the user.

If you need code, I'm sure I can whip it up, but this should get you
started.

--
Troy

Troy Munford
Development Operations Manager
FMS, Inc.
www.fmsinc.com


I have created a form that displays accounts, each with an initial $
amount
[Initial_Amt]. The form includes a subform that allocates money to each
account. Each account can be allocated many times.

I would like 2 things to happen within my subform:

1. the user must enter at least one amount [Allocation_Amt] otherwise it
will not allow you to save the record.
2. the [Allocation_Amt] must be equal to the [Initial_Amt] on the form,
whether they enter one or many. I would like a check here because I want
to
ensure that the user doesn't enter more than supposed to. I was thinking
of
adding a text box that adds the amounts of the subform then checks it to
the
[Inital_Amt] on the form.
Click to expand...
 
Back
Top