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

    List Box doesn't fill (2000)

    Hi Again!

    Ok, I've been at this for hours as I've done this before and have had it work effectively (due to help from the lounge) and now I'm crazed!

    On frmAR I have a list called lstInvoiceSelection. I want the form to only show the names that correspond to the AccountID that the form opens to. (The form opens in edit mode). I've used the following SQL in the query:

    PARAMETERS [Forms]![frmAR]![AccountID] Text ( 255 );
    SELECT tblBilling.AccountID, tblDemographics.[LastName/BusinessName], tblBilling.LaborCost, tblBilling.DateWorked, tblBilling.InvoiceTotal, tblBilling.DatePaid, tblBilling.PaidInFull
    FROM tblDemographics RIGHT JOIN tblBilling ON tblDemographics.AccountID = tblBilling.AccountID
    WHERE (((tblBilling.AccountID)=[forms]![frmAR]![AccountID]));

    I don't get any error messages when the form opens. The only issue is the lstInvoiceSelection doesn't show any records. When I simply have the list load to show all the records it's fine. It's only when I set the parameters to limit the the list to AccountID.

    Thanks,
    Leesha

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

    Re: List Box doesn't fill (2000)

    Is AccountID a text field? If it is numeric (long integer), you should specify that in Parameters.

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

    Re: List Box doesn't fill (2000)

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    ARRRRRHHHHHHHHHHH!!! I thought I'd covered all my bases!!!! I kept "thinking like Hans & Pat".
    Leesha

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

    Re: List Box doesn't fill (2000)

    <P ID="edit" class=small>(Edited by Leesha on 25-Jun-03 20:17. )</P>Really frustrated now. lstInvoiceSelection populates to the correct AccountID however for some reason totally foreign to me, its changing each time I re-enter the form and it requeries. The worst part is its changing the data in the underlying tables!!! The form is bound to qryAR and in an effort to fix this issue I made sure the SQL for the list was the same as form qryAR, but took out the columns I didn't need. I assumed it had something to do with the joins???? Anyway, that didn't fix the issue. In addition there seem to records being added when the list requeries, which I don't understand at all. The form is open in edit mode and there aren't new records being added. I'm truly lost wit this one!

    Thanks,
    Leesha

    BTW, the SQL for the list is as follows:

    PARAMETERS [forms]![frmAR]![AccountIN] Long;
    SELECT tblBilling.AccountID, tblDemographics.[LastName/BusinessName], tblBilling.InvoiceNo, tblBilling.DateWorked, tblBilling.InvoiceTotal, tblBilling.PaidInFull
    FROM tblDemographics RIGHT JOIN tblBilling ON tblDemographics.AccountID = tblBilling.AccountID
    WHERE (((tblBilling.AccountID)=[forms]![frmAR]![AccountID]))
    ORDER BY tblDemographics.[LastName/BusinessName], tblBilling.DateWorked;

  5. #5
    Lounger
    Join Date
    Dec 2002
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box doesn't fill (2000)

    Exactly when do you execute

    lstInvoiceSelection.Requery

    ?

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

    Re: List Box doesn't fill (2000)

    The requery takes place when the form itself loads. The form is opened from another form that allows the user to select the account from a list. frmAR opens to the AccountID selected. The query is narrowed down to the AccountID that frmAR opens to. The goal is that the user can then click on the invoice they want to post payment to.

    Leesha

  7. #7
    Lounger
    Join Date
    Dec 2002
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box doesn't fill (2000)

    Looks like the control of [AccountID] on frmAR is not ready when the form is up. Why don't you try the requery at Form_Current?

    When the query condition depends on a control of the form that is opening, it creates a funny timing issue (that mysterious event sequences). Since this form is opened from another form, I would use OpenArgs to send the AccountID and use that data to make the row source SQL of the list box. This way, it is rather explicit.

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

    Re: List Box doesn't fill (2000)

    You write that the underlying data are changed when the list box is requeried. That seems to indicate that the list box is bound to a field (i.e. the Control Source of the list box is a field name). I think that the list box should be unbound.

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

    Re: List Box doesn't fill (2000)

    Hi Hans,

    You'll be proud to know that I made sure the list was unbound, due to a similar thing happening to me with another database due to the lst being bound, and your advise to have it unbound!!! Alas, its already unbound and thus why I'm confused more than usual!!

    Leesha

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

    Re: List Box doesn't fill (2000)

    I will give this a try.

    >>I would use OpenArgs to send the AccountID and use that data to make the row source SQL of the list box. This way, it is rather explicit.

    frmAR loads with ....Me.AccountID = Me.OpenArgs. Do I add anything more to it or simply add the requery to the form_current? And, would this explain uderlying data being changed or worse yet records being added??

    Leesha

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

    Re: List Box doesn't fill (2000)

    The problem remains the same. Records are still being changed, records added, and in some instances records don't show and then when reopened they show up!!

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

    Re: List Box doesn't fill (2000)

    I'm afraid that - as before - it is impossible to find out what is happening without seeing the database. So once again, could you post a stripped down, sanitized version of the database? Don't forget to throw out everyhing not related to the problem at hand.

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

    Re: List Box doesn't fill (2000)

    I'm reading your mind and am in the process of doing that. I even remembered to take out the reports etc!! I'll post it in about 1/2 hour. Gotta do the mom thing and get the kiddies to bed!

    Leesha

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

    Re: List Box doesn't fill (2000)

    <P ID="edit" class=small>(Edited by Leesha on 25-Jun-03 23:57. Forgot to attach database!!)</P>Here you go. I eliminated all records from tblBilling and then went in a created one new invoice for each Account. I then used frmARAccountName to open each account in frmAR. They openned fine the first time. On the second round they began to open with no info in the list box. After the second time this happened I went to tblBilling and sure enough records were changed and added.

    I'm cringing at the thought of what you'll find. Don't laugh too hard!!

    Leesha
    Attached Files Attached Files

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

    Re: List Box doesn't fill (2000)

    When I open frmAR I get a parameter request for AccountIN and AccountID, these obviously don't appear on the form.
    Have you tried to open form frmAR in this cutdown database?

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
  •