Results 1 to 35 of 35

Thread: rs.Edit (help)

  1. #1

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Question rs.Edit (help)

    ok, my DB has a field named "Default".. and only 1 entry can have the value of "Yes".. so, when i put a check in the checkbox - i want it to make that entry the DEFAULT and make all the other entries to "No". there could be 2 entries or 500 entries for that matter. but again, only 1 can have the value of "Yes".

    so i assume there is some kind of loop or something but i don't know how to do it..




    Code:
    rs.Edit
    rs("Game Name") = Text1
    rs("Company") = Text2
    rs("Year") = Text3
    rs("Cartridge ID") = Text4
    rs("Rarity") = Combo1.Text
    rs("Own") = Text5
    rs("Qty") = Text6
    rs("Notes") = Text7
    rs("Value") = Text8
    rs("Label") = Text9
    
    
    'What i need is to go here i assume......
    'rs("Default") = checkbox1.caption
    
    rs.Update
    PS: there is also going to be another question later after this.. and that is - no duplicate names allowed..



    my module
    Code:
    Public db As Database
    Public rs As Recordset
    Public ws As Workspace
    
    Public Function InitDB(TableName As String, DataFile As String, DataIndex As String)
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(App.Path & "\" & DataFile)
    Set rs = db.OpenRecordset(TableName, dbOpenTable)
    rs.Index = DataIndex
    End Function
    
    Public Function SelectData(TableName As String, TableCol As String, TheData As String)
    Set rs = db.OpenRecordset("SELECT * FROM " & TableName & " WHERE " & TableCol & "='" & Replace$(TheData, "'", "''") & "'")
    End Function

    the app works flawlessly OTHER THAN the "Default" and no duplicate names..
    Last edited by elRuffsta; Feb 28th, 2024 at 09:37 PM.

  2. #2

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    once i complete these 2 things the app will be finished..

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,444

    Re: rs.Edit (help)

    Since this is DAO, i'm guessing it's an Access-Database, so Trigger would be out

    1) Execute your Insert of the new Record. Important: You need the "new" ID (Primary Key) of that Record back from the INSERT.
    During that INSERT set the value for your field to "Yes"
    Aircode
    Code:
    rs.AddNew
    'assign Values to Fields
    rs("Default") = "Yes"
    rs.Update
    rs.BookMark = rs.LastModified
    TheNewID = rs("ID")
    2) Immediatly after that execute following SQL
    Code:
    UPDATE tbl_test
    SET Default="No"
    WHERE ID<>" & TheNewID
    3)
    PS: there is also going to be another question later after this.. and that is - no duplicate names allowed..
    Easy: Put a UNIQUE-Constraint on that Field
    Last edited by Zvoni; Feb 29th, 2024 at 02:21 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,350

    Re: rs.Edit (help)

    Please do not post the same question multiple times. Post only in the most appropriate forum. If you think you posted in the wrong forum, use the Report Post tool to ask the mods to move your thread. The duplicate in the Database Development forum has been deleted.

  5. #5

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    Code:
    rs.AddNew
    'assign Values to Fields
    rs("Default") = "Yes"
    rs.Update
    rs.BookMark = rs.LastModified
    TheNewID = rs("ID")
    UPDATE tbl_test
    SET Default="No"
    WHERE ID<>" & TheNewID

    where would i put that exactly? where i said where i assumed it would go? above it? somewhere else?

  6. #6
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,940

    Re: rs.Edit (help)

    I'm assuming that the record that's the default might change at times.

    If it were me, I'd just spin through all the records and set the "Default" field to false before I change my one record to have "Default" = True.

    But, things are a bit confused because you're using a checkbox1.caption to set your default. Do you mean checkbox1.value? But even then, things are confused because checkbox1.value can equal 0, 1, or 2. None being True (exactly).

    So, just assuming we're using checkbox1.value = 1 as our Boolean test, we still have the problem of checkbox1.value = 0. In that case, we may be turning all our "Default" fields off, with no record remaining as the default.

    These issues need to be cleared up before I could proceed with any further advice, although I do have decades of experience with the DAO.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  7. #7

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    when i select the checkbox, i have it's caption set to "Yes", otherwise it's set to "No"... that's why i am using the caption VS the value. nothing more, nothing less.. it's just that simple.. and the only way to change the default is when editing or adding an entry by checing off the checkbox.

    Name:  default.JPG
