Delete records in subform if one field is Null

[injanib via AccessMonster.com]

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

 

[NG]

Hi,

I think it's better to avoid having a new record with a null txtInvoice.
Look at your relations and, if possible, use referential integrity.

 

[John W. Vinson]

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

If the Invoice field in the table is required... make it required. Don't make
the user enter a bunch of records and then delete them afterward, just don't
allow the entry at all.

If I'm misunderstanding please post some more detail.

 

[injanib via AccessMonster.com]

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

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

[quoted text clipped - 3 lines]

Is this the best way to go about it or is there a better way to handle this?
Thanks in advance

If the Invoice field in the table is required... make it required. Don't make
the user enter a bunch of records and then delete them afterward, just don't
allow the entry at all.

If I'm misunderstanding please post some more detail.

 

[Dirk Goldgar]

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.

Don't enable the subform unless the txtInvoice field is not Null.

 

[John W. Vinson]

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

It really sounds as if you're doing this the hard way! You need NO CODE AT ALL
if you use a Form with a Subform; you don't need a hidden textbox, you don't
need to look up the InvoiceNo...

That said, why not just set the Enabled property of the subform to No, and set
it to Yes once an InvoiceNo is defined? or if it's a popup form that you're
calling a subform, don't pop it up until it's legit?

 

[injanib via AccessMonster.com]

Thank you both for your response. I disabled the subform when the txtInvoice
is null.

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.

Here is something I just noticed. My subform is a multiple item datasheet
that shows each record in a row.
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.
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.

Ok, here we go.

[quoted text clipped - 22 lines]

If there is a better way than this please let me know. Thank you

It really sounds as if you're doing this the hard way! You need NO CODE AT ALL
if you use a Form with a Subform; you don't need a hidden textbox, you don't
need to look up the InvoiceNo...

That said, why not just set the Enabled property of the subform to No, and set
it to Yes once an InvoiceNo is defined? or if it's a popup form that you're
calling a subform, don't pop it up until it's legit?

 

[John W. Vinson]

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.

 

[injanib via AccessMonster.com]

both forms are based on tables.
The main table is where I enter the contract number, create an invoice number,
enter customer information.

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

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.

Should I base my forms on a query based on the two tables?
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.

 

[John W. Vinson]

both forms are based on tables.
The main table is where I enter the contract 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.

 

[injanib via AccessMonster.com]

My customers are actually saved in a separate table called tblCustomer. They
are regular customers. I will add new ones as I get them. My form pulls their
name and info from that table. I do need to save the customer information in
the recordsouce of my main form because of the following. I know that I could
link my records to the primary key of the tblCustomer and pull the
information from there so that I don't have to fill my database with
redundant info but:
It is expected that the contact name for one customer change and I would have
to update my tblCustomer with the new info. I do not want to change the
contact name on my old records. I still want them to show the old contact
name. Hence I have to save the info on the main table. Unless there is a
better way to do this.

As for the PK for the main and subform, I did mean for their record source. I
just typed it technically incorrect.
The items on my subform could be any thing. It could be product or service. I
may bill the customer for a service I perform for it. So it is really nothing
pre-set and I don't have a table of items. I enter them a new for each
invoice.
Also, the subform may have the exact same data in different records with the
exception of the invoice number. for example I may bill two different
customers for supplying 10 computer workstations. the only thing different
between these records would be the invoice number. Each record will have the
associated invoice number.

Yes I do have relationsships defined between the two tables on txtInvoice.
txtInvoice is what differenciates between similar items in the table of the
subform and relates them to the invoice number in the main form.

As far as the total is concerned, I do understand what you mean and I should
make the appropriate changes.

Thanks again for your feedback.