Controlling New Record on a Sub-Form

[RickRock]

I have an invoice form to capture invoice date, customer information and other information pertinent to the invoice. It has a sub form that captures the line items of the invoice. The table tblInvoice stores the invoice information and tblInvoiceDetail captures the line item. The sub-form is not visible until all the needed invoice fields are entered. When I make the sub-form visible, the fields for the first item are displayed. As soon as I tab out of the first field on the sub-form, a second record line, preceded by an asterisk, is displayed below the active detail record. How can I control this new record line from appearing until I want it to.

[NeoPa]

Forms have a Property called AllowAdditions. If this is set to No then that asterisk (*) line, which is the place-holder for entering new records, won't show. This can be set in Design View or by VBA code.

[RickRock]

How do I get another record for more line items? Should I put a button on the sub-form?

[NeoPa]

RickRock:
How do I get another record for more line items?

I have no idea what you're trying to ask for. Give me some context. Something that would make clear what you're saying.

[twinnyfo]

Rick,

It looks like you have your subform displaying as a datasheet--which is possible with a Subform, but not my recommended method. Create your subform so that you have controls for all your table fields and set its display property to continuous forms (try to line up all your controls horizontally). By itself, you should be able to view all line items for all invoices.

When on the main form, make sure you have the parent child relationship set on the subform to the invoice ID--or whatever primary key you are using for your invoices table.

Now, any time you add any line items, they will be associated with the Invoice ID of the main form.

Hope this hepps!

[RickRock]

Yes this did help. Thank you.

[cactusdata]

How do I get another record for more line items? Should I put a button on the sub-form?

Yes, or on the main form. Let the button click event call:

Me!NameOfInvoiceLinesSubformCONTROL.Form.AllowAdditions = True

In the AfterInsert event of the subform call:

Me.AllowAdditions = False

[twinnyfo]

All,

Would it not be more simple to have AllowAdditions set to true on the subform in the first place? After all, it is a form designed to "add records". I see no need to keep switching back and forth between true and false. You can also control what data is required (both at Table or Form level) so that you don't add incomplete records.

I'm open to learning about any advantages of turning this switch on and off.

[NeoPa]

TwinnyFo:
I'm open to learning about any advantages of turning this switch on and off.

The requirement is simply the request of the OP Twinny. If they ask for something, and I don't suspect it leads them in a bad direction, I'm generally happy simply to answer.

Where I see indication of the question arising from a poor understanding I may add some explanation as I feel it's our responsibility to help rather than simply answer questions, but where I have no idea why they may ask for something I generally just answer.

TwinnyFo:
After all, it is a form designed to "add records".

I'm not sure it is quite as simple as that. A Subform can be used to add records but it is always used to display them. Sometimes the ability or right to add records has to be triggered by some qualification or other. Sometimes in the data, sometimes by the user.

[twinnyfo]

Neo,

Your second paragraph makes much sense. If the Form were merely for display, we would not expect AllowAdditions to be true. The user entering data would need that functionality, and they could either initiate that function (as described above), or, if the DB was designed in such a way as to know "who" logged in, this feature could be set automagically.

Sometimes I forget to look at things from that "other perspective". As usual, I appreciate the reminder!

[NeoPa]

TwinnyFo:
Sometimes I forget to look at things from that "other perspective". As usual, I appreciate the reminder!

I find it so much harder nowadays because I'm actually using Access as a coded Front-End. When I first started here I was using it pretty much for data only and some menu forms. All the more complicated work to answer questions in here was done by simply looking things up in Help and running some dummy scenarios to find answers to questions. As I had no experience then there was nothing to tell me how things should work and so I had very few assumptions. Nowadays I'm far more inclined to assume it all works the way it does in my actual projects so I can certainly sympathise.

At the end of the day we live & learn. Hopefully I can remember back to when I knew how much I didn't know ;-)