Views: 132
Size:  2.5 KB
    Last edited by elRuffsta; Feb 29th, 2024 at 02:40 PM.

  8. #8

  9. #9

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    revamped app.. now i have a module that deals with it all.. basically now everything is RS based.. rsEdit, rsAdd, rsDestroy.. and so on. different than what i was doing.. and it's flawless as i mentioned in the OP. i am transforming an old app i made back in 2005 which allowed duplicate entries - (names) and there was no default entry.. and this is where the issue lays.. these 2 things have to be done to complete the project..
    Last edited by elRuffsta; Feb 29th, 2024 at 02:54 PM.

  10. #10
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,140

    Re: rs.Edit (help)

    Quote Originally Posted by elRuffsta View Post
    revamped app.. now i have a module that deals with it all.. basically now everything is RS based.. rsEdit, rsAdd, rsDestroy.. and so on. different than what i was doing.. and it's flawless as i mentioned in the OP
    Understood.

  11. #11
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,940

    Re: rs.Edit (help)

    Yeah, ok, if the caption is "No" for the record you're updating, proceed as normal, with the understanding you may have no "default" at all when you're done.

    If the caption is "Yes", spin through the recordset, setting all the "default" fields to "No", and then proceed with what you did.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,444

    Re: rs.Edit (help)

    No need for looping...... though i do like rollercoasters...

    Untested
    Code:
    rs.Edit
    rs("Game Name") = Text1
    rs("Company") = Text2
    rs("Year") = Text3
    rs("Cartridge ID") = Text4
    rs("Rarity") = Combo1.Text
    rs("Own") = Text5
    rs("Qty") = Text6
    rs("Notes") = Text7
    rs("Value") = Text8
    rs("Label") = Text9
    
    
    'What i need is to go here i assume......
    'CHANGED to VALUE. See Elroy's answer
    Dim s As String
    If checkbox1.Value Then s="Yes" Else s="No"
    rs("Default") = s  
    
    rs.Update
    
    rs.BookMark = rs.LastModified
    TheID = rs("ID")
    
    If rs("Default")="Yes" Then
       MyDaoDatabase.Execute("UPDATE MyTable SET Default="No" WHERE ID<>" & TheID, dbFailOnError)
    End If
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,940

    Re: rs.Edit (help)

    Quote Originally Posted by Zvoni View Post
    No need for looping...... though i do like rollercoasters...
    Well, that SQL statement is certainly doing a loop under the hood. I'm not up for testing, but I'm guessing a direct DAO loop is going to be faster than executing a SQL statement (just because of all the inefficiencies in firing up the SQL processor).

    But, in many ways, it just comes down to whether you like directly manipulating DAO recordsets, versus building SQL statements.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  14. #14

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    syntax error

    Code:
    'What i need is to go here i assume......
    'CHANGED to VALUE. See Elroy's answer
    Dim s As String
    If checkbox1.Value Then s = "Yes" Else s = "No"
    rs("Default") = s
    
    rs.Update
    
    rs.Bookmark = rs.LastModified
    TheID = rs("ID")
    
    If rs("Default") = "Yes" Then
       MyDaoDatabase.Execute("UPDATE MyTable SET Default="No" WHERE ID<>" & TheID, dbFailOnError)
    End If
    Last edited by elRuffsta; Mar 1st, 2024 at 10:49 AM.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,444

    Re: rs.Edit (help)

    Quote Originally Posted by Elroy View Post
    Well, that SQL statement is certainly doing a loop under the hood. I'm not up for testing, but I'm guessing a direct DAO loop is going to be faster than executing a SQL statement (just because of all the inefficiencies in firing up the SQL processor).

    But, in many ways, it just comes down to whether you like directly manipulating DAO recordsets, versus building SQL statements.
    How do you figure that? I‘m throwing a non-result SQL via the connection object at the db-engine.
    fire and forget!
    why would you expect your main thread to wait for execution finish from a separate thread?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  16. #16
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,176

    Re: rs.Edit (help)

    Quote Originally Posted by elRuffsta View Post
    syntax error

    Code:
    'What i need is to go here i assume......
    'CHANGED to VALUE. See Elroy's answer
    Dim s As String
    If checkbox1.Value Then s = "Yes" Else s = "No"
    rs("Default") = s
    
    rs.Update
    
    rs.Bookmark = rs.LastModified
    TheID = rs("ID")
    
    If rs("Default") = "Yes" Then
       MyDaoDatabase.Execute("UPDATE MyTable SET Default="No" WHERE ID<>" & TheID, dbFailOnError)
    End If
    Change "No" to 'No'. Does that fix it?
    Sam I am (as well as Confused at times).

  17. #17

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    compile error.. same line

    expected =

    if i try to run it again.. i just get syntax error

    also i changed "mytable" to "Details" since that is the name of my table... still same errors


    database name: DATA
    table name: Details
    Last edited by elRuffsta; Mar 1st, 2024 at 01:04 PM.

  18. #18
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,444

    Re: rs.Edit (help)

    *sigh*
    what‘s one of the differences between calling a procedure and a function?

    the use of parentheses…..

    though Sam‘s note about single quotes is correct

    but i did write untested, and my use of parentheses in procedure calls comes from using Pascal as my main language in the last 8 years
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  19. #19

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    it's whatever.. i spent way way too much time and effort on this app.. redesigned it 9x, used modules and all.. at the point i'm done with it.. i'll just use what i have as it is if i can't get it to have no duplicate entries and set a default entry.. then i'm over it and done. screw it. what should have been completed in 1 or 2 days at most has been way over a month now and 2 little things stopping it from being done.. thanks anyways everyone..
    Last edited by elRuffsta; Mar 1st, 2024 at 03:51 PM.

  20. #20

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    taking a day or 2 away from this..

  21. #21
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,176

    Re: rs.Edit (help)

    Yeah, I didn't catch the parentheses thing...should have.
    Sam I am (as well as Confused at times).

  22. #22
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,176

    Re: rs.Edit (help)

    I know all you experts prefer the method to which el switched, but I still like (and use) the 'old' way of doing this WITHOUT parameters. It is easy FOR ME to check my code by debugging my cmd.commandtext call....instantly advises me to my error(s).
    Sam I am (as well as Confused at times).

  23. #23
    Addicted Member ISAWHIM's Avatar
    Join Date
    Jan 2023
    Posts
    181

    Re: rs.Edit (help)

    Why not just indicate the "default" as a unique entry that just holds a record number. Any record that is NOT that number is NO, while the record number that IS that number, is YES.

    Why append useless, unique data that only has one value to every single record?

    As for no duplicates...

    Check if a record exists, before adding it or altering it. If it exists, then don't add it or delete the current record. Then there will not be any duplicates. (You delete the current record, if it is altered to matching another "existing record", because it already exists.)

    If you are importing data and don't want duplicates, then you have to search before adding each new record, if one already exists. (I think there is a test for "unique", which does that. However I don't know if VB does that with any of the database tools it has available.)
    Last edited by ISAWHIM; Mar 2nd, 2024 at 09:27 AM.
    Please, chime-in on my latest WIP.
    I can use all the help I can get.
    [VB6 Game], (In Development), "Galactic-Bondsman"

  24. #24
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,176

    Re: rs.Edit (help)

    at ISA....yes, Unique is available in VB6 (use it a lot). Op is using MS Access
    Sam I am (as well as Confused at times).

  25. #25
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,444

    Re: rs.Edit (help)

    Quote Originally Posted by ISAWHIM View Post
    Why not just indicate the "default" as a unique entry that just holds a record number. Any record that is NOT that number is NO, while the record number that IS that number, is YES.

    Why append useless, unique data that only has one value to every single record?

    As for no duplicates...

    Check if a record exists, before adding it or altering it. If it exists, then don't add it or delete the current record. Then there will not be any duplicates. (You delete the current record, if it is altered to matching another "existing record", because it already exists.)

    If you are importing data and don't want duplicates, then you have to search before adding each new record, if one already exists. (I think there is a test for "unique", which does that. However I don't know if VB does that with any of the database tools it has available.)
    what utter nonsense. Both ideas.
    being the default record is an attribute of the record itself. Read up on normalization rules.
    the usual mechanism to declare a record the default are Triggers, which Access doesn’t support, AFAIK

    as for duplicates: Sam pointed out the absolute minimum requirement to avoid them.
    What I don’t know, too, is if Access supports the ON DUPLICATE syntax, which is how the database itself should handle duplicates.
    reject the new values or replace the existing values
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  26. #26
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,140

    Re: rs.Edit (help)

    As the OP has mentioned in other threads, they are a Chef by trade. They are trying to get their program functional, not needing to pass some sort of stringent commercial coding standards.

  27. #27
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,176

    Re: rs.Edit (help)

    Access has an option in its fields to Allow Duplicates (or not). I tend to leave that at YES, but it IS an option. I just got used to leaving the default (YES) and using UNIQUE.
    Sam I am (as well as Confused at times).

  28. #28
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,048

    Re: rs.Edit (help)

    Quote Originally Posted by SamOscarBrown View Post
    Access has an option in its fields to Allow Duplicates (or not). I tend to leave that at YES, but it IS an option. I just got used to leaving the default (YES) and using UNIQUE.
    you can also set a Default value for a Field in the Database Table

    Name:  Defaultvalue.jpg
