Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Red face Unanswered: How to use ADO to insert/update/select data into a form with subform(Datasheet)

    Hi
    I have a question about ADO in the subform. I knew how to use ADO to insert/update/select data into the sigin form, but wehn I try to do it in the form with subform((Datasheet). I don't have any idea how to insert/update/select the recordset data into the subform(Datasheet), especially insert/update. Would someone can give me a idea or sample code to me? Appreciate your help.


    OS: windows XP +SP2+ Access 2003+SP2
    Database: MS SQL Server 2000+SP4

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    ADO doesn't interact with forms, it interacts with the recordsets of the forms. Can you restate the question in light of this? Specifically, do you need to determine the recordset of the subform? (Me.SubformName.Recordset normally does the trick...)

  3. #3
    Join Date
    Oct 2007
    Posts
    5
    Thanks Sco08y. My question is I can use the Single Form to display/update/insert/delete the data
    which using the ADO to get the data from the SQL Server, but when I craete a form which subform( datasheet), how can I display/update/insert/delete data in the subform. For example; I have a order which 5 items. I put those items into the subform(datasheet), When user edit/delete one of item. How can I to update this order to the SQl Server? Please advise. Thanks your help.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by cooperkuo
    how can I display/update/insert/delete data
    SELECT
    UPDATE
    INSERT
    DELETE
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2007
    Posts
    5
    Thanks georgev , I knew how to use select/update/insert/delete, my question is how to get the data from subform and update it to SQL Server. For example, I have a order with 3 items (Please see sample in below). And those items are in the subform(datasheet). If my user edit item2'Qty from 40 to 100, How can I get the item2 & QTY 100 to update my SQL Server (Use the ADO). Please advise. Thanks again.
    -----------------------------------------
    | ORDER: 1000 CUSTOMER: 20 |
    ------------------------------------------
    | | Item# QTY | |
    | | ITEM1 50 | |
    | | ITEM2 40 | |
    | | ITEM3 30 | |
    ------------------------------------------

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You would run an SQL command through the ADO connection
    Code:
    Dim sql As String
    
    sql = ""
    sql = sql & "UPDATE Items"
    sql = sql & "SET    Qty = 100"
    sql = sql & "WHERE  itemID = 2"
    
    conn.Execute sql
    Don't forget your error handling!
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2007
    Posts
    5
    Thanks georgev , your code is right, but in fact, user didn't tell me which field/record was edited. So after user click save, I should use ADO to delete all records on my SQL Server and insert those data from the client, right? If yes, how can I got those data in the subform to insert to SQl Server? Please advise. Thanks.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wouldn't suggest you delete then re-add... What happens if there is a failure on an insert? That's right - you've lost your previous data!

    How are you populating your subform?
    George
    Home | Blog

  9. #9
    Join Date
    Oct 2007
    Posts
    5
    Hi georgev, please forgot my question about ADO, would you tell me how to get the item2 value. For example, I can use "me.order" to get me order number and "me.customer" to get the customer number, but how to get item2 value in the subform? Please advise. Thanks.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why should I forget the ADO questions, hmm?

    As for you other question - is "item2" the selected record?
    If so you should be able to use the forms collection...
    Pseudo code
    Code:
    Forms!MySubformName.Value
    George
    Home | Blog

Posting Permissions

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