Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    7

    Unanswered: populating datasheet

    Hi everyone,
    I am new to access. I am using Ms access 2007.
    I have a subform in datasheet view.
    What I try to do is to populate the datasheet using Sql.
    How can I do this?


    Thanks in advance

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Set the SQL statement into the Record Source property of the subform?

    Not 100% sure on what you actually want without some further details in your question.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2008
    Posts
    7
    Thanks StarTrekker,
    Actually I have a datasheet subform. I populated the datasheet using master link and child link field. Each time a form is loaded, the datasheet displays all the record I want using the id from the master form.
    But each time I change the field values on the datasheet form, the previous values are updated by the newer one what I don't want.
    Each time the form is loaded, I want to populate the datasheet but I want the changes to be appended in the table as a new records.
    what should I do?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't use a data sheet so Imnot certain...
    but my view would be to ADD new records not overwrite existing records
    what makes you think that by changing the values in a datasheet will add new records.

    id scroll down to the bottom of the data shet and see if there is an empty row, or Id try to add a row using a command button if there isn't an appropriate button then Id try to add a row using a macro called from my own command button docmd.addnew springs to mind.. but I may be wrong.

    to me the datasheet approach is fine if you are trying to emulate a spreadsheet, but thats not a design stylistic Im happy with
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have a look at Append queries. This is using SQL to populate records.

    Once you have run your append query to append the data to the appropriate table, you may need to ReQuery the subform to show the results.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I think healdem has put his finger on the problem!
    Quote Originally Posted by Mr.Maharjan
    each time I change the field values on the datasheet form, the previous values are updated by the newer one
    When you change a value in an existing record, you change the record! You can't change an existing record, then save the record, and expect it to be saved as a new record! You have to scroll to the bottom and enter a new record!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or use VBA to copy the record after the change and abandon changes to the record...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    May 2008
    Posts
    7
    Sorry for late reply.
    I tried but..........
    Let me explain you the problem I am facing.
    Actually, I have a datasheet subform in a main form. Let's suppose, the fields I have in the datasheet are activity and status. I have to report the status of each activity in certain period of time. Initially, I have to enter the activity and status in the first reporting period of one project. In the next reporting period of same project, I need the activity name to be displayed in the subform which I did using master field and child field. But,this made the form to display both activity and status and if I change the status of the activity, It replaced the previous status, that is what I am facing? I want to display the activity only and I have to store the records of both reporting periods?
    What should I do?
    thanks

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Guessing as I can't see the table structure:

    * Add the reporting period field to the datasheet.
    * Add an Activity table.
    * Relate your datasheet table to the Activity table.
    * Modify your datasheet subform to include a combo box which looks up the activities in the Activity table.
    * Do data entry as normal.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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