Views: 68
Size:  12.4 KB

    this is an Access Database, you can see the Default value in the new Record
    I set the default value to someText
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  29. #29
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,940

    Re: rs.Edit (help)

    Here's an idea: Forget this "Default" field (deleting it from the table). Add a new field with the "AutoNumber" set so that each record gets a unique number.

    Then, create a new Table (maybe named "TheDefault") with just one record in it and one field (numeric) in that record (Maybe named "DefaultAutoNumber"). This "DefaultAutoNumber" field is known by you to be a reference to a specific record in the main table, with that record being your default.

    With that scheme, you can easily change the default, and you can also be assured that there won't ever be a situation where you have multiple records marked as "Yes" for "Default". Or, the possibility of NO records marked as "Default".

    And it would all be WAY easier to maintain, and wouldn't require any loops (SQL or otherwise).

    Just a thought.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  30. #30

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    each one has an ID.. that doesn't replace the DEFAULT.... AGAIN, i am not an expert coder like some of you.. i'm more of a designer.. yes, i can do SOME things.. but show example code or something instead of suggesting things... anyways, i'm going to bed cause i have to be up at 2am and it's 8:30pm...
    Attached Images Attached Images  
    Last edited by elRuffsta; Mar 2nd, 2024 at 08:29 PM.

  31. #31

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    if you guys really really want me to redo this app from scratch for the 10th time... and be there to see it through.. say so.. but i am not gonna waste my time otherwise in re-doing it again.. 9x was enough for me

  32. #32
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,176

    Re: rs.Edit (help)

    I see that this appears to be a completely different project than the one I had offered to assist with about vegetables.

    if you want me to look at this one zip it and attach it. Then tell us what you want the project to do. You’ll have to specific in your explanation.
    Sam I am (as well as Confused at times).

  33. #33

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    Quote Originally Posted by SamOscarBrown View Post
    I see that this appears to be a completely different project than the one I had offered to assist with about vegetables.

    if you want me to look at this one zip it and attach it. Then tell us what you want the project to do. You’ll have to specific in your explanation.
    it's really not.. it's still about vegetables.. just trying to convert one of my old apps into doing it.. i'd still rather have the original one..

  34. #34

    Thread Starter
    Hyperactive Member elRuffsta's Avatar
    Join Date
    May 2014
    Location
    OH
    Posts
    404

    Re: rs.Edit (help)

    Quote Originally Posted by SamOscarBrown View Post
    I see that this appears to be a completely different project than the one I had offered to assist with about vegetables.

    if you want me to look at this one zip it and attach it. Then tell us what you want the project to do. You’ll have to specific in your explanation.
    ok Sam.. i'll give you the full dummied up basics - (since you refuse to look at the final project outside of here)... i'll keep the design/look to myself cause i can edit it to look what i want in the end.. i have started on it and will post the zip when i'm comfortable releasing it.. it may be tonite or tomorrow. but this will be the last re-do of the app for me.. 10x is more than enough for me, so i am just coding in the basics of it...
    Last edited by elRuffsta; Mar 3rd, 2024 at 09:28 PM.

  35. #35
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,176

    Re: rs.Edit (help)

    Ok. Just make sure you send some ghost explanations of what you want it to do.
    Sam I am (as well as Confused at times).

Posting Permissions

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



Click Here to Expand Forum to Full Width