Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Delete record in VBA

Discussion in 'Information Technology' started by hursty, Jul 28, 2006.

Tags:
  1. hursty

    hursty
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "hursty" <hursty@discussions.microsoft.com> wrote in message
    news:19E992AD-3A11-4786-827E-411FC7E5C812@microsoft.com...
    >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
     
  4. hursty

    hursty
    Expand Collapse
    Guest

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

    "Allen Browne" wrote:

    > 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
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

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

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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "hursty" <hursty@discussions.microsoft.com> wrote in message
    news:885DFBA3-34F9-46E2-AA86-1FAF947755D0@microsoft.com...
    >
    > I want the person entering data to be able to put in records from other
    > dates, not just today's date.
    >
    > "Allen Browne" wrote:
    >
    >> 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
     
  6. hursty

    hursty
    Expand Collapse
    Guest

    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.
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "hursty" <hursty@discussions.microsoft.com> wrote in message
    news:088093B7-CA7E-438D-8F67-6E06B6681D24@microsoft.com...
    > 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.
    >
     
  8. hursty

    hursty
    Expand Collapse
    Guest

    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.

    "Allen Browne" wrote:

    > 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.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
     

Share This Page