"injanib via AccessMonster.com" wrote:

> I have a form with a subform. Both forms have one field in common (txtInvoice)
> . I would like to add a code to the colse or unload event of my subform to
> delete any new records where the txtInvoice field is null because if the
> txtInvoice field is Null then the main form is blank.
>
> Is this the best way to go about it or is there a better way to handle this?
> Thanks in advance
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1
>
>

> Ok, here we go.
>
> On the main form, the txtInvoice is automatically generated with the after
> update event of another field called txtContract. I have locked all other
> fields if the txtContracts is Null. So employees have to enter a contract
> number to move on. My command buttons are set up to either dump or save
> new
> records according to the user's input. They can chose to save or not save
> a
> record as they navigate.
>
> My subform also has a field called txtInvoice which relates the records in
> the subform to the record in the parent form. The field is also set to
> grab
> the same value from txtInvoice in the main form. The txtInvoice in the
> subform is not visible. It is just there to serve as the related field
> between the two forms.
>
> My command buttons do not work on the subform. So, employees can manually
> set
> the focus in the subform and start entering record without anything being
> in
> the main form. and because access saves records when the focus moves to
> another form (The mainform in this case) the record source for my subform
> will be filled with garbage.
> If the main form is blank the txtInvoice is null and if the txtInvoice in
> the
> main form is null so is the txtInvoice in the subform. So I wanted to set
> it
> up so that even if employees enter records in the subform, because the
> txtInvoice field is null the records get deleted when the form is closed.
>
> If there is a better way than this please let me know.

On Fri, 12 Jun 2009 18:37:34 GMT, "injanib via AccessMonster.com" <u35551@uwe>
wrote:

>Ok, here we go.
>
>On the main form, the txtInvoice is automatically generated with the after
>update event of another field called txtContract. I have locked all other
>fields if the txtContracts is Null. So employees have to enter a contract
>number to move on. My command buttons are set up to either dump or save new
>records according to the user's input. They can chose to save or not save a
>record as they navigate.
>
>My subform also has a field called txtInvoice which relates the records in
>the subform to the record in the parent form. The field is also set to grab
>the same value from txtInvoice in the main form. The txtInvoice in the
>subform is not visible. It is just there to serve as the related field
>between the two forms.
>
>My command buttons do not work on the subform. So, employees can manually set
>the focus in the subform and start entering record without anything being in
>the main form. and because access saves records when the focus moves to
>another form (The mainform in this case) the record source for my subform
>will be filled with garbage.
>If the main form is blank the txtInvoice is null and if the txtInvoice in the
>main form is null so is the txtInvoice in the subform. So I wanted to set it
>up so that even if employees enter records in the subform, because the
>txtInvoice field is null the records get deleted when the form is closed.
>
>If there is a better way than this please let me know. Thank you

>>John,
>>How do you suggest I do this? my subform is not a pop-up.
>>the hidden field is a bound field. I just don't need it to show on the
>>subform, because it already shows in the main form. I have used it as the
>>child field for the main form because it is the number that is unique and is
>>the same on both forms.
>
>The "Child Link Field" property of a Subform needs to be a *field* - not a
>control! - in the subform's Recordsource table or query. It is not necessary
>to have a Control (visible or invisible!) bound to that field.
>
>It's quite unusual to have a subform and mainform linked by their primary
>keys. What are the Recordsources of the two forms?? Do you have in fact a one
>to one relationship between the tables?
>
>>Here is something I just noticed. My subform is a multiple item datasheet
>>that shows each record in a row.
>
>That's typical, and normally you would have a one to many relationship, not a
>one to one relationship. The linking field in the subform would NOT be the
>subform's table's primary key, but a different field - e.g. in the Northwind
>sample database Orders form, the mainform is based on Orders (primary key
>OrderID), and the Subform based on OrderDetails, which includes an OrderID
>field as a foriegn key (and the Child Link Field).
>
>>I could enter multiple records in the subform that are related to one record
>>in the main form. What happens is that if I enter 3 or how ever many items in
>>the subform, it will create that many records all together. 
>
>Exactly.
>
>>what I mean is that if I navigate between records on the mainform, it will
>>have three diferent records with exactly the same data on them.
>
>It certainly shouldn't, not if your form is designed correctly.

>>both forms are based on tables.
>>The main table is where I enter the coentract number, create an invoice number,
>>enter customer information.
>
>So the real-life Entity (person, thing or event) modeled by the main table is
>an Invoice? Is it a fair statement that you do not want or expect repeat
>business, so that once you issue an invoice to a customer you'll never see
>that customer again, or else reenter that customer's information redundantly
>if they DO come back?
>
>>The subform is where I enter all the items for which I bill the customer. It
>>could be one item or more.
>
>>Then I print out the invoice which lists all the items for that invoice
>>number and the total ammount billed.
>>
>>The record source of the subform has the following fields and the name of the
>>fields on the form are also the same respectively.
>>txtInvoice, txtIDexcription, txtQty, txtUnit, txtUnitPrice, txtTotal
>
>I would suggest that you NOT store the total: if txtTotal is the product of
>txtQty and txtUnitPrice then it should be dynamically calculated as needed and
>NOT stored in any table.
>
>Do you have a table of Items? or do you reenter the description anew for every
>invoice-detail item? Are the items one of a kind, or do you sell "the same"
>item to multiple customers?
>
>>the txtInvoice is the PK of the main form, however it is not a PK on the
>>subform. The subform does not have a PK.
>
>Forms do NOT HAVE PRIMARY KEYS.
>Forms DO NOT STORE DATA.
>
>Forms are *just windows*, tools to manage data in tables. I really suspect
>you're mixing levels here.
>
>How then do you uniquely identify an item on the subform's table? Nothing to
>prevent duplicate records?
>
>Do you have a Relationship defined between the two tables (on txtInvoice)?
>
>>Should I base my forms on a query based on the two tables?
>
>No. The mainform should be based on the parent table, the subform on the
>details table.
>
>>I can't figure out why the form shows different records with the same info
>>for the same invoice. The number of the records depend of the number of
>>records entered in the subform.
>
>Without knowing what data is in your tables, and what you're seeing on the
>form, I really don't know - other than to say that it's possible that your
>tables or relationships may not be set up correctly.
>