List Box doesn’t fill (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

ARRRRRHHHHHHHHHHH!!! I thought I’d covered all my bases!!!! I kept “thinking like Hans & Pat”.
Leesha

Reply | Quote

(Edited by Leesha on 25-Jun-03 20:17. )

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;

Reply | Quote

Exactly when do you execute

lstInvoiceSelection.Requery

?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

(Edited by Leesha on 25-Jun-03 23:57. Forgot to attach database!!)

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

Reply | Quote

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?

Reply | Quote

It opens fine for me. I’m not sure what AccountIN is even referring to.

Leesha

Reply | Quote

I can open the database without problems.

1. When you double click the list box in frmARAccountName, you open frmAR without passing a WHERE condition; that means it is opened with ALL records, not only those for the AccountID you selected in frmARAccountName.

2. The selected AccountID is passed to frmAR in the OpenArgs argument, and used by frmAR to set AccountID. BUT, as remarked above, frmAR is opened with ALL records, so you will end up changing the AccountID of one of the existing records to the one selected in frmARAccountName; this makes it seem as if new records for that AccountID appear each time you open frmAR.

Setting AccountID was useful when you opened a form to a new record, to ensure that it would have the correct AccountID. But this time, you are editing existing records. So I would suggest to change the line that opens frmAR to:

DoCmd.OpenForm “frmAR”, , , “AccountID=” & Me.AccountID, acFormEdit

and to remove (or comment out) the line in the Form_Open event of frmAR that sets AccountID to OpenArgs.

Reply | Quote

Wow Han’s that worked beautifully!! You don’t want to know the hours I struggled with it before posting!! I appreciate the explanation. Again, always that need to “understand”. I wonder that I ever will.

Does the fact that frmInvoice (which is opened from frmInvoiceAccountName) opens to a new record, eliminate the issues I had hear or is there something going on behind the scenes I’ve yet to find?

Thanks,
Leesha

Reply | Quote

I haven’t checked everything in depth, but as I wrote, the code to set AccountID is OK for a form that opens to a new record. In a quick test, frmInvoiceAccountName and frmInvoice seem to work.

Reply | Quote

Thanks!!!

Reply | Quote