Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    28

    Unanswered: Retrieve SQL value into variable

    Hello again! Making progress... I'm starting to feel a bit bad about taking and not giving here. Is there a Lotus Notes forum? I could contribute there, but nowhere else...

    The db I'm working with is very heirarchical. Its actually a helpdesk database for the IT service desk, if anybody knows ITIL they'll knwo what I'm on about. A Service Agreement is "with" an Organisation, and "contains" various Products, each of which has a Price. My first box shows dates, and selecting one brings up all Service Agreements which expire on that date in the second box. Clicking a row in the second box brings all the products for that Service Agreement into another box. Or rather brings certain fields of those entities into a listbox. I don't have an actual handle on the SQL entities, I couldn't figure that out!

    What I want is one more step - when clicking an entry in the final box, a text box should display the total price of all the Products in the final listbox. I can get this with an SQL statement, but how do I put the results of an SQL statement into a textbox? Until now, I've only been working with Listboxes, which accept SQL statements in their RowSource property.

    Any general advice is of course welcome, thats why I decided to explain a bit more background! Thanks a lot peeps

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends how you implement this but there are few options

    One is to add to the unbound text box the cost each time you add something to the list box, rmembering of course to subtract any that are removed from the listbox

    Another is when you build your list box add the cost of that to the listbox as a hidden column, and sum the values in that column of the list box - its not really needed in this example, but it does have the ability to act as lookup table rather than have to run a series of expensive domain aggregate (DLOOKUP) functions.

    HTH

  3. #3
    Join Date
    Dec 2005
    Posts
    28
    That sounds like enough to get me started, thanks. Just in case anybody's interested, I worked out how to get a proper handle on the records:

    Code:
    dim rst1 as new adodb.recordset
    rst1.Open "Select organization.name, serviceagreement.startdate, serviceagreement.enddate from serviceagreement inner join party on party.partyid = serviceagreement.partyid inner join organization on organization.organizationid = party.organizationid Where EndDate = '" & DateList.Column(0) & "'", Application.CurrentProject.Connection, adOpenKeyset
    Then you can use the Update and AddNew methods of a recordset to alter the records on the autonomous data source! Not actually trie this yet, but it looks simple enough. I'm starting to hate MS Access a lot less now!

  4. #4
    Join Date
    Dec 2005
    Posts
    28
    Quote Originally Posted by healdem
    Another is when you build your list box add the cost of that to the listbox as a hidden column, and sum the values in that column of the list box - its not really needed in this example, but it does have the ability to act as lookup table rather than have to run a series of expensive domain aggregate (DLOOKUP) functions.
    OK, I was wrong. Thats not enough to get me started!! How do I do this? I've now got the price as a column in this listbox (the listbox is now done properly, by creating a recordest and attaching it to the listbox if that helps) but I don't know how to total it.

    I don't know if its just me, but the structure of the helpfiles in Access / VB / ADO seems to be so complicated. The Lotus Notes ones are so beautiful, so logical - they read like the Java API, pure syntactical consistency. The VB ones all seem to be conceptual prose!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a list box as a recordsource
    you can iterate through the recordsource summing the values of a specified column.

    how you do that is up to you
    but you know the number of items in the listbox (.listcount)
    so you could do soemthing similar to
    TotalPrice=0
    with <mylistbox>
    for iLoop = 0 to .listcount
    TotalPrice=TotalPrice + .recordsource.column(x)
    next iLoop
    end with
    <mycostcontrol>=format(TotalPrice,"#,##0.00p")

    you may be able to use the itemsselected collection
    not sure its recordsource or row source - its something like that. it may be a collection, if you investigate the properties of a list box you can find out what it should be

    if you go down this route you need to decide how often and when you update your cost box.

    BTW I mentioned that as an example, my preferred route would have been option 1 ie add / subtract as required

  6. #6
    Join Date
    Dec 2005
    Posts
    28
    It seems strane to me there's no totalling method built in. But what do I know?

    I can build it like this, adding up based on listcount and column values. It just seemed like messy code, but I guess its the only option!

    Thanks mate!

Posting Permissions

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