Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    Jun 2008
    Posts
    59

    Unanswered: Updating A List Box

    I have DB with two lists on a form. i can move data from A to B placing the data in B in a seperate table for later use
    i want to refresh list A to its original form to again use it at a later date
    how do i do this(i am a newbie so need specific details if this can be done
    rgds dennis

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I understand that you have an incoming list A, from which you wish to select records to be entered into the list in B and (optionally thereafter delete the records from list A).
    I encounter this situation quite a lot in situations where list A contains a list of offerings which can be selected by the user (e.g. dishes on a menu). The selected ones go into list B. I do it by having the user double-click in one list which then populates the second list. A choice can be deleted from the second list by double-clicking it and so on.
    If that is what you want, do the following:
    Right-click on the first list and select properties. Set focus on the Double-Click Event and click on the button at the RHS. This opens up VBA.
    In list A, create an INSERT SQL statement to add the data to table B, then do a requery on list B. If you wish, run a DELETE SQL statement on List A and requery similarly.
    If you want to keep the record in List A, but not have it selected again, add a boolean field to table A and set it to True when the record is selected. The Rowsource for Table A should ask for alll records except the checked ones.
    You can reset the boolean variable on closure if you like.
    Finally, if you wish ro reverse a choice, double-click on list B and in the event code, delete the record from table B, requery table B, reset the flag in table A and requery on Table A.
    I hope this helps.

  3. #3
    Join Date
    Jun 2008
    Posts
    59
    Many thanks Jim you are definately on the right path,my problem is as a newbie i am not up to VBA level yet but am learning fast!
    could you help me with"insert sql statement to add data to table B" and
    "do a requery on list b" I think i need to use append and delete queries but am not sure if that is what you mean
    thank you for your patience
    rgds dennis

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    "insert sql statement to add data to table B"
    He's referring to an append query.

    "do a requery on list b"
    Me.FormObjectName.Requery will requery one object on a form.

    Get VBA under control Dennis, once you do, you will wonder why you waited.
    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

  5. #5
    Join Date
    Jun 2008
    Posts
    59

    VBA "user"

    thanks star trekker,yes i do want to get vba under control? in your opinion what is the best way to go about it,i now have the time and desire to become "user friendly"with VBA
    rgds dennis

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Start small, work your way up.

    There are many reference books and online tutorials about that you can use, I just don't have links as the Internet consisted of single page "sites" when I was learning it... I used help files and the hard-copy user manual (there's something you just don't see anymore!) to learn it.... and drawing on experience I had with QBASIC and other BASIC languages. I started coding in '82 ^^
    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

  7. #7
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    You don't need to prop up your credentials, Startrekker. I wrote my first programs in Algol and Fortran in 1967 and I think you are probably cleverer than me. Oh, yes, I still have original C White Book, written by the authors of that language in around 1971.

  8. #8
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I started coding in '82 ^^ (Sorry, couldn't find the Quote thingey)

    You don't need to prop up your credentials, Startrekker. I wrote my first programs in Algol and Fortran in 1967 and I think you are probably cleverer than me. Oh, yes, I still have original C White Book, written by the authors of that language in around 1971.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sorry, reminiscing ^^

    Algol? There's one I've never heard of... but I guess it's logical since I was 1 in 1967 lol
    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

  10. #10
    Join Date
    Jun 2008
    Posts
    59

    append to a list box

    thks Jim,i have tried to follow your suggestions and all is well until i dble click on a name in box A,I then get a syntax error in my VB
    Mysql statement is as follows
    Private Sub ListboxA_DblClick(Cancel As Integer)
    INSERT INTO [List Box B] ( [Members bowls data], [Full Name], [Tag No] )
    SELECT [LIST BOX A].[Members bowls data], [LIST BOX A].[Full Name], [LIST BOX A].[Tag No]
    FROM [LIST BOX A];

    End Sub
    i cannot detect an error??
    rgdfsw dennis

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There needs to be a space between your words...
    Code:
    INSERT INTO[List Box B] ( [Members bowls data], [Full Name], [Tag No] )
    SELECT[LIST BOX A].[Members bowls data],[LIST BOX A].[Full Name],[LIST BOX A].[Tag No]
    FROM[LIST BOX A];
    Oh, and of course, you need to execute this statement (see the helpfiles for DoCmd.RunSQL)
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2008
    Posts
    59

    append query

    tksw george,i used your revised code but i still get a syntax error?
    i created the append query using the grid,as i am learning VB why does it not write the correct code automatically or am i doing something wrong?
    many thanks dennis

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can't run an SQL statement in code like that. VBA needs VBA commands, not SQL statements. To run an SQL statement, you must Execute it....

    vSQL = "INSERT INTO ....... "
    CurrentDb.Execute vSQL, dbFailOnError


    For example.
    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

  14. #14
    Join Date
    Jun 2008
    Posts
    59

    updating list boxes

    tks Jim and the forum,i have been able to follow some of Jim's details but have come unstuck on some items
    i created an "insert sql statement" as follows Private Sub ListboxA_DblClick(Cancel As Integer)
    INSERT INTO [List Box B] ( [Members bowls data], [Full Name], [Tag No] )
    SELECT [list box A].[Members bowls data], [list box A].[Full Name], [list box A].[Tag No]
    FROM [list box A];

    End Sub
    when i run the dble click event i get an error message "syntax error"
    when i run a command button"run the query and update records it copies all 122 records to my table B
    Sorry but how do i create a requery on the table B
    will the above corrections select individual names and show them in list box B
    Sorry to be a nuisance but i am learing VBA slowlu?
    rgds dennis

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Code:
    Private Sub ListboxA_DblClick(Cancel As Integer)
       vSQL = "INSERT INTO [List Box B] ( [Members bowls data], [Full Name], [Tag  No] ) SELECT[Members bowls data],[Full Name],[Tag No] FROM [list box A];"
       CurrentDb.Execute vSQL, dbFailOnError
    End Sub
    Zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzip.
    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
  •