Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying data from one record to next (AccessXP)

    I have a sub form, datasheet, that has five fields StartDate, EndDate, Activity, Content, Allowed. The Allowed field is not enabled. What I want to happen is the next record StartDate to be the same as the EndDate of the record being exited. I figure that needs to be in the On Exit event for the Content field. But I am unsure how to make that happen with VBA.

    Thank you.
    Fay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from one record to next (AccessXP)

    You don't want to do this in the OnExit event of a control, because you might exit to go to another control in the current record, to go to a previous record, or to close the form. In these cases, you don't want to set StartDate!

    On possibility is to set the DefaultValue for StartDate in the AfterUpdate event of the control bound to EndDate, and in the OnCurrent event of the form:

    Private Sub Form_Current()
    SetDefault
    End Sub

    Private Sub EndDate_AfterUpdate()
    SetDefault
    End Sub

    Private Sub SetDefault()
    If IsNull(Me.EndDate) Then
    Me.StartDate.DefaultValue = "Null"
    Else
    Me.StartDate.DefaultValue = "#" & Format(Me.EndDate, "mm/dd/yyyy") & "#"
    End If
    End Sub

    In the datasheet subform, you will see the default value of StartDate change as you navigate from record to record, and when you change EndDate.

    Note: the Format(Me.EndDate, "mm/dd/yyyy") is needed for international compatibility. If you have US settings, Me.EndDate would be enough.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying data from one record to next (AccessXP)

    Thanks Hans. It almost does the trick. The problem is that when I tab to the next row the StartTime, yes time I told you wrong the first time, that was the Endtime the row before disappears. Thinking this through I tried an AfterUpdate event on the Content to SetFocus to EndTime. But that just looped be back on the same record. I tried Me.NewRecord then the SetFocus statement and that didn't work. I then tried to set focus in the SetDefault() and that didn't work either.
    Private Sub SetDefault()
    If IsNull(Me.EndTime) Then
    Me.StartTime.DefaultValue = "Null"
    Else
    Me.StartTime.DefaultValue = "#" & Format(Me.EndTime) & "#"
    Me.EndTime.SetFocus
    End If
    End Sub

    Thanks
    Fay

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from one record to next (AccessXP)

    Sorry, my mistake. You must prevent SetDefault from being run in OnCurrent if the user is entering a new record, since EndTime has not been filled yet. Change the Form_Current procedure to

    Private Sub Form_Current()
    If Me.NewRecord = False Then SetDefault
    End If

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying data from one record to next (AccessXP)

    Okay I plugged in the Form_Current as listed above. That gave me an ambiguous error. I move the SetDefault statement above the new statement you just gave me. That gave me an Sub or Function not defined. If I remove it I get the same message. I checked the help and none of those seem to fit the situation.
    Fay

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from one record to next (AccessXP)

    It would be helpful if you mentioned what is highlighted when you get get these error messages - the Visual Basic Editor usually highlights the word or instruction that causes the problem (even though the real cause may not be what VB thinks it is).

    If SetDefault is unknown: in my reply, I assumed that you still had the SetDefault procedure in your form module:

    Private Sub SetDefault()
    If IsNull(Me.EndTime) Then
    Me.StartTime.DefaultValue = "Null"
    Else
    Me.StartTime.DefaultValue = "#" & Format(Me.EndTime) & "#"
    End If
    End Sub

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying data from one record to next (AccessXP)

    I am sorry. I should have included the highlighted word. That would obviously make long distance diagnosis easier. I liked your comment "even though the real cause may not be what VB thinks it is." It takes some of the pressure off the novice coder, knowing we may be lead down the wrong path. Also forewarns me to take it with a grain of salt.

    Okay back to the problem. Here is what I have
    Private Sub EndTime_AfterUpdate()
    SetDefault
    End Sub
    Private Sub Form_Current()
    SetDefault
    End Sub
    Private Sub SetDefault()
    If IsNull(Me.EndTime) Then
    Me.StartTime.DefaultValue = "Null"
    Else
    Me.StartTime.DefaultValue = "#" & Format(Me.EndTime) & "#"
    End If
    End Sub

    The problem is that the default StartTime in the new record disappears the moment I tab to the new record. It also disappears if I click in the EndTime, Activity, or Content column by passing the StartTime column.

    So I had been thinking that I needed to set focus on the EndTime. But that does appear like it would work in light of what I found above.

    Thanks for the help Hans.
    Fay

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from one record to next (AccessXP)

    So, what *was* the highlighted word? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    As I suggested earlier, try

    Private Sub EndTime_AfterUpdate()
    SetDefault
    End Sub

    Private Sub Form_Current()
    If Me.NewRecord = False Then SetDefault
    End Sub

    Private Sub SetDefault()
    If IsNull(Me.EndTime) Then
    Me.StartTime.DefaultValue = "Null"
    Else
    Me.StartTime.DefaultValue = "#" & Format(Me.EndTime) & "#"
    End If
    End Sub

    Does this cause an error message, and if so, where?

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying data from one record to next (AccessXP)

    I missed If Me.NewRecord = False Then SetDefault. It works as advertised. I figured it was me. I am sorry. Thanks!!!!

    Fay

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying data from one record to next (AccessXP)

    The above has worked as I want it to work, with the following exception. With a new class on the main form there is a time already in the StartTime field. I would think with the following lines of code that wouldn't happen.

    Private Sub Form_Current()
    If Me.NewRecord = False Then SetDefault
    End Sub

    Why is the default being set even when it is a new record?

    Fay

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from one record to next (AccessXP)

    The <font face="Georgia">If Me.NewRecord = False Then</font face=georgia> only prevents the default value form being changed when you are on a new record in the subform, it doesn't clear the default value. In fact, it is intended to make sure the default value is not cleared - that was what you were complaining about earlier on.

    The Me.NewRecord tells you whether you are on a new record in the subform (since that is where the code is running). It is no use trying to find out from the subform whether you are on a new record on the main form - you can't add a record to the subform if the record on the main form hasn't been saved yet.

    It would be possible to clear the default value in the BeforeInsert event of the main form, but it might have unexpected side effects. If you (or your users) can live with clearing or modifying the StartTime manually for a new class, I recommend leaving it like it is. If that is undesirable, post back for more advice.

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying data from one record to next (AccessXP)

    Thanks that makes it clearer. I agree with the last. I think the user can do a little work.

    I was think Me was referring to main form. I am currently getting a better understanding of all of this as I read Novalis's book "Access 2000 VBA Handbook."

    Thanks.

    Fay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •