Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 48
  1. #31
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Hello June7,



    After trying for quit some time, I have to admit I am not getting anywhere. What I did:
    I added two fields in Line Items (Description_Line Items) and (Price_Line_items) - without the added LI Access gives an error
    Deleted the text boxes and labels in Line Items PI Sub-Form
    I added the fields from the table Line Items into the PI subform
    I did the AfterUpdate: Me!Description_Line Items=Me.Product ID.Column(1) (Product ID seems the name of the combobox)
    And the AfterUpdate: Me!Price_line Items=Me.Product ID.Column(0)
    The fields show up, but with the #name in it.

    Would you be willing to give me a step by step guide how to accomplish this?
    (It is really not sinking in)

    Thanks very much,

    Leen

  2. #32
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You show a space in Product ID and Description_Line Items and Price_Line Items. If names have space or special characters, must enclose in []. This is why advise to avoid in naming convention.

    Me.[Product ID].Column(1)

    Why do you reference Column 0? Isn't that the column with the ID?
    The combobox RowSource must pull fields from Products table:
    SELECT [ID], Description, Price FROM Products ORDER BY Description;
    If you want to save the Description text as well as Price, don't need ID field.

    Then you need fields in Line Items table to save Description and Price. I presume those are Description_Line Items and Price_Line Items. Description_Line Items must be TEXT data type if you want to save the text and not the ID from Products.

    The code will save Description and Price from the combobox columns into Line Items fields. However, if the combobox is bound to TEXT field Description_Line Items, code is not needed for that value.
    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. #33
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    This is what I get when adding the new fields:

    To complete this operation, Access must modify the RecourdSource property of the current form or report. ...will create a new query. The form... will no longer be based on the "Line Items query.

  4. #34
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If the form RecordSource is based on a query object then modify the query object. Whenever possible, I build SQL statement directly in the form RecordSource instead of referencing a query object or just reference the table itself. The SQL could be simply: SELECT * FROM [Line Items];
    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. #35
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Dear June7,

    I am getting from bad to worse... I changed the query (not sure how to change the sql statements), followed your code, etc. but got an error: "The expression AfterUpdate you entered... The object doesn't contain the automation object 'Product ID'.

    The way I learn the best is by example: If I see the example, I often understand immediately; the other way around is very difficult for me to comprehend.

    That's why I asked for a step by step approach. Would you be so kind?

  6. #36
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Setting the RecordSource of a form is basic Access functionality. I am not going to try to teach basics in forum. Access Help has guidelines as does any introductory Access tutorial book.

    http://office.microsoft.com/en-us/ac...101814106.aspx

    Setting the RowSource of a combo or list box can use the same techniques.

    If your field is not named Product ID then what is the name?
    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. #37
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    This was not my point. You don't have to teach me basics. You write, "Whenever possible, I build SQL statement directly in theform RecordSource instead of referencing a query object."
    That goes over my head. You are, say, at level 100, while I am sitting at level 25, meaning give me an answer I can understand, or, descend to my level. That would make a great teacher.

    In my Line Items PI Subform are the following fields: Product ID (Combobox), Description_LI, Price_LI, and Amount. Whatever I try, neither Description or Price show up in the subform or the table.

    So, if the magic trick is to write a sql statement, I ask you to write this sql statement for me. When I see a good example, I often understand what the writer is trying to accomplish. A good teacher is to be imitated.

    Last, but not least, If I would have to study these basics, you might not see me back for a year, and my question then would be, "What is the purpose of this forum?"

  8. #38
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I provided a link that gives step-by-step for creating RecordSource or RowSource. It says pretty much what I would if I listed points in post.

    Identify specific point that you do not understand and will try to clarify.

    I did offer an SQL statement in post 32.

    Purpose of forum is to help, guide, share. We are mostly volunteers (I am one). Also, I guess the owner hopes to make some money somehow.

    I have old version of your db - do you want to provide latest?
    Last edited by June7; 05-28-2014 at 06:29 PM.
    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. #39
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Hello again,

    I watched the video and there is nothing in the video that I don't understand. However, it still doesn't help me with my problem. I've pretty much tried everything; the combobox doesn't work; yes, the right record source. Then the description text doesn't show up. I've make a brand-new Line Items Sub-form, took all the spaces out the naming, but no results, etc. etc.

    I've gone back to my "old" backup and it is a delight to see at least the program working, be it not fool-proof. And that's what I want. I understand the description and price field have to be written to the Line Items Table, but nothing is written to it; it just doesn't happen. I understand tat the combobox gets its info from the Product table, but it is empty when tried. You do have my database; would you please have a look at it to see what I could do?

    I'd be very grateful!

    Thanks,

    Leen

  10. #40
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I have your db from post 13. Nowhere is there a combobox for the product. It doesn't have fields in Line Items for the Description and Price. It has your original naming convention. Is that the version you are working with?

    This version has Invoice Main Form and Patient Invoice Form and several Line Items subforms. I will focus on Patient Invoice Form.

    I added fields Desc_LI and Price_LI to Line Items table. No other changes to data structure.

    The main form RecordSource is an SQL statement with an INNER JOIN of Patient and Invoice tables. INNER JOIN requires related records in both tables for a record to display. Change the join type to RIGHT JOIN (or remove Patient table from the query):
    SELECT Invoice.[Invoice No], Invoice.InvoiceDate, Invoice.Payment, Invoice.[Patient ID], Patient.Title, Patient.[First Name], Patient.[Last Name], Patient.[Phone No] FROM Patient RIGHT JOIN Invoice ON Patient.[Patient ID] = Invoice.[Patient ID];
    Probably want to set textboxes bound to Patient fields as Locked Yes and TabStop No to prevent editing.

    The subform RecordSource is a query object with similar situation - INNER JOIN should be RIGHT JOIN (or eliminate Product table from the query):
    SELECT [Line Items].[Invoice No], [Line Items].[Product ID], [Line Items].Qty, [Line Items].Desc_LI, [Line Items].Price, Product.Description, Product.Price, [Qty]*[Price] AS Amount FROM Product RIGHT JOIN [Line Items] ON Product.ProductID = [Line Items].[Product ID] ORDER BY [Line Items].[Invoice No], [Line Items].[Product ID];
    Personally, I would make the RecordSource an SQL statement: SELECT [Line Items].*, Qty*Price_LI AS Amount FROM [Line Items] ORDER BY [Product ID];

    Combobox for Product - this setup will save ProductID to Line Items table by binding combobox to field:
    RowSource: SELECT Product.ProductID, Product.Description, Product.Price FROM Product ORDER BY Description;
    ControlSource: Product ID
    BoundColumn: 1
    ColumnCount: 3
    ColumnWidths: 0";2";0.25"

    Code in combobox AfterUpdate event to save description and price in Line Items record:
    Me!Desc_LI = Me.cboProduct.Column(1)
    Me!Price_LI = Me.cboProduct.Column(2)

    Bind textboxes to Desc_LI and Price_LI fields. Users can edit those fields. Keep in mind that changing selection in combobox will overwrite the values in those fields.

    If you don't want to bother saving the ProductID into Line Items, then adjust the combobox and code and RecordSource accordingly and remove Product ID field from table.

    Maybe you will find discussion in this thread on similar issue informative https://www.accessforums.net/forms/l...rty-44098.html
    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. #41
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Hello June7,

    Thanks very much for all your work; it is really appreciated!
    Your question about "No combobox" has answered itself at the end of your response. Line Items PI Sub-Form indeed has the combobox included.

    I am going to give it a try, but may take some time...

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

    It works!
    I may not understand fully what you advised, but I got it all working. Your last advice made me really think and I was quite excited to start trying it out.

    Thank you so much for your patience and help. I might call on you again in the near future.

    Leen

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

    After having recovered from my "high", I tried to find an answer to another question. The internet is a wonderful source, but it is pretty tough to ask the right question.

    I have an input form for new a patient, with:
    ID (autonumber), Title, First Name, Last Name and Phone No.

    I discovered I had filled a record twice. I don't seem to find the right answer how to avoid duplicate records. So, there may be 2 Mr. John Smith's, but they must have a different phone number, or Mr and Mrs John and Jane Brown, having the same phone number.

    How would I resolve this? Preferably by having a pop-up msg warning about the duplicity.

    I hope you can me here as well!

    Thanks,

    Leen

  14. #44
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Options:

    1. Set the Last Name, First Name, Phone No. as a compound index http://office.microsoft.com/en-us/ac...010210347.aspx

    2. VBA procedure that looks for that combination in the table before committing the record

    But really, there is only so much can be done to thwart bad data entry. No system is perfect.


    Suggest you avoid spaces and special characters/punctuation (underscore is exception) in naming convention.
    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. #45
    Leen is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    70
    Hello,

    The problem I have with indexing is it really does not resolve duplicity, as I see it. Make a field unique, and the same phone no for husband and wife is blocked; and so the same with two John Smith's.
    I have seen many code samples, but am not sure if they could solve this problem, at least up to a point. If it were your software, what would you do/write?

Page 3 of 4 FirstFirst 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