Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    CustomerID not found (2000)

    Hi All,

    This is a continuation of sorts of the thread "Form Freeze". Pat has graciously been helping with a payment form that I'm having issues with. frmPayment is set to open from frmAR but clicking cmdApplyPayments. frmPayment should open the to the customerID in tblPayment that corresponds the AccountID in tblDemographics. I notice that the number identifying the customer never changes however the corresponding invoice data in lstInvoiceSelection does change. I've tried substituting CustomerID with AccountID on frmPayment but that doesn't change either.

    I'm concerned that my queries down the line will not produce the correct data if the correct CustomerID is not in place.

    I've attached the trimmed down database. BTW, frmARAccountName is used to open frmAR.

    Thanks,
    Leesha
    Attached Files Attached Files

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

    Re: CustomerID not found (2000)

    This is because you don't tell frmPayment to display records with the AccountID from frmAR, just like you used to open frmAR from frmARAccountName without specifying a Where condition - see <post#=268552>post 268552</post#>. You only pass AccountID as OpenArgument and use it to set this in a new record. Use the following instruction to open frmPayment:

    DoCmd.OpenForm "frmPayment", acNormal, , "AccountID=" & Me.TextAccountID, , , Me.TextAccountID

    PS I haven't been following the previous thread closely, so I don't know what the difference between AccountID and CustomerID is.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CustomerID not found (2000)

    Hi Hans,

    If I ever get this its going to be a bloody miracle. I tried the code you gave me but it didn't change anything. Honestly I'm not sure that I even need customerID anymore. I originally put it into tblPayment as a way to link back to AccountID which is the name of the account. Pat has been giving me a lot of help with this and he had me change things quite a bit to work better and more efficiently which it has.

    When I looked back to my relationships, PayID in tblPayments has a relationship with PayID in tblPaymentAllocations. tblPaymentAllocations has a relationship with tblBilling between invoice numbers. tblBilling has a relationship with AccountID in tblDemographics. So in theory I guess they all link up. I'm still building so I'm having a hard time visualizing the whole thing actually working. I guess I need someone to tell me whether I even need AccountID to open to CustomerID.

    One more question for you. I'm trying to get frmPayment to actually open to the invoice number chosen on frmAR (it makes sense to me although I think Pat is ready to pull out my hair!!! <img src=/S/bash.gif border=0 alt=bash width=35 height=39> He's been trying to help me with the code but I know is slamming with other stuff. Would I use code similar to what you have below.................................in other words to get frmPayment to open with InvoiceNoReference in the subform (frmPaymentAllocations) already chosen as the same invoice on frmAR.

    I know, you probably want to <img src=/S/bash.gif border=0 alt=bash width=35 height=39> me as well. Believe me, it makes sense to me!

    Thanks,
    Leesha

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

    Re: CustomerID not found (2000)

    Leesha,

    I am afraid that I don't understand what you are doing at all. Do you want frmPaymentAllocations to show only the invoice selected in lstInvoiceSelection on frmAR? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CustomerID not found (2000)

    Hans, you aren't confused at all. That is exactly what I want. To clarify, if should be the subform on frmPayment which is actually frmPaymentAllocations. Did I just confuse you again??

    Leesha

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

    Re: CustomerID not found (2000)

    I am extremely sorry. I still don't understand this at all. I am unable to see how it works, or is supposed to work. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CustomerID not found (2000)

    No need to apologize Hans. Poor Pat has been in the same boat trying to understand my intent.

    My goal is that the user has two options when deciding how to post allocations. Either they can go to a version of frmPayment (not created yet) and just apply payments in bulk by manually entering or selecting the invoice number to post to, OR they can get to it from frmAR. The purpose of frmAR is for the user to easily make corrections to invoices. IF after making adjustments, they wish to post allocations to THAT specific invoice they can do so by cmdApplyPayments which is on frmAR. My goal is to have have frmPayment open with the invoice number that corresponds to the one that is current on frmAR already chosen. There are two reasons for this. First is will decrease the chance that the user will select the wrong invoice number and second it will keep whining users from saying "why do I have to go to the extra step of having to select the invoice number when I already did that on frmAR". It's so clear in my head. I must be nuts!

    Leesha

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

    Re: CustomerID not found (2000)

    Leesha,

    I have attached what I have come up with. I have removed CustomerID from tblPayment. Instead, a payment is linked to an account via tblPaymentAllocation and tblBilling. I changed qryPayment to reflect this, and linked frmPaymentAllocation to frmPayment on InvoiceNo and PayID. There is no need to set the value of fields in the subform in code, that is done by the link. There is also no need to update lstInvoiceSelection in Form_Current, I think.

    I hope that you can use this (or part of it).
    Attached Files Attached Files

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CustomerID not found (2000)

    Hans!!! That is beautiful!!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Now, at the risk of you killing me, is it possible to have frmPayment to open to a new record and still do the same thing with the invoice??? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    I'm sooooooooooooo relieved!!

    Leesha

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

    Re: CustomerID not found (2000)

    A problem with doing this (taking the CustomerID or AccountID, I cannot remember which) out of tblPayments is that if a Payment is entered bu the allocations are not done there and then, you will get a floating payment in the system that does not relate to a Customer, then I believe you will have problems trying to reconcile.
    This was the major reason to put this field in tblPayments.

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

    Re: CustomerID not found (2000)

    I am sorry, I shouldn't have butted in, since you have been handling most of this. It seems to me, though, that this violates relational design, but I will leave it to you (I'm going to bed anyhow).

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

    Re: CustomerID not found (2000)

    No Hans, how can you be sorry, you have solved 1000's of problems. we can all learn from you. As for butting in, when you butt in (as you call it, I call it adding to the subject at hand) most people tend to learn things.

    BTW, why does it violate relational design?
    Sweet dreams Hans. Hear from you tomorrow, I'm firing, it's only 1.20pm here.

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

    Re: CustomerID not found (2000)

    Hi Pat,

    In the database as posted by Leesha, the CustomerID field in tblPayment is not related to any other table. This can lead to inconsistencies and in fact, there is a record in tblPayment with CustomerID=40, but there is no record in tblDemographics with AccountID=40.

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

    Re: CustomerID not found (2000)

    Hi Hans
    CustomerID in tblPayments should be related to AccountID in tblDemographics, it certainly was when I put my changes into the design.

Posting Permissions

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