Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Subform Totals (Access 2003 SP2)

    I have a subform based upon a table tbl A as described in my previous post. tbl Ref is used to show fields via =DLookup(... There are 2 fields that are shown from tbl A, they are REFid that is used to lookup the tbl Ref table. Another field AmountSpent is shown from tbl A. Some problems are:

    1. The tbl A.REFid column is blank where it should be showing a field that is the 1st in the combo boxes SQL.

    2. The total (which is Sum(AmountSpent)) is showing #error which forces another total to show #error.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Totals (Access 2003 SP2)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform Totals (Access 2003 SP2)

    Here is a cutdown version.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Totals (Access 2003 SP2)

    1) What is this question in the context of the database you attached? (You changed all the table and field names, so I don't know what your question refers to)

    2) You cannot use =Sum(ControlName), only =Sum(expression). Change the Control Source of Text21 to

    =Sum([Splurged])*Forms![frm Hans]!YenRate

    or

    =[Text20]*Forms![frm Hans]!YenRate

    or

    =[Text20]*Parent!YenRate

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform Totals (Access 2003 SP2)

    Sorry about that, too busy trying to conceal the data and field names. I'll change the question based upon the current field names.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    I have a subform based upon a table Trans as described in my previous post. Table Fore is used to show fields via =DLookup(... There are 2 fields that are shown from table Trans, they are Hansid that is used to lookup the Fore table. Another field Splurged is shown from table Trans. Some problems are:

    1. The Trans.hansid column is blank where it should be showing a field that is the 1st in the combo boxes SQL.

    2. The total (which is Sum(Splurged)) is showing #error which forces another total to show #error.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Why does text20 show a #error ?
    text21 is still showing a #error.


    Ignore the questions in bold, my mistake.

    But why if the PK field showing blank when I believe it should not.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform Totals (Access 2003 SP2)

    Why does text20 show a #error ?
    text21 is still showing a #error.

    Ignore the questions in bold, my mistake.

    But why if the FK field showing blank when this is the link to the Fore table. In fact if I click in this field it shows what the field is meant to but if there is more than one record in this subform then the other record's FK field shows as blank.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Totals (Access 2003 SP2)

    I would add the Fore table to qry Trans, with a left join from Trans to Fore.
    That enables you to display the fields from Fore in the subform without resorting to DLookups and code.
    Since the subform is continuous, the combo box bound to HansID is the same in all records. You must place a text box bound to the P field (the field displayed in the combo box) on top of the combo box to display the value in records other than the current record.

    See attached version.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform Totals (Access 2003 SP2)

    This was what I had done previously but you talked me out of it in <post#=550610>post 550610</post#>. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform Totals (Access 2003 SP2)

    Well that was certainly a huge improvement, the only problem now is that when scrolling through the records I get the PK being blank sometimes.
    1st main record, there are 2 subform records with both FKs being shown.
    2nd main record, only 1 subform record the FK is not shown.
    3rd main record, 3 subform records where the 1st was blank.

    I don't know what the problem is.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform Totals (Access 2003 SP2)

    Another problem I have is that I can change values in the Fore table in this subform where I don't want to.

    How can I overcome this problem?

    I guess I could put these 2 values (comboboxes for Z and S) in the Trans table, so it would not matter if they then got changed.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Totals (Access 2003 SP2)

    Sorry, I was replying to this when my internet connection went down, and since it didn't come back quickly and it was already after midnight here, I went to bed.

    I had forgotten about the cascading combo boxes. I don't think there is a satisfying solution since you are using a continuous subform. There is actually only one set of controls on a continuous form. Unbound controls will display the same value in every record. So you'll either have to accept that the Z and S combo boxes display the values for the current record in all records, or you'll have to take them out of the detail section. Neither is ideal. It would be fine in a single record subform.

    The attached version uses unbound combo boxes in the detail section, so they'll always display the values for the current record.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform Totals (Access 2003 SP2)

    You obviously have to sleep sometime, although it seems like you don't. How fast can you type Hans, is it about 3,000 words a minute.

    Seriously though, I have solved this problem by including the 2 combobox fields as 2 text fields in the Trans table. The combo boxes SELECT statements still look for distinct values in the Fore table and these values are saved in the Trans table. There's the added bonus that I don't have to accept the Z and S fields being duplicated for all records in the subform, they show the correct values.

    These fields in Trans are only used for display purposes and it works really well, and fast too, since the subform had 4 other DLookup fields to display as well. Now, because the tables are joined and indexed on the appropriate fields it is super fast in comparision.

Posting Permissions

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