Don't save record if null

[Dave]

Hi again,

I would like to prevent a record from becoming added if a particular
field is left empty. Normally that would be easy through validation
rules or required field in the table. However, the particular field I'm
referring to is a hidden because it's just a foreign key to the primary
key of another table. This foreign key field is a child to a parent in
another subform. It's possible to select a blank record in the other
subform which then won't populate this particular field. Anyway, if I
just use validation rules, I end up in a loop with a message box. How
can I prevent this? Perhaps something in the Before Update event or a
message box with a record undo button..

PS. I'm not the greatest with code yet, but I'm working on it.

Thanks,

Dave B

 

[Arvin Meyer [MVP]]

Your problem is that you can't fill a hidden control except in code, so you
have 2 choices. Discard the record, or write code to ensure the control is
filled. The second choice is preferable, at least to me. Please post back
with subform control names, text box names, and descriptions and I'll write
the code for you.

 

[Dave]

Thanks Arvin,
The hidden control, if the user select the proper record before getting
to this control, should be filled automatically. So I need a way to
discard the record so the user can go back and choose the proper sample
date and try again. The hidden control is "SampleID" on subform
"tblCellLocation subform". This SampleID should be filled from
"SampleID" on "tblSampleData subform" . Does this make sense?

Thanks again,
Dave B

 

[Arvin Meyer [MVP]]

If you have properly linked the subform, the SampleID should fill
automatically, but it shouldn't create a record until the first character is
typed in the subform. If you don't want aq record created until the proper
data is filled in the main form, you can either disable, or hide the subform
control. Then add code to the correct main form control to enable or make
visible the subform.

The code would be something like:

Private Sub SampleDate_AfterUpdate
If Me.txtSampleDate > (Date - 10) Then
Me.SubformControlName.Enabled = True
Else
MsgBox "The sample date must be less than 10 days", vbOkOnly
End If
End Sub