Invoicing Problem

[slogie]

That is my issue guys!!! I do not want to have to manually input the price.

 

[pbaldy]

Sorry slogie, I didn't realize you were online. Which form?

 

[slogie]

Ok originally, I had the price in the Itemlist form, but you said to put it in the Transaction form. So I did that but nothing is working. I have to put the price in myself. The form is the Cash Inv Query form.

 

[pbaldy]

I don't see the price in the Itemlist table, which appears to be the table for products. That table would typically contain the current price, which you would copy to the transaction table when you pick a product.

 

[slogie]

Yes I know, that was where I had it, but you guys told me to move it from there. It worked ok in the Itemlist table except for the fact that it changed in the invoices when I changed the price.

 

[pbaldy]

Try this. We didn't tell you to move it, we told you to add a field to the transaction table.

 

[pbaldy]

To clarify, you have the data in two places, which would seem to violate normalization rules. However, it really does not. The price in the products table is the current price, an attribute of the product. The price in the transaction table is the price the sale was made at, an attribute of the sale. Technically you could give a person a discount so that the price of a sale wasn't at the price in the products table anyway, so you'd have to store the price with the transaction.

Clear as mud?

 

[slogie]

Clear as mud!!!

That seemed to have done it! I was close, but I guess I did not get something right. Thank you so much. I will finish working out the kinks and let you know if I have any more problems.

Appreciate the help.

 

[pbaldy]

No problem. I think you'll be happier in the long run with what Simon and I have been trying to guide you towards.

 

[slogie]

So, let me get this clear... All I have to do then is just keep the price in the itemlist and add a price field in the stock transaction, no changes to the relationship part?

 

[pbaldy]

Yes. I added the field to the itemlist table, modified the combo to include that field when it got the selections from that table, and added the code to copy the price over to the field in the transaction table when you select from the combo.

 

[slogie]

Ok, so that's where I get lost...the combo box. I can only get the combo box to look up the values in one table. How do I add the code? or whatever it was that you did?

 

[pbaldy]

You only want it to look up values in one table; in your case, the itemlist table. After adding the field to that table, I simply added the field to the combo you already had (and adjusted its column count and column width properties accordingly). You can add the code here:

http://www.baldyweb.com/FirstVBA.htm

 

[slogie]

I am clueless about codes...but I figured it had something to do with it. That was why I was stuck. So you had to write in the code manually?

 

[Simon_MT]

OK What is the Sales Tax Rates. Its getting late I will look at this in the morning.

Simon

 

[pbaldy]

Yes, I typed the code in manually. Most code is, other than what wizards might create.

Simon, I think technically it was already "in the morning" when you wrote that.

 

[slogie]

Ok so if I was to create another invoice set for credit invoices I could just copy that code into the other combo box?

 

[alexlds]

Hi Simon re post 18 "there IS no other way" - after struggling with this prob for weeks I have found another way.

Perhaps I should describe my prob first. I use a function with many parameters to produce a calculated value in a query field. The prob is that some of the paramaters needed for new records may change - or I may occasionally need to manually override / overwrite the calc results in the table for some of the records.

Its all very well saying keep all the parameters and recalc the result every time as required - but changing the parameters affects all records in the query. What I needed to do was to somehow record, in a table, what I call a "passive copy" of the calc result.

What I am now going to tell you will no doubt get experienced guys like you foaming at the mouth and tearing their hair out - or will prob get me banned from this site altogether(!!). What I did was DoCmd.TransferSpreadsheet - to export the query results to Excel and then immediately import them into an access table . A massive kludge - but hey it works !!

The moral of the story is When all else fails, dont force it - get a bigger hammer. haha !

 

[heinc1]

Hello!
I am also a novice and found this thread. I completely follow what you are suggesting, pbaldy, and want the price to be part of the sales record, but do not know how exactly to do it.
My sales database is quite simple. I have a tblPriceChart that lists 8 items and their corresponding prices. 1 is $50, 2 is $40, 3 is $35 and so on. When number 1 is placed in the "Type" field of my order detailsI would like the unbound Price field to populate with the corresponding price from the tblPriceChart. The Type field is a Lookup which drops down a three column display with the Type number,Price,Discription field.
I have tried entering the following code in On Change in the Type field:
Private Sub Type_Change()
Price =DLookup("Price","tblPriceChart","ID=" & Type)
End Sub
This works great when I use the dropdown arrow to select a Type. But when I just want to key in the Type number (because I have it memorized) it gives me a Run-time error '3075': Syntax error (missing operator in query expression 'ID='.
How do I get this to accept a directly keyed in value and still have it lookup the price from the tblePriceChart? What Event or Code should I use to make this happen?
By the way, it is not absolutely necessary that I use the Lookup on the Type field, I just thought I would try to make it more user friendly for my helpers.
Thank you for any advise you may have.

 

[pbaldy]

Try the after update event instead of the change event, which fires with every keystroke. This is probably more efficient:

http://www.baldyweb.com/Autofill.htm