Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Duplicate values of previous record on subform controls

    I have a mainform with subform opened so that I can add a new record to the subform.
    New records are made to tracks changes to the mainform; ie Mainform record is Suzie Smith
    subform records are distinct to Suzie:
    ID Day Shirt Pants Shoes
    1 Mon Red Sweater Blue Pants Black Shoes
    4 Tues Blue Blouse Blue Pants Black Shoes
    8 Wed Sweatshirt Blue Pants Black Shoes

    The mainform is locked and the focus is on the new record in the subform. The subform is never blank and none of the existing records are allowed to be changed or deleted.

    What I need to do is get this subform opened on a new record that has copied the pants value and the shoes value for Suzies last record (ID 8).
    The only data that user will need to enter is the day and the shirt, because Suzie changed her shirt.

    I've exhausted all my google searches and tried numerous things. Can anyone offer a suggestion how this can be done?

    Thank you

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use (adapt the names according to those in your database):
    Code:
    Private Sub Form_Current()
    
        Dim lngID As Long
        
        lngID = DMax("ID", "Tbl_Suzie")
        Me.Pants.Value = DLookup("Pants", "Tbl_Suzie", "ID=" & lngID)
        Me.Shoes.Value = DLookup("Shoues", "Tbl_Suzie", "ID=" & lngID)
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    The mainform and subform are 2 different tables, the forms are linked on Name (Access2007) -
    table records are:
    MAINFORM tblGirls =
    GID Name
    1 Suzie
    2 Jane
    3 Gwen
    4 Heidi
    5 Jodi
    6 Dottie
    SUBFORM tblClothes =
    ID Name Day Shirt Pants Shoes
    1 Suzie Mon Red Sweater Blue Pants Black Shoes
    2 Jane Mon Red Sweater Black Pants Black Shoes
    3 Gwen Mon Yellow Sweater Black Pants Black Shoes
    4 Suzie Tues Blue Blouse Blue Pants Black Shoes
    5 Heidi Mon White Blouse Blue Pants Black Shoes
    6 Jodi Mon Red Sweater Blue Pants Black Shoes
    7 Dottie Mon Red Sweater Blue Pants Black Shoes
    8 Suzie Wed Sweatshirt Blue Pants Black Shoes
    9 Jane Tues Sweatshirt Black Pants Black Shoes
    10 Gwen Tues Sweatshirt Black Pants Black Shoes

    When I open the MAINFORM for Suzie - I need subform tblclothes to open on record
    11 Suzie *day* *Shirt* Blue Pants Black Shoes
    - user will enter day and shirt for this record

    The values copied for tblClothes Criteria needs to be the Max ID for tblGirls record 1 Suzie - which is now 8
    So I am taking the Pants and Shoes for Suzie, not necessarily the last Pants and shoes

    I know there's a way but can't grasp it exactly
    Last edited by Foskbou; 11-12-11 at 09:27. Reason: I posted too quick :-)

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then use a criteria in the DMax() function:
    Code:
    lngID = DMax("ID", "tblClothes", "Name = 'Suzie'")
    Have a nice day!

  5. #5
    Join Date
    Jun 2010
    Posts
    186
    in my form current criteria - I need name to be variable based on the mainform/name I am in

    how do I type "Name = 'variable'"

    (I can also filter on Name in form properties)

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to use a variable, it can be:
    Code:
    lngID = DMax("ID", "tblClothes", "Name = '" & Variable & "'")
    If you want to be the value of a control (TextBox, ListBox, ComboBox)in a form, use:
    Code:
    lngID = DMax("ID", "tblClothes", "Name = '" & Me.ControlName.Value & "'")
    Have a nice day!

  7. #7
    Join Date
    Jun 2010
    Posts
    186
    Thank you Sinndho!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Jun 2010
    Posts
    186

    Duplicate values of previous record on subform and duplicate+1

    I am trying to adapt some VBA I use from an old post in a new project.
    This will go in a continuous subform that holds between 40 - 50 records per main form.
    The entering is shipment details - so it is consecutive number ship tickets(being entered) TktNo, and duplicate dates TktDate. Each mainform is a week of shipments, so every 10-13 records the TktNo sequence changes and the date changes.

    TktNo = Number
    TktDate = Date/Time

    The following code enters the same data as the previous record:
    Private Sub Form_Current()

    Dim lngID As Long

    lngID = DMax("ID", "CybexTkts")
    Me.TktNo.Value = DLookup("TktNo", "CybexTkts", "ID=" & lngID)
    Me.TktDate.Value = DLookup("TktDate", "CybexTkts", "ID=" & lngID)


    End Sub


    For TktNo, how do I change the DLookup to give me last TktNo + 1?

    Since this code is to speed up the data entry and for ease of following along with this consecutive data, I need to be able to overwrite the ticket number and date where needed.
    Is this the most efficient way to do that and keep integrity in the saved records?

    Thanks much

Posting Permissions

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