Results 1 to 11 of 11

Thread: Query/Form Prob

  1. #1
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22

    Unanswered: Query/Form Prob

    Hi All,

    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!

    All the best.

    John

  2. #2
    Join Date
    Nov 2004
    Posts
    4
    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
    End Sub

    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.

  3. #3
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    Thanks for your response.

    Can I clarify a few things:

    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.

    John

  4. #4
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    Or anyone else? Feel free to advise me ;-)

    John

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi John

    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


    Dave

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by Big John
    Thanks for your response.

    Can I clarify a few things:

    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.

    John
    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,
    BUD
    Attached Files Attached Files

  7. #7
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    Thanks for the help.

    Tried these suggestions - see results below:

    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
    End Sub

    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
    End Sub

    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!

    John

    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?

  8. #8
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22

    Doh!

    Sorry - forgot the attachment!

    John
    Attached Files Attached Files

  9. #9
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22

    Doh!

    Sorry - forgot the attachment!

    John

  10. #10
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    ok
    i can't open zip files but here are some things ive noticed
    1)
    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
    yourcontrolname.requery

    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

    Dave

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Big John
    Sorry - forgot the attachment!

    John
    Hi Big John,

    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
    End Sub

    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.

    Give it a whirl....
    BUD
    Last edited by Bud; 11-21-04 at 07:00.

Posting Permissions

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