'Discount' option as both £ and %

[AndyCabbages]

[FONT=&quot]I am working on a database which requires an invoicing system. Sometimes a discount is including in the invoice and ideally this discount should be entered as either a % value or a £ value.

I have given this quite a bit of thought and haven’t really managed to come up with a practical solution. A few things are troubling me such as:

Would I need 2 different fields?
How would I define which field the value is entered into?
How would I get it to convert a £ into % and visa versa?
Would the above be necessary?

I am open to any ideas as to how I could do this, to me it does not really matter much how I do it as long as it is possible. Any sample solutions would be great as well if possible

Andy[/FONT]

 

[vbaInet]

Suggestion:

1 text box
2 Option buttons in a frame - 1 for percentage, the other for pounds.

You wouldn't save the value.

 

[AndyCabbages]

How would that work with getting the value entered in the text box? Please talk me through your though the process you are suggesting

Ultimately the discount would have to be converted into a % in the calculations since the total would be worked out using sub total and dicount (%).

 

[vbaInet]

The controls I've suggested would be unbound controls. I'm guessing you have a subform and from that subform you've already calculated the subtotal?

 

[Simon_MT]

There are presentational issues involved as in some cases the price is an agreed price and enters a fixed percentage. Generally, the price is agreed with the Client contractual price so I prefer to hold the amount and calculate the percentage.

I also store a Discount Flag and if it is unticked then instead of discount the invoice will state "At agreed price."

Simon

 

[AndyCabbages]

vbaInet, I have just realised it is you helping me out again

Yes, the subtotal is calculated automatically already based on the data selected in one of the subforms. A disccount then needs to be entered and a Total calculated from the subtotal and discount. The next stage after the discount is entered is to print out an invoice report which shows the subtotal, discount (%), and total.

 

[vbaInet]

Haha!! Guilty as charged AndyCabbages lol.

So have you got your controls on the form? What did you name the text box, and the frame?

Do you know how to read off the value from your subform's subtotal to your main form?

 

[Simon_MT]

I would start at the subForm level for your invoice and create a Header and Footer Section for the Invoice No to put the billing information etc. That way you get at the discount and then total the values in the Invoice Footer.

Simon

 

[AndyCabbages]

Currently the subform allows you to choose different items and add them to a list. When an item is added to the list the total price is updated and displayed on the form as 'SubTotal' (Subtotal is never recorded as a value, just as a calculation based on the items added to the list). A discount (which is stored as a value in a table) is entered and then a 'Total' is calculated.

Subtotal is never recorded in a table and neither is Total, at present the only value ever recorded in a table is 'Discount'.

This seems like a bit of a strange method to me, but I think it is down to the company printing off every single invoice and then sending it off to the accounts department to enter into a different database entirely.

 

[vbaInet]

So what is this extra discount you're applying or is it the same field?

 

[AndyCabbages]

It is not so much an extra discount, just an option to choose between wether the discount is recognised as a % or as a £ value.

I have been looking at the calculations and the way the database works and it seems like it is asking too much to have an option to choose between the 2, since pretty much all the calculations are done internally without values actually being stored it would mean way too many different calculations being edited.

 

[vbaInet]

I think it's just for visuals, nothing else. I'm just working something out.

 

[vbaInet]

Almost forgot about this. I'm not sure what version of Access you use? I've attached both 2003 and 2007 versions.

The tables haven't been setup in a logical manner but the forms give you the gist of how to go about it.

 

[Simon_MT]

Going a little further the you should be able to give the Accounts department to process inport the invoices into their Accounting system.

I have done it for Sage it was just a matter of working the format of the csv file.

Simon

 

[AndyCabbages]

vbaInet, thanks for the example, that looks like something that would fit the needs of the system. The only problem I had was that the subtotal and total values were not appearing for some reason so I couldnt get a complete idea of exactly what was happening

 

[vbaInet]

It wasn't appearing on my example or on your db?

 

[AndyCabbages]

It wasnt appearing on your example, but now it is working.

The database I am working on is 2000, is the example you gave me achieveable in this version? I have been running yours in Access 2003 and the db I am working on in Access 2000 side by side and I cant seem to get the same effect. I am mostly having trouble with linking the 2 clickable boxes so that when one is clicked the other is unclicked

 

[vbaInet]

Yes it's achieveable.

What you've done is dropped two option button controls directly on your form, but there's a control that will group two of them and will only allow you to select one. Notice that frame around my option buttons?

In your toolbox, look for the Option Group control, drop that on your form and follow the prompts.

 

[AndyCabbages]

Found it and got the 2 different option boxes linked

Working on implimenting your code for total into my database now

 

[vbaInet]

Grand. Just post back on progress.