Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2004
    Posts
    22

    Unanswered: an easy question on update

    I have an easy question for experts:

    I have two related tables tblFruits and tblProvideer, and I try to update both it does but doesn't make a new record, just overwrite an existing record.

    For example in fruits I have:

    Apple, Grapes, and WaterMelon, and I have three providers:

    Fruit1 Fruit2 and Fruit3, the provideers repeats frecuency and I have different values for each fruit,

    The provideers are on tblProvider:
    ID - Provideer
    1 - Fruit1
    2 - Fruit2
    3 - Fruit3

    When I updated the record with the code below occurs this
    ID - Provideer
    1 - Fruit3
    2 - Fruit2
    3 - Fruit3

    and al the records related to Fruit1 "pass" into records related to fruit3 :whoops: can any could help me?

    this is the very-easy code related to a buttom

    Code:
    Option Compare Database
    
    Private Sub Comand0_Click()
    Dim conDatabase As ADODB.Connection
    Dim rstCustomers As ADODB.Recordset
    Dim strSQL As String
    
    Set conDatabase = CurrentProject.Connection
    strSQL = "SELECT * FROM tblFruits, tblProvider"
    
    Set rstCustomers = New Recordset
    rstCustomers.Open strSQL, conDatabase, _
        adOpenDynamic, adLockOptimistic
    
    rstCustomers.MoveLast
    With rstCustomers
    !Apples = "5555"
    !Grapes = "5555"
    !WaterMelon = "5555"
    !Provideer = "Fruit3"
    End With
    
    
    rstCustomers.Update
    
    MsgBox "All fruits are updated"
    
    
    Set rstCustomers = Nothing
    Set conDatabase = Nothing
    End Sub

    any suggestion I'll appreciated

  2. #2
    Join Date
    Sep 2003
    Posts
    228
    what is the goal of on your on click event?

    Maybe you should put your SELECT statement into a query and see if the results are what you expect...

  3. #3
    Join Date
    Sep 2003
    Posts
    228
    what is the structure of the fruit table? What fields does it have?

  4. #4
    Join Date
    Sep 2003
    Posts
    228
    I am not having a problem with the code you provided. I may not have built my tables the sames as you so if you could provide more details on your table structure and what type of relationship did you establish between the fruitID and the providerID?

  5. #5
    Join Date
    Aug 2004
    Posts
    22

    Unhappy This is the structure of the tables

    ohhh ottomatic! if you don't have troubles with the code above could you plz send me ur code?, the purpose of the buttom is only update the records for the two tables related, the structure and records on the tables are this:

    for tblFruits:
    Code:
    ID                   Autonumber
    Apples              Number
    Grapes              Number
    WaterMelon          Number
    tblProvideerID       Number
    for tblProvideer:
    Code:
    ID                  Autonumber
    Provideer           Text
    this tables are related by:
    Code:
    ID (On tblProvideer) (1) ----> tblProvideerID_  (On tblFruits) (infinity)
    I have three Provideers fruit1, fruit2 and fruit3, and 8 records for fruits that have this provideers (a provideer can repeat i.e. 2 for fruit3, 4 for fruit1 etc).

    I try to change MoveLast for AddNew (in the code above) but when I do this something strange occurs, because Updates the records in tblFruits EXCEPT the Provideer (because is related to tblProvideers) but in tblProvideer generates a duplicate record (two Fruit3 provideer, that supose to be only 1 record Fruit3 and the others call from it).

    In resume generate the records for each table but the records doesn't update their relation.

    if in ur code doesn't occur that error, you found my three days question, I cant sleep without think about that!!!!

    plz help
    thnks for ur comments
    David G.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you are ADO and i'm DAO but it shouldn't make that much difference.

    rstCustomers.MoveLast
    With rstCustomers
    !Apples = "5555"
    !Grapes = "5555"


    ??? does this do anything at all without .Edit as in:
    With rstCustomers
    .Edit
    !Apples = "5555"
    !Grapes = "5555"


    anyhow - if it does anything it is doing exactly what you told it to do - go to the last record and play with the fields. the last record in a recordset is not a new record!

    how about:
    ' we dont need this any more rstCustomers.MoveLast
    With rstCustomers
    .addnew
    !Apples = "5555"
    !Grapes = "5555"
    !WaterMelon = "5555"
    !Provideer = "Fruit3"
    .Update 'this is also tidier
    End With


    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Aug 2004
    Posts
    22

    Unhappy Maybe ADO sucks...

    yes, maybe ADO sucks,

    whatever could you plz bring me ur db?, maybe ado doesn't sucks and Im bad in a definition or else.

    here's my db... its a very-short code.

    thnks
    David
    Attached Files Attached Files

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    do you want to add new records or update old ones? they are diferent process. and ADO does not suck.
    ghozy.

  9. #9
    Join Date
    Aug 2004
    Posts
    22

    I want to add new record

    jajajaj

    I want to add new records,

    Yes u are correct, ADO doesn't sucks, I suck (I sad because the code doesn't work!).

    thnks for post
    David

  10. #10
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Could you post the data as it currently exists and how you want it to look after you update it...

    I'm having trouble following what you want to accomplish.

    And before setting connections and recordsets to 'nothing', close them first.

    -Warren

  11. #11
    Join Date
    Aug 2004
    Posts
    22

    Red face Yes Warren :)

    Yes warren, my data is like this

    on tblProvideers I have three providers:
    Fruit1, fruit2, fruit3

    on tblFruits I have
    Code:
    Provideer   Apple   WaterMelon Grapes
    Fruit1       12          87              89
    Fruit2       12           98              97
    Fruit3        32          87             87
    Fruit1        14          67              98
    Fruit2       29           45             56
    Fruit2       89           90              78
    this two tables have an autonumber ID and also tblFruits have an tblProvideers_ID (number) that is the relation between the table and tblProvideers.

    that I want is for example I want to put another record on tblFruits (tblFruits call from tblProvideers the provideer). the table upstairs are before, and I want that after the tblFruits has an extra-record with (for example):

    Code:
    Fruit2    666               666          666
    and tblProvideers looks as before

    but what I have.. with my code is: on tblFruits

    Code:
    (EMPTY)   666       666             666
    and for tblProvideers I have
    Fruit1 Fruit2 Fruit3 AND Fruit2 (the record is repeated! )

    I hope this could help, also you can se my code that I post above.

    Thnks for post
    David

  12. #12
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    I still don't think it makes sense? I don't see the relational structure at all, you're going to encounter referential integrity problems.

    Regardless to accomplish what you want please re-read izy's post and follow his steps and also change your SQL statment to this:

    strSQL = "SELECT * FROM tblFruits"

    Hope this helps.

    -Warren

  13. #13
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I think you created the necessary relationship between tables but you don't use it all. Provideer field supposed to be number field (foreign key). you are either trying to insert string data into it or trying to insert two different tables at once. instead you should insert proper Provideer ID number into the fruits table.
    here is the proper code for inserting data to your fruits table.
    Code:
    Application.CurrentProject.Connection.Execute "INSERT INTO tblFruits (Apples, Grapes, Watermelon, [Buscar en tblProvideer]) VALUES ('1111','1111','1111','3');"
    as you can see we put number 3 into [Buscar en tblProvideer] field ( I took the name from your database). Because tables are related, you should put proper number into this field, instead of "frutiX".
    ghozy.

  14. #14
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just my pennies worth but shouldn’t the SQL be like this

    strSQL = "SELECT * FROM tblFruits, tblProvider WHERE tblFruits.tblProvideerID = tblProvider.ID"


    if you do not specify the relationship then you will get every combination possible from the two tables, not just the related ones!


    Check the number of records returned ?


    MTB

  15. #15
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    actually it should be a JOIN query to enforce releationships. but I thought lydmanen doesn't have too much knowledge about normalized databases, so I went with the quick fix.
    ghozy.

Posting Permissions

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