Unit price increase and how to report historic sales as well as present sales - help needed please

[anniehall]

Please be more specific. Which report exactly is it and which field name? Everywhere I looked the price was being displayed correctly.

Hi Aytee111
From the Switchboard,
a) Report of cards currently Post Office (my copy) - enter OK for photo (all been stripped out), * for occasion code, OK through the rest - one entry but price is blank
b) Cards sent to Post office - error message referring to photos (possibly because all stripped out)?
c) Invoice for Post Office with dates 1/8/16 and 30/10/16 - 2 entries, one with price (transaction before price changes), one price box blank
d) Report of cards currently at Post Office (PO copy) - see a)
e) Report of sold cards - can be filtered - enter OK for photo (all been stripped out), * for occasion code, OK through the rest - several older entries with prices, one new entry but price is blank

Can you help?

[aytee111]

a) Doesn't happen for me! Probably due to the fact that I deleted all records with blank price in the tables - that just messes up testing
b) Error states that "Photo1" is being referenced without a corresponding table name but it could come from more than one table so Access does not know which one the query is referring to
c) Same as a?
d) see a
e) works for me

When you are displaying data from a table in a form or report or query, always check the table itself to see if the data is being displayed correctly.

[anniehall]

Just checked my full database. The prices have not been picked up on the Event Procedure. I'll have to have another look and see what I've done wrong. Probably be a day or two - toddler grandson minding tomorrow! Thanks for your time anyway. Keep an eye out for me - I'm bound to need more help with this.

[aytee111]

Test on the database you sent me, delete the blank rows from the movement table. Much easier to test that way with only 4 records.

[anniehall]

OK Good plan. I'll get back to you if I need to.

[anniehall]

Hi Aytee111
I've worked out what the problem is but I can't solve it. the problem is in the "defaultprice" field in the card movement subform. Where I've added in the before insert event procedure so that the default price auto fills from the Cards table, it's no longer putting the figure through to the Card Movement table.
How can I fix this?
Annie

[anniehall]

What I need is some additional coding which says "after auto filling with default price from Cards table plug this number into Card Movement table". But I haven't a clue how to do this

[aytee111]

I presume you are working on the subform with the suffix "1"? Not good to have multiple, very confusing!

There is no "Price" field on the subform. For bound fields you have to put a value into it in order to store it on the table.

And do you know I gave you bad instructions! For the record source of the subform you don't need to join with the card table, let your record source be just the movement table, no query. Then in the BeforeInsert this will be the code: Me!Price = Forms![Fm:Card Entry and Tracking]![Price Nov 16]. Dlookups are good but should be used as little as possible as they are heavy users. Here we can just copy it from the main form.

BTW, special characters and spaces are VERY bad practise in names of database objects, field names, etc. Those are for display only, not for use in the designing area of a database.

[anniehall]

Hi Aytee111. Thanks for the revised code. That seems to be working fine. The only outstanding thing now is how to make the subform (Tbl:Card Movement Record subform1) multiply the price by the number of cards. So if I want to send several of the same item to an outlet, the total price is plugged into the Card Movement Record, not the item price.
Using your new code for reference, I've tried several options (AfterUpdate event procedure on Number of Cards, BeforeUpdate event procedure on Price) but I can't get it to work.
What am I doing wrong?
Ann

[aytee111]

Add a new textbox to the form, its Control Source will be : =[Price]*[Number of Cards]. That's all you need. NEVER store information of this kind, it is a calculated field and will be re-calc'd whenever you need it. For historical purposes you need to be able to keep track of how many you sold to the customer and at what price.

[anniehall]

Hi Aytee111. Thanks for that. I think it's all sorted now. And I already had a number of calculated fields in various reports, so should have been able to work that out.

In an earlier post you say "BTW, special characters and spaces are VERY bad practise in names of database objects, field names, etc. Those are for display only, not for use in the designing area of a database". Why are spaces etc bad practice in names? And should I go through and change them in my database?

[aytee111]

Special characters are used by Access to denote different things such as arithmetic operators, wildcards, etc, and mistakes can easily happen - these field names must always be surrounded by square brackets and if you forget them then the instruction might be translated by Access into something entirely different to what you think. Spaces also require the use of square brackets every time and errors can easily creep in. These will always come back and bite you at some time in the future.

There is the developer part of a database and the user part and they are very different. Develop databases following technical rules and regulations, designs and methodologies. For the user make it look pretty and be grammatical - even if you are the only one using the database!

Changing names, especially field names on tables, is very difficult once the database has gone a long way down the road. One consideration is how far along are you. Another is what is the intention of this database, will you be in business for a long time, will the database grow? I would say yes to changing it, if you can. If nothing else, the exercise will get you delving into the database as a whole and you will learn a lot!