Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Subform Help (2002 SP-2)

    I currently have a query (qfltOrdersBilled) that shows all orders in billed status (txtOrderStatus). In my "funky" old way of doing things, when a payment was made, I would open the continuous form based on qfltOrdersBilled, sort by that customer (txtCustomerNo), and then change the order status to "paid". I would then make the entry in my accounting software (Quickbooks). I said it was funky.

    I tried to create a form based on a new table (tblPayments) and a subform based on qfltOrdersBilled and linked by txtCustNo, but don't get any records in the subform. I figure it probably has something to do with relationships between the table and query because the link just shows an arrow from qfltOrdersBilled to tblPayments (not a "1" and a "infinity" symbol). I haven't made a subform before, but it seems to me that what I want to have is the customer name, check number, payment date, and a "batch" number in the main form; and the invoice number, amount, and blnPaid in the subform. On close, the form would update all "billed" orders for that customer (bln = yes) to "paid" status. The "batch" number (auto-number) would be used to export the data to Quickbooks. I don't get what I'm doing wrong in the relationship that the subform isn't displaying any orders, or even if that's where the problem is.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Subform Help (2002 SP-2)

    I'm not sure that a form based on payments with a subform based on orders is a good idea. It would imply that many orders can belong to one payment.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Subform Help (2002 SP-2)

    "...many orders can belong to one payment"

    Isn't that what I want? One customer sends payment for 23 invoices? I don't follow.
    (Knowing even less about accounting than I do about subforms probably explains why I don't follow)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Subform Help (2002 SP-2)

    I'm sure I know less about accounting than you do. Can you provide some more information about the tables and queries involved and their relationships?

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Subform Help (2002 SP-2)

    Hans:

    I hope the attached mess is at least a starting point. I'm certainly not opposed to starting over from the beginning. Thanks for your consideration.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Subform Help (2002 SP-2)

    The main form is based on tblPayments and the subform on qfltOrdersBilled. The query is also based (among others) on tblPayments. I don't think tblPayments belongs in the record source of the subform. Removing it from the query should also solve the "ambiguous outer joins" error.

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Subform Help (2002 SP-2)

    Hans:

    Removed tblPayments from the query; got the subform now, but with weird results (attached). I'm not sure I follow "I don't think tblPayments belongs in the record source of the subform", what should it be? tblPayments is where all the sources for the form entries are. Thanks.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Subform Help (2002 SP-2)

    If tblPayments contains data that should go into the subform, I don't understand why the main form is based on tblPayments.

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Subform Help (2002 SP-2)

    Geesh Hans, I'm sorry, I'm confusing myself (again). The subform is based on qfltOrdersBilled and the main form is based on tblPayments. Still, the subform results are certainly wrong. What a mess!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Subform Help (2002 SP-2)

    Perhaps you could post a stripped down copy of the database (see <post#=378681>post 378681</post#> for instructions). Otherwise, it might be better if somebody who is familiar with invoices etc. tries to help. It is unknown territory for me.

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Subform Help (2002 SP-2)

    Thanks for your help thus far Hans. The phone is going to start ringing any moment, so I'll work on the db as I am able. Thanks again!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Subform Help (2002 SP-2)

    Well, let's see how much trouble I can stir up with this ripped, chopped, beaten, and reduced db. I had to remove all the calculations from the query and just have the amount appear as an "entry", rather than the complex calulations contained in that query. I retained only relavent info. Thanks.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Subform Help (2002 SP-2)

    The only record in tblPayments has a value of "20" in the txtCustNo field.
    The records in qfltOrdersBILLED have values such as "SIMAG" and "ECOC" in the txtCustNo field. The value "20" does not occur.
    Since there are no matching records, the subform remains empty.

  14. #14
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Subform Help (2002 SP-2)

    I removed tblPayments and made the source tblMaster, but it's still screwed up. I think this idea is going to the back burner. Thanks for your input!
    <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Subform Help (2002 SP-2)

    The combo box on the main form is not correct. It's bound column is the number field idsCustTrackingNo, but its control source is the text field txtCustNo. Probably due to this confusion, the txtCustNo field`in tblMaster no contains a mixture of number and text values. Changing the properties of the combo box helps: I changed the row source, reduced the number of columns, changed the column widths, and adapted the control source of the text box next to it.
    The first record still doesn't show subrecords, since 20 is not a good txtCustNo, but the second does.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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