Page 1 of 4 123 ... LastLast
Results 1 to 15 of 56
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Form Freeze (2000)

    I'm posting the database I've been struggling with. frmARAccountName is used to open frmAR. Once on frmAR the user enters the date range of the invoices they wish to work with for the AccountID the form is opened to. All of this is working fine. The issue is that the form freezes not allowing any data entry. I've also noticed that various command buttons like the exit one need three clicks before it will close the form. The frm is opened in edit mode, however its as if the record that is seen is not current/active for the lack of a proper description. I've spent an ton of time redoing the underlying queries associated with the form and the list on the form and that hasn't helped. I'm not sure what to do next.

    Thanks,
    Leesha
    Attached Files Attached Files

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Form Freeze (2000)

    Leesha,

    For starters, without getting into great details, go into Visual Basic mode and try to compile your database. You have errors in the code for From AR such as referencing objects that don't exist, an End If clause with out an If, etc. that may be causing some problems / freezing.

    After correcting the code errors, then re-try using the database and see if it works.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Form Freeze (2000)

    Hi Gary,

    This form has been reworked so many times I forgot there were things in the code that were no longer needed. I went through and took them out, didn't find any If/EndIF issues but I may have deleted them. Checked twice. Thanks for the suggestion and I apologize for the inconvenience to you. However, the from is still frozen.

    I've attached the corrected form.

    Leesha
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Freeze (2000)

    Leesha

    Gary is correct, there are certain items which are not referenced.
    If you add "Option Explicit" underneath "Option Compare Database" and then compile, you will see where the errors are.

    If a variable is not declared, it's value is set to 0(zero) so this little tip will help you in the future.
    Basically if you imagine your code as a circle, each time there is a problem within the code, it puts a break in the circle.
    So by referencing your controls and variables correctly, you will eradicate any faults enabling the code to run smoothly.

    I for one cannot write code as such, but can appreciate the fundimentals.
    Later in your database production, you may find errors which could be difficult to remedy, it's a bit like one step forward and two steps back, if you get my meaning.

    A couple of examples from your code:

    SalesTax= [LaborCost] * 0.06
    really ought to be
    Me.[SalesTax]=Me.[LaborCost] * 0.06

    You're telling the code that [SalesTax]&[LaborCost] are a controls on the form it resides.
    Explicitly defined.
    Your code later on defines the Me. as belonging from the form.

    Hope this clears a little up for you.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Freeze (2000)

    I've just noticed on your fields [StartDate],[EndDate], you have a setfocus on the On_Exit event.
    When you try to close the form, you are actually exiting the field and setting focus on the next one, this happens until the generate invoice button loses focus, then lets you close the form.

    Have you tried after update or something else.

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

    Re: Form Freeze (2000)

    Hi Dave,

    I appreciate the info and will go back and do as you suggest.

    I've been working at this for hours and the freeze seems related to the qryAR that frmAR has as a data source. If I remove tblPayment and tblPaymentAllocations. When I put tblPayment back joined between InvoiceNo and CustomerID the table is not frozen. this is the SQL for that:

    SELECT tblBilling.AccountID, tblDemographics.TaxID, tblDemographics.TaxExemptNumber, tblDemographics.TaxExemptStatus, tblDemographics.[LastName/BusinessName], tblDemographics.ActivationDate, tblBilling.DateWorked, tblDemographics.AlternateBillingSalutation, tblDemographics.AlternateBillingFirstName, tblDemographics.AlternateBillingMiddleInitial, tblDemographics.AlternateBillingLastName, tblDemographics.AlternateBillingSuffix, tblDemographics.AlternateBillingStreet, tblDemographics.[AlternateBillingApt/Suite], tblDemographics.AlternateBillingCity, tblDemographics.AlternateBillingState, tblDemographics.AlternateBillingZipcode, tblBilling.HoursWorked, tblBilling.LaborCharge, tblBilling.LaborCost, tblBilling.Discount, tblBilling.PreTaxTotal, tblBilling.SalesTax, tblBilling.InvoiceTotal, tblBilling.InvoiceComments, tblBilling.SupplyCost, tblBilling.SupplyDescription, tblBilling.JobDescription, tblBilling.JobNotes, tblDemographics.InactiveDate, tblDemographics.ReactivateDate, tblDemographics.ProspectDate, tblDemographics.Active, [salutation] & " " & [firstname] & " " & [middleinitial] & " " & [lastname/businessname] & " " & [suffix] AS Expr1, tblDemographics.Street, tblDemographics.[Apt/Suite], tblDemographics.City, tblDemographics.State, tblDemographics.Zipcode, tblBilling.DiscountComments, tblBilling.InvoiceNo, tblBilling.DateBilled, tblBilling.InvoiceType, tblPayment.DatePaid, tblPayment.TypeOfPayment, tblPayment.CheckNumber, tblPayment.CardNumber, tblPayment.CardType, tblPayment.CardExpirationDate, tblPayment.PaymentComments, tblPayment.PaymentAmount
    FROM tblDemographics RIGHT JOIN (tblBilling LEFT JOIN tblPayment ON tblBilling.InvoiceNo = tblPayment.CustomerID) ON tblDemographics.AccountID = tblBilling.AccountID
    ORDER BY tblDemographics.[LastName/BusinessName];

    The freeze issue comes back into place when I add tblPaymentAllocations. No matter what the combination, it freezes or doesn't allow updates to the balance field. I haven't a clue!

    Leesha

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

    Re: Form Freeze (2000)

    >>Have you tried after update or something else.

    Nope but will gladly do so!!! I may never get this GD form to unfreeze but I'll learn a lot in the meantime!

    Thanks, I appreciate it!

    Leesha

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Freeze (2000)

    I just briefly looked at the db...u have in the qry, from tblDemographics a join from ACCOUNTID (which is an autonumber (record number) field) to CUSTOMERID (which is an autonumber(record number) for the customers. This join will not only produce incorrect results because you set the join type on tblAllocations to 2, but will not allow the recordset to be updated (which is noted in the status bar on the bottom left of the screen)

    The reason your results will be incorrect ... you are joining ID to ID...therefore where 1 =1 and 2=2 there is a match...but that isn't the results you are looking for .... what you are looking for is a match on CUSTOMERS...which as best as I can tell you don't have a field for in tblDemographics...therefore no way to make a join.

    Simpler said...the ID for tblDemographics is about the Demographics....the ID for tblPayment is about Payment Types. Neither of which are about Customers which is what you are attempting to make a join on.


    Hope this was clear and of some help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Form Freeze (2000)

    Hi Roberta,

    I joined AccoutID in tblBilling to AccountID in TblDemographics. You are correct that AccountID in tblDemographics is an automumer. I did it this way since each account would only be in the tblDemographics once, so in fact the autonumber corresponds to the Account or customer as you described them. The same is the situation with tblPayment - customerID (not called AccountID just so I wouldn't get confused which table is was coming from) joins back to the autonumber in tblDemographics. I take it that isn't the way I should do it??

    Leesha

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

    Re: Form Freeze (2000)

    At this stage I have just looked at the tables and I have the following questions about tblPaymentAllocations:

    1. The field Balance, what is it? It should be AmountAllocated. eg. an Invoice of 300 may have 200 allocated from a Payment of 250, that invoice would now have a balance of 300-200=100. The other 50 could be allocated to another invoice for 100, this invoice of 100 would have a balance of 100-50=50. You can work out the balance of an invoice by summing the paymentallocations against it and subtracting this from the invoice amount.

    2. Field PaidinFull. This should go into tblBilling, if you really need it. Again it is paid in full when the balance (invoice amount - paymentallocations) is zero.

    3. Field PartialPayment. Why is this required?

    4. Field CreditDue. What is this?

    5. Field BouncedCheck. This should probably go in tblPayments.

    I am about to take one of my girls to tennis, so I will be back in a few hours. I'm also trying to find another house to live in.

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

    Re: Form Freeze (2000)

    Hi Pat! Thanks so much for taking a look for me. Here are the answers to your questions:

    1. The field Balance, what is it?

    It is in fact the amt remaining on the bill after a payment has been made. I'm having a hard time visualizing how you would set up the tblPaymentAllocations as in how does tblAllocations "know" the amount that has been allocated to it?? I can't "see it" as in how it works or I should say obtains the data.


    >>3. Field PartialPayment. Why is this required?

    >>4. Field CreditDue. What is this?

    Don't laugh, but these were from my more primative thoughts (pre Pat suggestions) as to how to query or find bills that were outstanding. I was going to use them in queries. Stop laughing!!


    >>I am about to take one of my girls to tennis, so I will be back in a few hours. I'm also trying to find another house to live in.

    Yep, gotta do the mom thing here too! Thank God I don't have to find a new house!

    Can't wait to get a better picture as to what I'm supposed to do! I'll be so excited when this works.

    Leesha

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

    Re: Form Freeze (2000)

    I don't know how your form currently works, but the tblPaymentAllocations is there to apply amounts to different invoices.

    Say you had 2 invoices in the system for the same customer for $300 (invoice#1) and $100 (invoice#2).
    Say a payment for $250 was to be paid against the $400 worth of invoices.
    You may allocate $200 against invoice#1 ($300) which would leave a balance $100, and allocate $50 against invoice#2 which leaves a balance of $50.

    the next time you get a payment and these are the 2 invoices in the system for that customer, they should come up in the window as:
    Invoice#1 Amt os $100
    Invoice#2 Amt os $50

    Hope this helps

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

    Re: Form Freeze (2000)

    Hi Pat,

    I understand the concept of allocations. We have to do it all the time at work. The piece I'm not getting is whether the allocations are made as separate table entries or are they applied in code. For example, on tblAR what my goal is is to be able to open the frmAR to the AccountID and have the invoices that apply to that account open up. The form does this. This is where I get stuck. I need there to be a payment history of dates and amts that are allocated to each invoice - thus your idea of a tblPaymentAllocations. Where I'm getting lost is how to do that on tblAR and have the data go into tblPaymentAllocations. Is this even possible or do I need a separate form??? I'll warn ya, if you mention a subform you're gonna have to point me in the direction of how to do the code so that the data gets to the right spot!! I tend to shy away from subforms due to never having had any luck with them.

    BTW, couldn't get your email.

    Leesha

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

    Re: Form Freeze (2000)

    I think I've got it!!!! Been working for the past 2-3 hours and created frmPaymentAllocations which opens from frmAR to the AccountID and InvoiceNo that are active in the frmAR. I then am able to enter the allocated payment amount and the date payment was made. I'm still working on it and will probably run into glitches (so I'll be back!) but I think I've got it!

    Leesha

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

    Re: Form Freeze (2000)

    When you have it, would you post your DB and I'll have a look at it.

Page 1 of 4 123 ... 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
  •