How do I lock a sub form when a summary value has been reached?

[Will_T]

I have a subform that I need to lock when a fixed total of hours have
reached. Currently, I have a hidden text box on the subform that totals the
number of hours for a student. When the total number of hours for that
student reaches a fixed number I need to lock the subform were it will not
allow any new records to be added. The user may edit the records contained
in the subform but they cannot add.

Regarding the total number of hours, that value appears on the main form.

What would be the best way to do this?

 

[Jack Leach]

Something like this....

If NumOfHours > MaxValue Then
Me.subformcontrol.Form.AllowAdditions = False
End If

There are also AllowEdits and AllowDeletions properties.

This might be of some use as well:
http://allenbrowne.com/ser-56.html

This will lock all of the controls, but still allow unbound controls to be
used. Might come in handy

hth


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[John W. Vinson]

I have a subform that I need to lock when a fixed total of hours have
reached. Currently, I have a hidden text box on the subform that totals the
number of hours for a student. When the total number of hours for that
student reaches a fixed number I need to lock the subform were it will not
allow any new records to be added. The user may edit the records contained
in the subform but they cannot add.

Regarding the total number of hours, that value appears on the main form.

What would be the best way to do this?

I'd use the Form's BeforeInsert event to check. You don't need the hidden
textbox (though it is certainly ok if you have other uses for it); you could
use code like (assuming 8 hours is the limit)

Private Sub Form_BeforeInsert(Cancel as Integer)
If DSum("[Hours]", "[tablename]", "[StudentID] = " & Me!StudentID) _

= 8 Then

MsgBox "This student has too many hours already", vbOKOnly
Cancel = True
End If
End Sub

You may want to instead (or in addition) put similar code in the hours
textbox's BeforeUpdate event, so if a student already has 6.5 hours, and
someone tries to add 2, you'ld prevent that entry.

 

[Will_T]

I keep getting a 3464 Error, "Data type mismatch in criteria expression." I
have looked at my SSN fields of both the table and the subform and they are
both text fields. My Hours for both the table and the subform or both long
integer. My procedure is as follows;

Private Sub Form_BeforeInsert(Cancel As Integer)
If DSum("[Hours]", "[T_Communications]", "[SSN] = " & Me!SSN) > 9 Then
MsgBox "This student has too many hours already.", vbOKOnly
Cancel = True
End If
End Sub

What did I do wrong?

--
Will

I have a subform that I need to lock when a fixed total of hours have
reached. Currently, I have a hidden text box on the subform that totals the
number of hours for a student. When the total number of hours for that
student reaches a fixed number I need to lock the subform were it will not
allow any new records to be added. The user may edit the records contained
in the subform but they cannot add.

Regarding the total number of hours, that value appears on the main form.

What would be the best way to do this?

I'd use the Form's BeforeInsert event to check. You don't need the hidden
textbox (though it is certainly ok if you have other uses for it); you could
use code like (assuming 8 hours is the limit)

Private Sub Form_BeforeInsert(Cancel as Integer)
If DSum("[Hours]", "[tablename]", "[StudentID] = " & Me!StudentID) _

= 8 Then

MsgBox "This student has too many hours already", vbOKOnly
Cancel = True
End If
End Sub

You may want to instead (or in addition) put similar code in the hours
textbox's BeforeUpdate event, so if a student already has 6.5 hours, and
someone tries to add 2, you'ld prevent that entry.

 

[John W. Vinson]

I keep getting a 3464 Error, "Data type mismatch in criteria expression." I
have looked at my SSN fields of both the table and the subform and they are
both text fields. My Hours for both the table and the subform or both long
integer. My procedure is as follows;

Private Sub Form_BeforeInsert(Cancel As Integer)
If DSum("[Hours]", "[T_Communications]", "[SSN] = " & Me!SSN) > 9 Then
MsgBox "This student has too many hours already.", vbOKOnly
Cancel = True
End If
End Sub

What did I do wrong?

Left out the syntactically required quotemarks needed for Text fields. Try

DSum("[Hours]", "[T_Communications]", "[SSN] = '" & Me!SSN & "'")

Expanded for readability (don't do it this way!) that's

DSum("[Hours]", "[T_Communications]", "[SSN] = ' " & Me!SSN & " ' ")

to generate a search string like

[SSN] = '000-00-0000'