473,721 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2827
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 programmaticall y 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.goo glegroups.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
LinkParentField s, 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_AfterInser t()
Me.AllowAdditio ns = False
End Sub

Private Sub Form_Current()
'This is the only required field, all other fields are optional,
'so I fill this in programmaticall y.
If Me.AuditID = "" Or IsNull(Me.Audit ID) = True Then
Me.AuditID = Me.Parent.Audit ID
DoCmd.RunComman d 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.frmProgramAu dits.Form.Allow Additions = True
Else
Me.frmProgramAu dits.Form.Allow Additions = 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
8405
by: Carl | last post by:
I have a main form with navigation buttons on it and a label showing for example Record 1 of 15 using recordsetclone on it and eveything works fine. When I move through the records the record number changes fine. Within this main form I have a subform detailing distribution records for the contact (main form is based on this) that also has navigation buttons and a label showing Distribution 1 of ##. This is where the problem lies. The...
1
371
by: April | last post by:
I am working on a database to be used for auditing medical groups. TABLE:DETAILS contains these fields: (autonumber) 50 group fields through each a combo box that pulls from the same list of 100+ groups Each group is audited by reviewing 10-20 patient records. This
13
6530
by: Ron | last post by:
Hi all I'm deciding whether to use the PK also as an account number, invoice number, transaction number, etc that the user will see for the respective files. I understand that sometimes a number will be missing, which is not a problem for my purposes I don't think, but how would it be negative? Can randomly created PK autonumber fields be negative? Any way for a PK, autonumbered, *incremented* to be negative? TIA
1
4442
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have created a MainTable (and related form), which has an associated SubForm (popup) along with its underlying, separate Table. The tables' relationship is one to many respectively. The primary key
2
2912
by: OM | last post by:
I have two tables, one with categories in it, and only 3 records - Wages, Salary, Contract. I have a second table that records hours and numbers of employees in each category, per month. How do I limit the number of entries for each month to the number of categories ? I only want 1 x Hrs and 1 x Numbers for each category, for each month (the figures are entered each month). I have a form (which has Month as the only field), and a...
3
8074
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we incur numerous service problems “Events”. Each morning we have a global conference call where events which occurred within the previous 24 hours are discussed. Prior to the call, an analyst has to review these events and provide a report, ‘The Morning...
5
8230
by: Dakrat | last post by:
Allow me to preface this post by saying that this is my first database project, and while I have learned a lot, any concepts I have learned are hit and miss as I have found new requirements and researched solutions. That said, I have a "training" database with PowerPoint briefings which I have users access and complete training. The form then records the date and time they completed training in a relevant field. I have a separate "master"...
2
2509
by: altesse33 | last post by:
I have a command button on the main form of my database that allows users to add new records. But, even though new records get added to the sub table when I click on the button, those new records aren’t immediately reflected in the combo box that is on the subform (whose record source is a junction table). Do you know of any methods I can add in an event procedure to update the subform? I added the following code based on a suggestion I...
3
1606
by: ramprat | last post by:
Hi All, I am using Access 2003 and have a continuous form based on a table (table1) as a subform on another form. I simply want to be able to take any records that get added to table1 through the subform and populate a 2nd table (table2) with these added records from the subform the instant they are added. The two tables both contain a common ID field. Also if any values in existing records on the subform change I would like to update a...
0
9373
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9227
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9145
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8020
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6676
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5992
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4761
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2590
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2143
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.