Delete record in VBA

H

hursty

I am making a defect tracking database and in one of the forms I want to keep
the date after they hit submit so that the person entering data doesn't have
to type in the date everytime. The text box is called EntryDate
So, to solve that problem I did one of these...

Private Sub Submit2_Click()
On Error GoTo Err_Submit2_Click
Dim savedate As Date
savedate = EntryDate
DoCmd.GoToRecord , , acNewRec
Me.EntryDate.SetFocus
Me.EntryDate = savedate
Exit_Submit2_Click:
Exit Sub

Err_Submit2_Click:
MsgBox Err.Description
Resume Exit_Submit2_Click
End Sub

I added the

Dim savedate As Date
savedate = EntryDate

Me.EntryDate = savedate

to the code so it would save the date.
This part of it works fine, however, now I have a new problem.
After submitting the last record, and hitting the 'X' to close, I get the
following error.
"The field 'Recrate Reason Table.Reason Code' cannot contain a Null Value
because the Required property for this field is set to True. Enter a value in
this field"
I still want Reason Code to be required for all the entries but I don't want
this message to pop up everytime.
So I tried to figure out some sort of delete record command on Form_close
but it was to no avail.
Please Help
 
A

Allen Browne

If you just want to store the date that the record was entered or last
modified, just use the BeforeUpdate event of the *form*:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.EntryDate = Date
End Sub

This event fires just before Access saves the record. You can then omit your
Submit2 button, or else just use it it save the record:
RunCommand acCmdSaveRecord
 
H

hursty

I want the person entering data to be able to put in records from other
dates, not just today's date.
 
A

Allen Browne

Perhaps move the code into the form's BeforeInsert event then?

Or just set the Default Value of that text box to:
=Date()
 
H

hursty

Setting the default value to date doesn't solve my problem. I already have
the previously entered date as the default. But, when you have just entered
to record, hit submit, the form clears and the date just entered will pop up
again. That is what I want to happen. But then when you try to close the
form with only the date box filled out it gives me that error about required
fields. I have the date doing what I want it to do unless there is a better
way to do it, but it is in closing the form with a partially filled out
record that is my problem.
 
A

Allen Browne

Did you try moving the code into Form_BeforeUpdate, so you are not dirtying
the form too early?

AFAICT, the problem seems to arise from the fact that you are assigning a
value to a bound control before it is warranted.
 
H

hursty

Yes, I did try that, but that was only if I wanted it to be today's date. I
don't want to always enter today's date. I think I'm am going to forget it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top