Unwanted blank records

[TerryEA]

Hi

What's the best approach to deal with this?

I have a orders form, with an order-items subform, in a one to many relationship. The user clicks a command button to add an new order, fills in some fields and then, may or may not add order items at that stage. The order is automatically saved when the form is closed but there may be a blank order-item record stored, even if the user has entered no items. How can I make sure that, if the order-item record has no data, the order-item record is not saved, even though the order header is?

Which event would be the most suitable to test for the absence of data in the child record.?

Help would be much appreciated.

Terry

 

[Remou]

Before Insert may suit.

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Me.SomeField) Then
    Me.Undo
    Cancel = True
End If
End Sub

 

[TerryEA]

Hi Remou

That's doesn't seem to be working as I want. It does stop a blank record from being saved but fields derived from a stock code combo box are now not working, so the record won't populate.

Any other ideas?

What's the reson for the "cancel = true" part?

Terry

 

[Remou]

Cancel=True cancels the insert. Are you filling fields in programmatically? It may be better to set the default value.

 

[TerryEA]

Remou

The fields are being populated by user input. You see, what happens is that the user wishes to set up an order record, typing in things like Order Date, Customer Code, (Order no is self-incrementing) and may wish to save the order, coming back to later to type in what could be several order items (which are linked to a stock table). But if the user saves the order a blank order item record is also saved.

Also, I think what is happening is that the user might be clicking the add button on the subform, then changing his/her mind and not cancelling. Thus a blank record is saved. I've got round it by making a delete query which deletes any blank records in the table and this is activated whenever the main form is closed. So I guess that's a solution, but I'm sure I could do this a better way.

Thanks for your help once again.

Terry

 

[TheAceMan1]

How are ya TerryEA . . .

[blue]The order is automatically saved when the form is closed [purple]but there may be a blank order-item record stored[/purple], even if the user has entered no items.[/blue]

In order for a subform record to be saved (on close) the subform record has to be in [blue]edit mode[/blue] (depicted by the [blue]pencil icon[/blue] of the record selector.)

Hunt for something in code writing to the subform when you edit the mainform to know the cause. I expect this will involve updating the link field of the subform!

BTW: is the subform linked to the mainform with [blue]Link Master/Child[/blue] properties?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[TerryEA]

Hi TheAceMan1

Yes, the subform is linked to the mainform with Link Master/Child properties.

I think I've worked it out now. The subform could create records just by using the record selector at the bottom, and going past the last record, so it was easy for the user to inadvertently create new blamk records. I've taken the record selectors off and used command buttons to control the adding of records and the browsing of existing records.

Thanks for your input

Terry