471,579 Members | 1,764 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,579 software developers and data experts.

How to restrict the number of records added to a table by a subform??

I am working on an audits database.
The main table, Audits, has an AuditID primary key.
Another table, 1:1 relationship, ProgramAudits, has AuditID as a
foreign key.

I have a master/main form for Audits data, and a child/sub form for
ProgramAudits data.

I put a subform for ProgramAudits onto the Audits table form, and the
following code in the OnCurrent event to grab the AuditID field from
the main form and put it into the AuditID field of the ProgramAudits
subform:

Private Sub Form_Current()
' automagically grab the AuditID and put it in the right field
Me.AuditID = Form.Parent.[AuditID]
Me.Refresh
End Sub

Me is the ProgramAudits subform.

I only want the ProgramAudits table to have one record per Audits table
record, is there a better event or means to achieve what I want to do??
Or is there some uncomplicated way to check if the ProgramAudits table
already has a record corresponding to an Audits table record??

Should I enforce my end users to always manually add the one new
ProgramAudits record per each Audits table record??

One reason I ask is that the above event causes a new record to be
added each time the Audits table main form is opened, and I don't want
to do that, setting AllowAdditions to false for the subform doesn't
work.

I've looked on the forum but so far have not found the right way to
search the groups to know if anyone has solved this question.

Thank you for reading this,
Tom

Feb 7 '06 #1
6 2715
I tried George Nicholson's code as shown in this thread,

http://groups.google.com/group/micro...46a031431ce02c

but the subform disappears if AllowAdditions = False!!

Can I somehow prevent the subform from disappearing??

Thank you, Tom

Feb 7 '06 #2
After reading some more, it seems there's no real way to do what I
want, so long as the ProgramAudits table is shown on the Audits form as
a ProgramAudits subform.

Perhaps there is some other way I can programmatically set up the
desired record in teh ProgramAudits table each time a new Audits table
record is added.

I will work on this, since I am using custom nav buttons for the Audits
table, one of them is cmdAddNew, maybe this will work better, but I
hope that someone could point me toward displaying this on a subform,
too.

Thank you for reading this,
Tom

Feb 7 '06 #3

A moment of patience...

wait for Tim or Lyle (the big Goons). They know all about that.

-Pam

Feb 7 '06 #4
On 6 Feb 2006 16:45:19 -0800, "tlyczko" <tl*****@gmail.com> wrote:
Another table, 1:1 relationship, ProgramAudits, has AuditID as a
foreign key.


Open ProgramAudits in design view; select the AuditID field; and
change its Index from Allow Duplicates to No Duplicates.

John W. Vinson[MVP]
Feb 7 '06 #5
"tlyczko" <tl*****@gmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:
I am working on an audits database.
The main table, Audits, has an AuditID primary key.
Another table, 1:1 relationship, ProgramAudits, has AuditID as
a foreign key.

I have a master/main form for Audits data, and a child/sub
form for ProgramAudits data.

I put a subform for ProgramAudits onto the Audits table form,
and the following code in the OnCurrent event to grab the
AuditID field from the main form and put it into the AuditID
field of the ProgramAudits subform:

Private Sub Form_Current()
' automagically grab the AuditID and put it in the right field
Me.AuditID = Form.Parent.[AuditID]
Me.Refresh
End Sub

Me is the ProgramAudits subform.

I only want the ProgramAudits table to have one record per
Audits table record, is there a better event or means to
achieve what I want to do?? Or is there some uncomplicated way
to check if the ProgramAudits table already has a record
corresponding to an Audits table record??

Should I enforce my end users to always manually add the one
new ProgramAudits record per each Audits table record??

One reason I ask is that the above event causes a new record
to be added each time the Audits table main form is opened,
and I don't want to do that, setting AllowAdditions to false
for the subform doesn't work.

I've looked on the forum but so far have not found the right
way to search the groups to know if anyone has solved this
question.

Thank you for reading this,
Tom

You are making this much more difficult than it needs to be.
The subform has a pair of properties, linkChildFields and
LinkParentFields, which will handle the automatic entry of the
foreign key to the sub table.

As to adding the child record, if the user has no additional data
to add, there is no need for the child record. As the user adds the
other required data, the foreign key should automatically be added,
so there is no additional work for the user.

If you still wish to use your code above, you could embed it in an
if condition that tests the subform's .recordcount property and
only sets the Me.AuditID = Form.Parent.[AuditID] if the recordcount
=0
--
Bob Quintal

PA is y I've altered my email address.
Feb 8 '06 #6
Hello, I got this figured out.

I did several things:
Code in the SUBform's Current and AfterInsert:

Private Sub Form_AfterInsert()
Me.AllowAdditions = False
End Sub

Private Sub Form_Current()
'This is the only required field, all other fields are optional,
'so I fill this in programmatically.
If Me.AuditID = "" Or IsNull(Me.AuditID) = True Then
Me.AuditID = Me.Parent.AuditID
DoCmd.RunCommand acCmdSaveRecord
Me.Recalc 'To reset txtRecordNos on the form
End If
End Sub

I also set the Cycle Property of the SUBform to stay on the current
record.

In the MAIN form I did:
Private Sub Form_Current()
' (Parent Form)
'test the current record of the MAIN form
If Me.NewRecord = True Then
' Parent form is on a new record
Me.frmProgramAudits.Form.AllowAdditions = True
Else
Me.frmProgramAudits.Form.AllowAdditions = False
End If
End Sub

All but one of the SUBform's fields are locked, only one field is
actually editable, and this is optional.

If anyone has comments, suggestions for improvements, thank you!!

Thank you for reading this,
Tom

Feb 8 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.