Problem populating combo box

[Annalyzer]

I'm trying to populate a combo box on a form by inserting this query:

SELECT *
FROM menu
WHERE NOT submitted;
 

into the Row Source property of the combo box control. The Row Source Type is set to Table/Query. The query is a stand alone query (not just typed into the Row Source property).

Every time I try to save the form or switch to form view, I get the error message: Microsoft Access has encountered a problem and needs to close.

Here is what I've tried so far:

• recreated the combo box
• rewritten the query
• compacted and repaired the database
• created a new database and imported all my tables, form, reports, queries, macros, and modules

I don't know what else to try. Can you help me or is there any more information I can provide to help you to help me solve this problem?

[salimudheen]

Actually your process is correct. Better u try to create one query with ur sql select statement. And test the query. If its correct better u give that query name as a recordsource of the combo box.

Regards
Salim

[NeoPa]

What happens if you try to save the form without the ComboBox there (deleted)?
(Don't forget to backup anything you change for testing purposes.)

[Annalyzer]

The query does return the correct records, salimudheen, and it is saved as a separate query with the name of the query as the record source for the combo box.

NeoPa, I can save the form with the combo box deleted just fine. I can also save the form with the combo box in place as long as I don't try to add the query as the record source. It's only when I put that query in place that I have problems. Does it matter that the form itself will change the results of the query? The purpose of the form is to add new menus to the database. Later in the processing, a group of menus are submitted as a single claim. Hence, the menus being entered on the form fit the criteria of the query. The purpose of the combo box is to allow the user to retrieve and change a menu that has already been entered but has not yet been submitted in a claim. If this is the problem, I wonder if I could put the combo box in a subform? I guess that will be the next thing I try unless you see a more definite problem and/or solution.

[Annalyzer]

I finally figured this one out. Apparently, you can't have calculated fields in the query that populates the combo box. As soon as I took the calculated fields out, the combo box populated correctly with no errors.

[Stewart Ross]

Hi Annalyser. I've been following this puzzle too, and though I'm glad you have found a solution things don't quite add up. In particular, your reference to calculated fields is in conflict with your SQL posted in post #1 which is a straightforward select all from an underlying table.

I had tried, as I am sure had others, to replicate the problem you faced - without success. My conclusion was that there had to be more to this one than what you mentioned in your posts (by oversight I am sure).

Access combos can normally be used with calculated fields in their rowsource queries (e.g. to show an item list with Max(SalesDate) to show the most recently recorded transaction for that item). I cannot think what must have been happening to cause the application errors that shut you down so consistently. When I was trying to replicate the problem earlier I had the nagging feeling that what you were doing with the combo was in conflict with the form's recordsource in some way, but that was just conjecture on my part.

I went as far as trying deliberately to set a recordsource for the combo that was the same as the recordsource of the form itself - no problems there. Anyhow, you found a solution that worked, and that can only be to the good for you.

-Stewart

[Annalyzer]

You are so right and I am so undisciplined. The query was pulling * from a query, not from a table (my bad, I'm sorry to mislead). The query it was pulling from was this:

SELECT menu.provider, menu.month, menu.year, menu.type, menu.t1attend, menu.t2hattend, menu.t2lattend, menu.op_days, menu.t1B, menu.t1AS, menu.t1L, menu.t1PS, menu.t1S, menu.t1ES, menu.t2hB, menu.t2hAS, menu.t2hL, menu.t2hPS, menu.t2hS, menu.t2hES, menu.t2lB, menu.t2lAS, menu.t2lL, menu.t2lPS, menu.t2lS, menu.t2lES, menu.t1Bprice, menu.t1ASprice, menu.t1Lprice, menu.t1PSprice, menu.t1Sprice, menu.t1ESprice, menu.t2lBprice, menu.t2lASprice, menu.t2lLprice, menu.t2lPSprice, menu.t2lSprice, menu.t2lESprice, provider.lname & ", " & provider.fname AS Name, (t1B*t1Bprice)+(t1AS*t1ASprice)+(t1L*t1Lprice)+(t1PS*t1PSprice)+(t1S*t1Sprice)+(t1ES*t1ESprice)+(t2hB*t1Bprice)+(t2hAS*t1ASprice)+(t2hL*t1Lprice)+(t2hPS*t1PSprice)+(t2hS*t1Sprice)+(t2hES*t1ESprice)+(t2lB*t2lBprice)+(t2lAS*t2lASprice)+(t2lL*t2lLprice)+(t2lPS*t2lPSprice)+(t2lS*t2lSprice)+(t2lES*t2lESprice) AS [Claim Total]
FROM menu, provider
WHERE menu.provider=provider.Id AND menu.selected=True;

The form's recordsource is the table 'menu.' I had the same feeling about there being a conflict, but I couldn't make sense of it.

It's kind of weird, really, my not being able to think straight. Been lots of stress the past few months - husband's aorta split, life flight to hospital, 8 hours open heart surgery, 2 weeks in a coma, etc, etc. This was all 5 months ago and I thought it was over. I didn't realize how much it had affected me until I started this project. I'm having a hard time following a train of thought like I used to. Sure hope it gets better, this is frustrating. Thanks for your help. You've been a real blessing.

[Stewart Ross]

Hi Annalyser. I think you have had much more pressing matters to deal with than Access in the last few months! I wish you and your husband well and hope that the traumatic events you have experienced have been, and continue to be, overcome.

Looking at the query you provided, I notice that your two tables (menu and provider) are unjoined. Although the Where clause will do a similar job to the join, we do not know and cannot predict the workings of the Access Database engine (JET). Depending on the size of the tables the lack of a join could be a source of difficulty, but I doubt that it could explain the severe errors causing application closedown.

There are several calculated fields in the query, but on the face of it these are not complex either.

This one remains a bit of a mystery at present...

-Stewart

[Annalyzer]

I was able to make it work by removing the calculated fields, so I'm not worried about resolving why it wouldn't work. I'll set this one aside and try to figure it out when my brain is less taxed. For now, I just want to be done with this project and I think I can see the finish line from here! =)