Limiting entries in a data sheet of a form.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Data - 17 Jul 2006 06:03 GMT

Hello,

Just have a question. My main form has textbox: name is Condition.
Prefilled by clicking on combo box.

Basically, if the condition is GPM, then user can only enter two
entries in a subform (Orientation form). But if the condition is DBT,
then user can entry up to six entries in the subform (Orientation
form).

I don't know what property of the form(me.etc) to use restrict record
entries in the vba code?

Can anyone help me out.

Thanks

Reply to this Message

Duane Hookom - 17 Jul 2006 15:42 GMT

You can add code to the After Insert event of the subform like:

Private Sub Form_AfterInsert()
Dim intAllowRecords as Integer
Select Case Me.Parent.Condition
Case "GPM"
intAllowRecords = 2
Case "DBT"
intAllowRecords = 6
End Select
If Me.RecordsetClone.RecordCount >= intAllowRecords Then
Me.AllowAdditions = False
End If
End Sub

I would actually design a solution with a small lookup table of Conditions
and maximum number of records. I don't like to hard-code the values like
"GPM" and "2".

Signature

Duane Hookom
MS Access MVP

> Hello,
>
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

Data - 17 Jul 2006 18:58 GMT

Thanks so much Duane. Unforunately, the subform is not functioning in
access 2003 on my computer, since not installed.

Another question,

Say in the main form(clients form), textbox(condition field) displays
GPM or DBT.
Instead of subform, user clicks a command button to go to the
orientation form.
This orientation form displays a set amount of records based on the
condition field.

If GPM then displays 2 records to enter data. If DBT the dispays 6
records or entries to enter data.

I am not sure of the code to display in the orientation form,
command click event.

If parent or me.condition = GPM then
'code for number of entries in datasheet in orientation form
If parent or me.condition = DBT then
'code for number of entries in datasheet of orientation form
End If
End If

Also, can you suggest any books for beginning access vba programmers?

Thanks again

> You can add code to the After Insert event of the subform like:
>
[quoted text clipped - 35 lines]
> >
> > Thanks

Reply to this Message

Duane Hookom - 17 Jul 2006 22:20 GMT

I'm not sure why you think the subform is not installed?

If I understand, your code would be similar to the suggested code except you
would replace
Me.Parent.Condition
with
Forms!Clients!Condition

Signature

Duane Hookom
MS Access MVP

> Thanks so much Duane. Unforunately, the subform is not functioning in
> access 2003 on my computer, since not installed.
[quoted text clipped - 65 lines]
>> >
>> > Thanks

Reply to this Message