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

    Query Question (2000)

    Hi All,

    My quest for knowledge is never ending!! Today's question.......................on frmAR I have a list called lstInvoiceSelection which thanks to Han's now opens to only show the invoices for the AccountID frmAR opens to. I've further limited lstInvoiceSelection to only choose the dates the user enters into two unbound text boxes called StartDate and EndDate. This is working fine. Now I'd like to give the user the ability to view the invoices either as all invoices or to filter them as unpaid invoices. There is a yes/no field on frmAR called PaidInFull. This field is also in the query that lstInvoiceSelection is bound to. I'd like to give the user the option of choosing whether the records are PaidInFull or not. Presently there is a control called cmdSortInvoices which simply requeries lstInvoiceSelection. My question, what would be the best way to do this???

    Thanks,
    Leesha

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

    Re: Query Question (2000)

    Put an unbound check box on frmARAccountName.
    Set the caption of its label to "All Invoices".
    If you want the default to be all invoices, set its Default Value property to True; if you want to show only unpaid invoices by default, set its Default Value property to False.
    Name the check box chkAll.

    Now open the query that acts as row source of lstInvoiceSelection in design view.
    Enter the following criteria for the PaidInFull field:

    False Or [Forms]![frmARAccountName]![chkAll]

    If the check box is "on", the condition evaluates to False Or True, i.e. all values are allowed, so all invoices will be returned.
    If the check box id "off", the condition evaluates to False Or False, which is the same as False, so only unpaid invoices will be returned.

    You can requery the list box in the On Click or After Update event of the check box.

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

    Re: Query Question (2000)

    Thanks Hans! I was thinking I would need to go that route but I've never really worked with the check boxes etc. and the few times I've tried..........................well you just don't want to know!!!! I'll give that a whirl and am confident I can get it to go due to your detailed directions. I do believe I'm "starting" to get this!

    Have a grand day,
    Leesha

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

    Re: Query Question (2000)

    Hi Hans,

    Finally had a chance to try your suggestion. All seemed to go well till I tried the form and now the textboxes are frozen and don't allow me to update them. I took the checkbox off to see if that eliminated the problem and it didn't. I can't imagine how what's happened.

    Leesha

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

    Re: Query Question (2000)

    Leesha,

    I see now that I confused the two forms earlier on. The check box chkAll shoould be on frmAR, not on frmARAccountName, and therefore the condition on PaidInFull should refer to frmAR instead of frmARAccountName.

    If your form is not updateable, perhaps you modified the record source of the form instead of the row source of the list box.

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

    Re: Query Question (2000)

    Hi Hans,

    I was hoping you were around. The teacher in you should be proud to note that I spotted the incorrect form name on my own and made the change. Also, it was the row source of the list box that I updated not the record source of the form. I am learning!

    While I was waiting to hear back I was trying to think of any changes I'd made to the form since I last tried the list box with your new code. The only changes I'd made was to the qry that the frm itself is actually bound to. When I eliminated those changes, the it freed up the form for updating. I'm not sure what the issue is. Here is the SQL for the form:

    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.DatePaid, tblBilling.AmtPaid, tblBilling.Balance, tblBilling.PaidInFull, tblBilling.PartialPayment, tblBilling.Rebill, tblBilling.CreditDue, tblBilling.PaymentSource, 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.CheckNumber, tblBilling.ARGeneralComments
    FROM tblRebill RIGHT JOIN (tblCreditDue RIGHT JOIN (tblBouncedCheck RIGHT JOIN (tblDemographics RIGHT JOIN tblBilling ON tblDemographics.AccountID = tblBilling.AccountID) ON tblBouncedCheck.BouncedCheckCost = tblBilling.BouncedCheckCharge) ON tblCreditDue.CreditDueAmt = tblBilling.CreditDue) ON tblRebill.RebillDueAmt = tblBilling.RebillAmt
    ORDER BY tblDemographics.[LastName/BusinessName];

    Leesha

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

    Re: Query Question (2000)

    I don't understand why you have tblRebill, tblCreditDue and tblBouncedCheck in this query, since you only use fields from tblDemographics and tblBilling.

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

    Re: Query Question (2000)

    Hans,

    The inability to update the record is intermittent. It's really weird. Also, the list box only requeries to remove the paid invoices. When I uncheck the option box (I liked the look better than a check box) the requery doesn't return all records. Do you want me to send you the database?

    Leesha

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

    Re: Query Question (2000)

    I "thought" it would be better to store the rebill, bounced check, and credit data in separate tables. frmAR will eventually have tabs for each of these sections and they will populate based on the result if an invoice is not paid in full, a check is bounced etc. I tried having all the items in tblbilling but I couldn't find a way to apply partial payments to an original invoice and maintain a billing history. If there is a better way to do this I am certainly open to it!

    Leesha

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

    Re: Query Question (2000)

    I have attached a zipped Access 97 version of what you posted yesterday, with the radio button.

    Note: the standard use of option buttons in Windows applications is to select one of a number of mutually exclusive options. For something that can simply be on or off, a check box is used. You, as designer, are free to ignore this, but usually it is better to adhere to Windows standards.
    Attached Files Attached Files

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

    Re: Query Question (2000)

    Hi Hans,

    As always I appreciate your guidance and of course will deferto windows standards vs appearance!! I've attached the database with frmARAccountName used to open frmAR so you can see my problem. I replaced the option button with a checkbox and that seemed to correct the list updating issues (although its backwards but I think I can fix that). I'm still having issues with the form freezing on me. The frm has changed since the one I sent to you and is still a work in progress. I'm not sure if its the underlying qryAR that the form is bound to that is messing it up or not.

    Leesha
    Attached Files Attached Files

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

    Re: Query Question (2000)

    <img src=/S/bash.gif border=0 alt=bash width=35 height=39> OK, you're gonna shoot me!!! After posting the trimmed down database to you I remembered that when I when I deleted the option box for a check box, as you suggested, I neglected to change the parameter name from optAll back to chkAll. I just did that and the form seems to no longer be locking up. I'll know better the more I use it - to see if it continues to be intermittent or not and/or related to the qryAR that the form is attached to. I know, I know, go slower and don't work so long! But ...............uhm that's me. Sorry for the inconvenience. I'm sure you've already found the parameter issue as well as others.

    Leesha

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

    Re: Query Question (2000)

    >>I tried having all the items in tblbilling but I couldn't find a way to apply partial payments to an original invoice and maintain a billing history. If there is a better way to do this I am certainly open to it!<<
    How do you record the payments currently? I thought all the Invoices go into table tblBilling.

    What I would do is to have a table for Payments (amazingly called tblPayments), and a Payment Allocations table, then you can relate multiple payments to one or more invoices.

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

    Re: Query Question (2000)

    >>(amazingly called tblPayments .............tee hee!! OK, that sounds easier. Question, if I set it up that way, would I still connect them in the same manner as I did in the database I posted, therefore having them all linked via a qryAR?? I simply don't think like a database!

    Leesha

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

    Re: Query Question (2000)

    If I restructure this to have tblPayments, is tblAllocations a lookback table that would hold the payment types????? I'm assuming that is what you mean, but I get in trouble when I assume things! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Leesha

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
  •