I'm a bit of a beginner in the world of databases so am learning as I go.
I'm at a bit of an impass with my latest issue and wondered if anyone can help.
Here's the background:
I've got a very straight forward event management database: 1 table and a few Select queries. I have created a form for inputting data when I get an enquiry.
One of my fields is called [Tickets Needed] (a number: long integer) - which is essentially the number of tickets requested by a customer. I then have a field called [Invoice Total] - currently has no data. My idea was to generate an invoice total based on the number of tickets required.
So if I have a fixed price of £20 per ticket - I would have thought it would be easy to do a calculation to populate the [Invoice Total] field and display it in the form. I can then mailmerge an invoice in word to pick up the required fields.
Anyone know how I can achieve this?
Things I need to consider:
1) when would the [Invoice Total] field be updated? (e.g. as soon as the [Tickets Needed] field in the form has been entered?)
2) Is this a query function or something you build into the form
3) How can I make the returned [Invoice Total] a read only field - so it can't be amended in the form?
Any suggestion would be most welcome as I have all the right ideas and none of the experience to implement them!
1) yes its VB Code but don't worry to much about that
2) If you click on your textbox txtqty go to properties and choose the Event Tab,
find the line that says afterupdate and put in the code above(it should be fairly obvious once your there)
Make sure your textboxes are called the names that you use in the code, this can also be Changed through the properties
1) Is your suggestion VB code
2) Where should i put this code (literally) - never done this before so have no idea.
Look forward to hearing from you.
Hi Big John,
And let me say "Welcome to the Forum"
Nice to hear you're an Access newcomer. The above sample provided by tbmnwvl is a bit of VBA code and would be placed in a module behind the field called txtQty and would come into play as soon as that field is updated with a figure.
There are however 2 other choices....(always good to know more than one way to skin a cat without gettin scratched...lol)
(1) I don't use this one often but just a good is to be done in the Query itself. In one blank area (Field) of your query you would do let's say InvoiceTotal: That is an alias for the field you want to hold the totals. AFTER you do the colon : do this [TicketsNeeded]*[TicketPrice]. What you're saying is create a field named InvoiceTotal to hold the resulting calculation of the fields [TicketsNeeded] and [TicketPrice]. That help you?
(2) What I use most is in the form itself. With your Form in DesignView add on more TextBox. It is Unbound...meaning it isn't attached to anything in the Table. Name that TextBox InvoiceTotal. Inside that box place this calculation: =[TicketsNeeded]*[TicketPrice]. There ya go.
However, to make things a bit easier take a look at the sample database I posted here. Just a note, it is sometimes very helpful to make a scaled down version of your database, minus sensitive data, Zip it down and Post it here.
So take a look see and I'll check back later.
have a nice one,
1) "Let's say your text box control bound to the [Tickets Needed] field is named txtQty and the control bound to the [Invoice Total] field is named txtInv:
Private Sub txtQty_AfterUpdate()
txtInv = txtQty * 20
Set the Locked property of txtInv to True in design view. Then only the event procedure above can change the value of the bound field."
---> I did this and changed the field names as follows:
Private Sub tickets needed_AfterUpdate()
total invoice = tickets needed * 25
I then opened the properties on the [total invoice] form field and pasted this into the event tab in the After Update section.
I set the lock property in the data tab to yes.
I then got an error message - which I have taken a screen grab of and attached it as an image in PowerPoint. Any suggestions?
2) I opened the properties on the [total invoice] form field and typed the following in the control source in the data tab: =[tickets needed]*25
This works in terms of displaying what I need it to in the form, but I need to know how to capture this and populate it in the database (so I can mailmerge it to an invoice later).
3) I wasn't clear on Bud's no#1 query: "(1) I don't use this one often but just a good is to be done in the Query itself. In one blank area (Field) of your query you would do let's say InvoiceTotal: That is an alias for the field you want to hold the totals. AFTER you do the colon : do this [TicketsNeeded]*[TicketPrice]. What you're saying is create a field named InvoiceTotal to hold the resulting calculation of the fields [TicketsNeeded] and [TicketPrice]. That help you?"
I think I need this broken down further into bite sized, literal actions.
Sorry to be so high maintenance, but even doing this examples, I am learning so much. So thanks to you all for yoru help!
P.S. I have just tried to do an append query. I have 2 tables of data with identical field names and similar, though not the same, data. Both tables have an ID field that corresponds with one another i.e. the person with ID=1 in the first table is the same person in the other table. It's just one table is more up to date, but doesn't contain a column of data that the other has (though it does have the field name.) What I'd like to do is append my up to date table with all of the data from the [source] field where the IDs are the same. Any suggestions?
i can't open zip files but here are some things ive noticed
you cant have spaces in VBnames to reference these fully you can use
Forms!yourFormName![tickets needed] and likewise for [total Invoice]
run through the procedure i suggested for setting up your event procedure and VBA will set up your sub for you all you have to do then is add your calculation,
also are you need to refereence the controlnames rather than the query names as i think you may have done
2) I wouldnt bother doing this as you would need to code the information out rather than let access do it itself via the control source
3) open the query you are using in design view
on the area where you have your selected field names go to a blank one
In this type TotalInvoice:[TicketsNeeded]*[TicketPrice]
This will put a the calculation in your query
When referencing in a form then when a value changes put the following line in the controls you change after update events
in your p.s. section what you need is an update query rather than a append query
append queries add rows to your table but update queries change data within a table
What I was stating in my reply above was simply in #1, you can do the calculations in your Query. But MYSELF, I like to do it in the form. Did you take a look at my example I posted? What that does is let you Select the Item, the Price is already in the system, all you do is Input the Qty. and it calculates it on the fly. If you set it in code to be:
Private Sub txtQty_AfterUpdate()
txtInv = txtQty * 20
What that means is that the txtInv will ALWAYS sum up the txtQty *20. What if you only have a qty. of 5 for [TicketsNeeded]? You need it to be flexible whereas you set the quantity of [TicketsNeeded] The form I have in the sample database attached above shows it in very easy detail.