records in subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jenny - 22 Aug 2006 14:56 GMT

Hi there

I have a mainform which the user enters records into the tblevents and a
subform which enters records into tblinvoice. Although the relationship
between the tables is one to one, multiple rows appear in the tabular subform
for a user to enter more than one record. Then of course should they attempt
to an error message displays saying the changes will not be succesful as it
weould create duplicate values in the pkey or relationship.

Have I made a glaring error? Or is there an easy way to restrict entries to
one record only on the subform that I have missed?

Many thanks for your help

Reply to this Message

John Vinson - 22 Aug 2006 17:46 GMT

>Hi there
>
[quoted text clipped - 9 lines]
>
>Many thanks for your help

One to one relationships are actually QUITE uncommon. Is it in fact
the case that each Event will have either zero or one invoices, and
each invoice must have one and only one event? If so, why not simply
put the invoice fields into the Events table?

What is the Recordsource of the main form? the subform? What are the
subform's Master/Child Link Fields? It sounds like they may be
missing, and that the subform is showing all records instead of the
one related record.

John W. Vinson[MVP]

Reply to this Message

Jenny - 24 Aug 2006 15:32 GMT

Thanks John

I originally deisned the database thinking we would have many invoices to
one event but for now we will just be having one to one as I cannot get the
time to design the necessary to get many invoices to one event.. Hence I've
changed the relationship to 1:1 for now but too many queries & depend on the
structure to add the invoice table fields to the event table. Plus I think
the no of fields per table might exceed the maximum if I do that.

The master/child fields are established [proposalnumber];[eventnumber]
(these two fields make the prinary key) and it doesn't show all records, just
the current record and a blank line as if waiting for data entry but then of
course data entry is not permitted as it would create duplicate records in
the primary key!

Thanks for your help

> >Hi there
> >
[quoted text clipped - 21 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 24 Aug 2006 19:57 GMT

>The master/child fields are established [proposalnumber];[eventnumber]
>(these two fields make the prinary key) and it doesn't show all records, just
>the current record and a blank line as if waiting for data entry but then of
>course data entry is not permitted as it would create duplicate records in
>the primary key!

If the main form contains values for both fields, and the master/child
links are as you say, then there is something ELSE wrong. What are the
following properties of the main and subforms:

Recordsource (post the SQL if it's a query)
Data Entry
Allow Additions
Allow Updates

John W. Vinson[MVP]

Reply to this Message

Jenny - 25 Aug 2006 09:47 GMT

Hi John

Mainform

> Recordsource: Event Table
> Data Entry: No
> Allow Additions: Yes
> Allow Updates: ? Can't find this before and after update are empty

Subform :

> Recordsource: Invoice Table
> Data Entry: No
> Allow Additions: Yes
> Allow Updates: ? Can't find this before and after update are empty

Thanks John - hopefully this sheds some light on where Im going wrong?

> >The master/child fields are established [proposalnumber];[eventnumber]
> >(these two fields make the prinary key) and it doesn't show all records, just
[quoted text clipped - 12 lines]
>
> John W. Vinson[MVP]

Reply to this Message

Ken Sheridan - 22 Aug 2006 17:50 GMT

In the main parent form's Current event procedure put the following:

Dim frm As Form

Set frm = Me.YourSubformControl.Form

frm.AllowAdditions = (frm.RecordsetClone.RecordCount = 0)

where YourSubformControl is the name of the control on the parent form which
houses the subform, not the name of the underlying form object; unless both
have the same name of course. Also put the following code in the AfterInsert
event procedure of the subform:

Me.AllowAdditions = False

And put the following in the AfterDelConfirm event procedure of the subform
so that if its record is deleted a new record can be entered:

Me.AllowAdditions = True

Ken Sheridan
Stafford, England

> Hi there
>
[quoted text clipped - 9 lines]
>
> Many thanks for your help

Reply to this Message

Jenny - 24 Aug 2006 15:23 GMT

> In the main parent form's Current event procedure put the following:
>
[quoted text clipped - 32 lines]
> >
> > Many thanks for your help

Thanks a lot Ken

That all seems to work fine apart from

Dim frm As Form

>
> Set frm = Me.YourSubformControl.Form
>
> frm.AllowAdditions = (frm.RecordsetClone.RecordCount = 0)

the text I insert for MYSUBFORMCONTROL is always rejected by the debugger

I have just realised the possible reason, sorry I forgot I have a subform
within a subform so it goes like this

Mainform: PNo mainform
Subform 1: Event New Form
The tabbed page on subform 1 is on: Finance
Subform 2 on Finance page: Invoice subform

I have entered each one of these for MYSUBFORMCONTROL but none of them are
accepted. Is it because they have spaces in? Or is it because the code you
gave me will not work with a subform within a subform?

If I was starting again I would design it differently but at the moment Im
on a deadline and Ive no time to change the whole structure!

Sorry to be a novice! Can you help ?!!

Thank you so much

Reply to this Message