Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003

    Smile Unanswered: Copy records (or parts of records)

    I'm working in Access 2003.

    I have a form with 7 fields. Date (date field), cust1(text), cust2(text), cust3(text), cust4(text), time(date/time), and 18holes(Checkbox). The underlying table is prepopulated with a years worth of dates and times. So when I run the form, I see a years worth of records with each day having many times "available". The idea is to use this as a subform of a form linked to a dates table. So for each day, the subform will display many times and 4 customers for each. The customer fields are empty for now. What I want to do is this: When the customer names are filled in, and the 18 holes check box is checked, I want to copy those customer names to the next time available. To help clarify this - this is for tee time reservations. When a customer is going to play 18 holes of golf, his name should be copied into a record 2 hours later in the day. So the question is how do I copy parts of one record into another record that already exists.

    I would appreciate any help you can give me. I know just enough VBA to muddle through, so if this gets complex, I might need a little handholding

    Thank you

  2. #2
    Join Date
    Nov 2003
    You might try this (to copy data from the current record you are in, to another record). You'll have to set up the strCriteria the way you want and you will have to set up the condition that the current record you are on is ot touched. Keep in mind as well. This routine will pull out and work with the FIRST record it finds matching the criteria you have set out.

    You will need to set up yourself any conditions that need to be met before firing this routine as well as where you might want to place this routine.

       Dim dbs As Database, rst As Recordset
       Dim strCriteria As String
       Set dbs = CurrentDb
       'Set the Criteria to whatever you need to bring up the required record.
       strCriteria = "[CustomerName] = '" & Me.myCustomer & "' AND [PlayDate] = #1-1-95#"
       'Change "myTable"  (below) to the name of the table which holds your records.
       Set rst = dbs.OpenRecordset("myTable", dbOpenDynaset)
       ' Find first matching record.
       rst.FindFirst strCriteria
       ' Check if record is found.
       If rst.NoMatch Then
              MsgBox "No record found."
              'or just Exit Sub/Function
              'or whatever....
              rst!Cust1 = Me.Cust1
              rst!Cust2 = Me.Cust2
              'and so on......
              'and so on......
       End If
       Set dbs = Nothing

Posting Permissions

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