Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    4

    Unanswered: Duplicating line in an invoice report

    Hello,

    I am sure someone will have a simple answer to this.

    I am building a simple invoicing tool. It records the clients details in a registration table, with a uniqe issues autonumber account number (with a primary key). The second table contains a list of products,

    The form contains 2 parts

    1) To list the customers and type in a customer orders number.
    2) Sub-form to allow the customer to buy many products.

    All works fairly well. Except in the sub form, there is a field that allows some notes to be typed into a box. Again that works ok. (We type dates into this field)

    But if I add a second line with the same probduct and a different date, the lines double themselves on the report. But the final information that gets recorded onto a third accounts table is correct.

    Here are some expamples of the sub-forms (not all the details, just the problem part)

    Buffet, ,10, 5:00....
    Donughts, 27th April, 100, 0.15....
    Donughts, 28th April, 150, 0.15...
    Canapies, ,50, 1.00...

    So far to good, when I press the button to create a report, it posts all the correct informtion to the apporiate table witht the correct amounts.

    How the report will come out like this.

    The field where the date gets added is called the date of service field.

    Buffet, ,10, 5:00....
    Donughts, 27th April, 100, 0.15...
    Donughts, 28th April, 100, 0.15...
    Donughts, 27th April, 150, 0.15...
    Donughts, 28th April, 150, 0.15...
    Canapies, ,50, 1.00...

    So wheneven some extra text or dates are typed in the sub-form, the report seems to double the lines and give me the wrong invoice amount, and yet the correct amount will have been posted to the approriate table.

    I have attached a sample of the database.

    When you open the database, press Enter and then Raise Invoice.

    Click the customer drop down and choose a customer, the details will then be populated on the screen.

    Next choose the Product Code from the sub form and choose some products.

    Tab along to fill in the QTY, and continue tabbing....it will caluculate the fields for you.

    The example I gave you.....

    Buffet, ,10, 5:00....
    Donughts, 27th April, 100, 0.15....
    Donughts, 28th April, 150, 0.15...
    Canapies, ,50, 1.00...

    Type the dates in the Date of Service for the donughts. (NOTE These are VAT free so their will be no VAT Caluculations for them.)

    Then press, Raise Invoice and it will produce the invoice on the screen. The correct amounts will have go onto the necessary tables, but the donoughts line will have doubled themselves on the invoice screen and therefore given the wrong invoice amount.

    The problems seem when the dates are added. (What the customer wants).

    Many thanks for your help
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Gordon,

    When you get a job, please remember to NOT ever use special characters like a dash or spaces in your object names or field names. It WILL come back to make your life, and anyone that follows you in that job, much more difficult than it needs to be.

    Now, to your assignment. I downloaded your database and walked all the way through to the invoice. I put in two products and got out two products on the invoice. I expected the line with service dates added to be duplicated as you said, but there was no duplication. Sorry I can't help with this problem.

    Oh yes, it is always nice to tell people that you are asking to help you that you are a student. It really is not good for us to do your homework for you, but we are happy to help guide you to the solution.

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by GolferGuy
    Oh yes, it is always nice to tell people that you are asking to help you that you are a student.
    I would think basic honesty demands it, and I certainly won't help someone who is dishonest.

  4. #4
    Join Date
    Apr 2007
    Posts
    4
    Vic,

    I have never seen such a patronising reply in my life....you are clearly someone who has very little dealing with the public.

    i put the request on to be sorted out...not patronised....when you can't answer it, don't answer it....very simple

    Firstly I am the manager/owner of a business....how the hell you seconded guessed that I am some student with a some sort of MS Access assignment is unbelievabe. How did you manage that? I am curious.

    Secondly had you read the instructions properly (as others have done on other forums) you would have been able to replicate the problem.

    Thirdly the problem has been solved now. On another forum. Sorry you couldn't sort it.

    The database was being built by me for a pal who has a small catering business. My weakness was with Access was reports. Why on earth you and Scooby thought I was being dishonest, is both amazing and patronising.

    Regards

    Gordon

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Guys - this was clearly not a homework thread and even if it was it was submitted by someone who had done 99% of the work and had a very specific problem - this is a very different senario to a student copying and pasting their assignment into a thread.

    Gordon - I would be grateful if you could post links to the duplicated threads if you do post your questions on multiple forums so that contributors can see the avenues taken by people on other boards.

    Ta!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Gordon,
    Sorry for the offence. Not only did I take the time to download your database, walk through everything that you posted for instructions, but as I continued to work on the situation to try to find your duplicated records problem, I ran across a record like this:
    Gordon Dow, The Bourse Business Centre, "Simplicity Computer Training". Plus it was your first post, which can be another indicator of a student.

    Silly me, I had just been politely reprimanded the day before for giving the answer to a student, which I had not seen to be a student. But your database looked like a student because of the entry cited above. Sorry I was wrong about your student status. We rarely get, except from students, such a detailed explanation of the problem. Good job!

    Now, about my intelligence and handling of others. First I must really be stupid, because I just downloaded your database again, VERY carefully followed your instuctions, and I have attached an image of the invoice that your system created for me, correctly, one more time. I would love to see the directions you gave in other forums so I could see the difference(s) in the directions. And my people skills? I just re-read my response to you and I do not see any patronizing in my response. Patronizing is talking down to someone. I merely accused you of being a student asking for help with homework but not stating it was homework. Like I said before, sorry.

    What I found interesting is for someone that must have such great people skills as you seem to think you do, to fly off the handle because you were mistakenly accused of being a student. Hey, I spend quite a bit of time on this forum and another forum helping others because I know how frustrating it is to know what I want to do, but don't know how in this new tool. Maybe next time you can look at the response you get, and rather than being such a victim, look at the fact that someone did take the time to try to help you, but your directions were somehow flawed so when they were followed, the error did not surface.

    Sincerely sorry about you feeling offended, but I will continue to help people that want help, even if they aren't thankful.
    Attached Thumbnails Attached Thumbnails ScreenCapture_019.jpg  

  7. #7
    Join Date
    Apr 2007
    Posts
    4
    Vic,

    Thank your apology, and I apologise too.

    Lets start again.

    Attached are three JPG's

    The first is what I want you to enter up. (you can enter a manual cost...the client wants this and the VAT is supposed to be Zero). The problem seems to occur when the dates are added to the dates of service field.

    The second shows what appears on the invoice. With the duplicated lines.

    The third shows the correct amounts being posted to the sales details table, which is what I expected to happen. It's just this report.

    Also the answer we had on another forum, was to take the date of service field out and keep it as a seperate comments field.....but I didn't feel this was adequate.

    Regards


    Gordon
    Attached Thumbnails Attached Thumbnails Sample 1.bmp   Sample 2.bmp   Sample 3.bmp  

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I just want to defend Vic:
    Quote Originally Posted by GordonEdinburgh about Vic
    ...you are clearly someone who has very little dealing with the public...

    ...how the hell you seconded guessed that I am some student with a some sort of MS Access assignment is unbelievabe. How did you manage that? ...
    Vic dedicates a lot of time to this forum, specifically the Access topics. He is always willing to do his best to help out any problems you or others may have! He provides *free* expert Access advice and knowledge that he has gained over many years - so he's allowed to get antsy

    And the student comment: we get a suprising amount of students who come on wanting help with their assignments, so unfortunately some of us jump to this conclusion all to redily.

    But hey, assumptions were made and apologies were too
    EDIT: Didn't realise you had just posted too - Yay! Isn't it better when we all jsut get along?
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your main problem is you have a normalisation problem.
    http://r937.com/relational.html

    To fix the problem as it stands you need to create a join in the query [qry - final invoice] between [dates of service] in the tables [tbl - orders - details] and [tbl orders - sales].

    Since the temperature seems to be cooling now may I also say Vic is quite right that you would be well advised to stick to alpha characters and underscores only in field and object names.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2007
    Posts
    4
    Hello Pootle,

    Thank you, that sorted the problem out just the way I wanted.

    I'll also take onboard your and golferguy's comments about dashes and spaces.

    I run the company that does training on MS access and other microsoft applications and my guys could see what the problem was. I only dabble with it...haven't build anything serious for many years now.

    Thanks again for you answer...I knew it was a simple solution.

    Regards

    Gordon

Posting Permissions

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