Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copying text (Access 2000)

    Hi, I have a form listing events with a date field. I'd like to be able to re-use the data in this date field to do a couple of things. Firstly to be able to automatically populate a new record's date with the data from the previously edited record, and secondly to be able to populate 2 text boxes on a separate form with the data from the record just edited on the events form. I thought that some sort of code copying the data just prior to the date field losing focus would do the trick, then a paste when the new record or other form gets the focus. I'm not sure how to proceed or which is the best event to use for the copying. Can anyone point me in the right direction?

    Thank you very much. Roger

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

    Re: copying text (Access 2000)

    1. You can very simply copy the value of the current field (the field having the focus) from the previous record to the current record by pressing Ctrl+' (apostrophe).

    2. For an automated procedure, you can use the technique described in ACC2000: Fill Record with Data from Previous Record Automatically.

    3. Should the user be able to modify the values in the two text boxes on the separate form? If not, you could simply set their Control Source property to

    =[Forms]![NameOfFirstForm]![NameOfControlOnFirstForm]

    with the appropriate names for the form and control substituted. If yes, you would need VBA code.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying text (Access 2000)

    Thanks, Hans. Point 2 is just what I needed and works well.

    For point 3: yes, the user would need to be able to modify the values, I simply want to use the value as the defaults for a date range selection form prior to previewing a report on the data. How would I set these with code, taking the value from the events form's last entry?

    Cheers, Roger

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

    Re: copying text (Access 2000)

    Is the date range selection form always open when the user edits the events form?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying text (Access 2000)

    No, it's closed. I have a button on the events form that opens a report displaying events data which I will filter using (in 99% of cases) the date of the last record entered on the events form. So that button will open the form, in which I want the 2 text boxes to be populated with the date, which will then lead to the report opening. I can set up the form and report no problem, I just don't know how to get the date as the default value for the text boxes.

    Roger

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

    Re: copying text (Access 2000)

    Ok, thanks. Try code like this in the On Load event of the date range selection form, with the appropriate names for the form and controls substituted:

    Private Sub Form_Load()
    If CurrentProject.AllForms("frmEvents").IsLoaded Then
    Me!txtSomeDate.DefaultValue = Forms!frmEvents!txtTheDate
    Me!txtOtherDate.DefaultValue = Forms!frmEvents!txtTheDate
    End If
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying text (Access 2000)

    Hi Hans, I think I'm almost there. I can get my date selection form to load and everything works as wanted, but the default date is 31/12/1899 in both fields. (dd/mm/yyyy date format in this country). Any ideas? The events form name is EventsMain, the date field is txtDate, perhaps what is happening is that it is not looking for the date in the specific record on the EventsMain form? The code I have in the On Load event is:

    Private Sub Form_Load()
    If CurrentProject.AllForms("EventsMain").IsLoaded Then
    Me!txtStartDate.DefaultValue = Forms!EventsMain!txtDate
    Me!txtEndDate.DefaultValue = Forms!EventsMain!txtDate
    End If
    End Sub

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

    Re: copying text (Access 2000)

    Try this. The # characters tell Access that the value is a date:

    Private Sub Form_Load()
    If CurrentProject.AllForms("EventsMain").IsLoaded Then
    Me!txtStartDate.DefaultValue = "#" & Forms!EventsMain!txtDate & "#"
    Me!txtEndDate.DefaultValue = "#" & Forms!EventsMain!txtDate & "#"
    End If
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying text (Access 2000)

    Hans thank you very much - this works perfectly.

    Regards
    Roger

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying text (Access 2000)

    Hans - I'm back with a request for another refinement so solve a date format issue with this now. Our default date format is dd/mm/yyyy, and this format is specified on all date text boxes. The date format is taken correctly from the EventsMain form and used as the default date correctly provided that the day is 13 or greater. If the day is 12 or less on the EventsMain form, the date is converted to mm/dd/yyyy format. (I have confirmed this by using a dd mmmm yyyy format to specify the month as a word on the report that is subsequently opened). How do I force the default date to be in dd/mm/yyyy format? I have tried using "medium date" as below (or "dd/mm/yyyy"), but this doesn't work.

    Code for setting default date is currently:
    Private Sub Form_Load()

    On Error GoTo Err_Form_Load

    If CurrentProject.AllForms("EventsMain").IsLoaded Then
    Me!txtStartDate.DefaultValue = Format(("#" & Forms!EventsMain!txtDate & "#"), "medium date")
    Me!txtEndDate.DefaultValue = Format(("#" & Forms!EventsMain!txtDate & "#"), "medium date")
    End If


    Exit_Form_Load:
    Exit Sub

    Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load

    End Sub

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

    Re: copying text (Access 2000)

    Try this instead:

    Me!txtStartDate.DefaultValue = "#" & Format(Forms!EventsMain!txtDate, "mm/dd/yyyy") & "#"

  12. #12
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying text (Access 2000)

    That's weird !!! Yes, putting in the "mm/dd/yyyy" format gives me the desired "dd/mm/yyyy" format. I don't think I want to know why that works ;-)

    Anyway, thanks a lot, it's working as I want now.

    Regards, Roger

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

    Re: copying text (Access 2000)

    I can understand that you don't want to understand <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    But if you do: VBA is strictly US-centered. If your date is 2 June 2004, you type 02/06/2004 in dd/mm/yyyy format, but VBA interprets it as February 6, 2004, i.e. day and month have been switched. By formatting it as mm/dd/yyyy, you force VBA to switch day and month AGAIN, resulting in 02/06/2004. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

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