Limiting the number of records in a subform.

[AKBirder]

I have a subform (frm_SUB) within a form (frm_MAIN). On my main form I have a field ([Count])that requires the user to enter a number. (frm_SUB) and (frm_MAIN) are linked by a common ID.

What I want to happen is for the subform (set up in data sheet view) to generate, automatically, the number of records according to the number entered in [Count]. There will be other fields within the subform that the user has to fill in.

Ex.

(frm_MAIN)

ID = XYZ; [Count] = 3

(frm_SUB)-autopopulate-

ID QUANTITY SIZE WEIGHT
XYZ 1 -entered by user-
XYZ 2 -entered by user-
XYZ 3 -entered by user-

I am doing this to ensure that the user can only enter the number of fields specified by the [Count] field on the main form.

Any suggestions? Does that make sence? Thanks a bundle!

 

[seaport]

You can deal with that in the form_beforeInsert event.

    If Me.Recordset.RecordCount = 3 Then
        MsgBox "Maximum 3 line items can be entered!"
        Cancel = True
    End If

Seaport

 

[TheAceMan1]

How are ya AKBirder . . .

[blue]I am doing this to ensure that the user can only enter the number of fields specified by the [Count] field on the main form. . . . Does that make sence?[/blue]

Yes it makes sense! However if the user has control over record count (your headed for alot of orphan records here), whats the difference if the user enters records one by one anyway? . . . That is, typical data entry!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MajP]

Set the allow additions to false in the subform.

Then you can build a code to insert the amount of foriegn keys into your table.

This is a very general idea without any error checking. In this example Table1 would be the table where the child records exist.

Public Sub addRecords(intNumRec As Integer, varFK As Variant)
  Dim strSql As String
  Dim intcount As Integer
  DoCmd.SetWarnings (False)
  strSql = "INSERT INTO Table1 ( fkID ) values('" & varFK & "')"
  For intcount = 1 To intNumRec
    DoCmd.RunSQL strSql
  Next intcount
  DoCmd.SetWarnings (True)
End Sub

from some event
call addRecords(me.txtBxcount, me.pkID)

 

[AKBirder]

Thanks seaport... that got me part of the way.

Ultimately though, what i want Access to do is AUTOMATICALLY populate those records with the numbers 1, 2 and 3. I dont want the user to be able to mess with these. With the code you sent, they still have to enter the numbers into the subform

Any more ideas?

 

[seaport]

Here are more codes

private sub form_beforeinsert(cancel as integer)
    dim l as integer
    l=me.recordset.recordcount
    If l = 3 Then
        MsgBox "Maximum 3 line items can be entered!"
        Cancel = True
    else
        Me!LineNumber=l+1
    End If
end sub

Seaport

 

[AKBirder]

In the "else" statement, what is "LineNumber"? Access gives me a debugger error on this line.

Also, there will be instances where the [count] will not be simply the number 3, it will be whatever value was entered in the main form under [count].

Can I still use this code but replace the

= "3"

with something like

=[forms]![frm_MAIN]![count] instead?