Page 1 of 4 1234 LastLast
Results 1 to 15 of 48
  1. #1
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70

    Question Access 2010 Code needed for List Box and Sub-Form

    Hello,

    I am fairly new to MS Access 2010; and have a problem I don’tseem to be able to resolve.

    I am trying to write an Invoice DB

    One part of the software is filling out an annual taxstatement form with a sub-form connected.
    This sub-form has 5 tabular fields, and is set tocontinuous. Link Master & Child are: Invoice No; InvoiceDate

    These are the fields:

    Invoice No
    InvoiceDate
    LastName
    Cost
    Amount

    The List Box has exactly the same fields as the sub-form, ismulti-select (extended), and populated through a query.

    List Box name: TaxListBox(unbound)


    Sub-Form name:LineItems AT Sub-Form
    Button name:SelectInvoice-btn

    I have looked around for a long time, but every time I thinkI’ve found the necessary code, I miss a critical part to make it work.

    Any expert out there who is willing to help me?

    Thanks very much beforehand!

    Leen

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What are 'tabular fields'?

    Why is InvoiceDate part of the Master/Child linking?

    What is the role of listbox?

    What exactly is the problem you encounter? What exactly is not working?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70

    Access 2010 Code needed for List Box and Sub-Form

    Quote Originally Posted by June7 View Post
    What are 'tabular fields'?

    Why is InvoiceDate part of the Master/Child linking?

    What is the role of listbox?

    What exactly is the problem you encounter? What exactly is not working?
    ------------------------------------------------------------------------------------------

    I'll try to answer your questions.

    1) Tabular fields are the result of the choice I made when setting up a sub-form (instead of, for example, columnar)
    2) I am not sure why I linked these fields. I guess Invoice No should be enough?
    3) The list box shows all invoices, sorted by invoice no and last name; I pick the invoices from a certain year and want to transfer them to the sub-form, ready for print-out.
    4) I have the settings of the list box set to extended, etc. but need a code to transfer the selected records in the list box to the sub-form. The code examples I saw are great, but I have a hard time to "translate" the different items needed. I hope that makes sense?

    Thanks,

    Leen

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Really best to use a report for printing.

    Still not sure what you mean by tabular - how is that different from columnar? Is the subform in Datasheet, Continuous, or Single view?

    Records are not 'transferred' from listbox to subform, the subform is filtered by values of listbox item.

    If you want to choose more than one listbox item for printing, that requires code as demonstrated in http://allenbrowne.com/ser-50.html

    It would be simpler to select invoices for printing by range parameters such as beginning and ending invoice number or date. This can even be accomplished without VBA through use of dynamic parameterized query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Forget about "tabular fields." I thought, based on what I saw in this forum it'd be good to be as extensive as possible.
    I may not understand the right terminology, but if I select several list box items, then press the button, I assume the selected records are "transferred" to the sub-form.

    I have looked at range parameters (year), but get stuck every time. Maybe you have some good advice there.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, records are not 'transferred', a filter is applied. If you use a multi-select listbox to select multiple parameters then will require VBA code as shown in the link.

    Example of parameterized query with date range:

    SELECT * FROM tablename WHERE [Datefieldname] BETWEEN [enter beginning date] AND [enter ending date];

    Post your attempts for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Hello again,

    I am going to follow your advice by creating a parameter query, and am sure it'll work. Thanks for your advice.
    However, I have one more problem and hope you can help me this one as well.

    When I create an invoice, I use a combo box for the product description, that takes the info from the Product table. In 90% of all cases I do not need to add or change the description, but sometimes it is necessary. This information becomes then part of the invoice, being saved. However, I don't want to change the underlying Product table, which is what automatically happens.

    How can I change that?

    Looking forward to your answer...

    Leen

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Are you saving the ProductID or the ProductDescription into the invoice?

    How can selecting the product in combobox change the description in Product table? If this is happening, sounds like Product table is included in the form RecordSource and the combobox is bound to the wrong field, in which case no data is saved into the invoice record. So I am very confused by what you describe.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    I don't think so. The ID is the Invoice No.
    The Combo box is integrated in a subform. Link Master and Child are both Invoice No. The product ID is the control source; the row source is a query. This is the sql: SELECT Product.ProductID, Product.Description
    FROM Product;

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The subform is bound to what table - InvoiceDetails or some such name - I certainly hope it is not the Product table. The combobox ControlSource needs to be the field in InvoiceDetails table where you want to save product ID. What is name of that field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Oh my, It's good to know I'm good in a lot of stuff, but MS Access is obviously not one of them.
    The sub-form is from a Line Items Query, with the combo-box (product ID, Description) also a query, but taken form the Product table.
    No matter what I 'edit' on the Invoice form (sub-form, description, price, or amount) it shows up in the product table.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What tables are in the Line Items Query? Sounds like it includes the Product table.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Hi again,

    Here is my database copy, still in the making of course.
    Thanks for helping me!

    Leen
    Attached Files Attached Files

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    INNER JOIN requires related records in both tables for record to show in the query. The Line Items Query uses INNER JOIN, should be RIGHT JOIN.

    The Line Items IM and PI subforms have textbox bound to Description field from Product table. This textbox should be Locked Yes and Tab Stop No to prevent edit. Options for user input of description are:

    1. create new Product record

    2. save the product description text into Line Items

    Then there is the question of whether or not to save the price into Line Items. If the price is likely to change in the future, don't want that change to be reflected in existing invoices. So again, options are:

    1. create new product record

    2. save the price into Line Items


    BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention for fields and objects.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Thank you for your quick response!
    I had to read it three times before I understood it one time...

    The INNER join remark I just have to believe that this is the right thing to do; I'm afraid I don't really understand what that is all about.
    Then I locked the text boxes (Description) for both Line items, and I can't edit it now at all. What I'd like to do is when creating a NEW invoice having the possibility to ADD to the description text, as I did in the past, without having this "adding" being saved to the product table.
    With the locked on yes and tab on no, I can't edit the field at all. Would you have a solution for that?
    Thanks also for your advice about the punctuation, etc. I'll take that into account with the next DB.
    Looking forward hearing from you.

    Leen

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 05-07-2013, 08:37 PM
  2. Replies: 12
    Last Post: 01-07-2013, 03:32 PM
  3. HELP>Access 2010 User Login Form Code.
    By zaaimanm in forum Programming
    Replies: 5
    Last Post: 10-22-2012, 07:28 PM
  4. Replies: 0
    Last Post: 07-31-2012, 12:25 PM
  5. code needed to filter form using combo boxes
    By drjim in forum Programming
    Replies: 1
    Last Post: 06-29-2012, 01:50 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums