Action query cannot be used as row source (1 Viewer)

[justice8965]

Hey guys, having a problem. I have a Select query. If I open the query in design mode, change it to an Append query, enter the table to append to and run it, it works fine.

However, if I save the query then close it, and run it again I get this error message:

"An action query cannot be used as a row source"

If I go back and change it to Select query again, close it, open it, change it to Append it works again. I'm really confused as to whats going on to cause this. Any ideas?

 

[boblarson]

Sounds like it is also bound to a form or combo/listbox. You can't do that with it. What is its purpose and how are you trying to run it?

 

[Banana]

How are you opening the query in design, exactly? Are you opening a query that's a row source of a combobox or listbox, or maybe recordsource of a form?

if you are merely opening a query for design from the database windows' Queries tab and getting that errors, are you sure that query in question isn't used by any combobox or listbox?

Failing both, I would create a new blank database, turn off AutoCorrect then import in all objects and see if this fix the corruption.

 

[justice8965]

It is pulling a number from a Combobox on a form. How would I get around this?

 

[Banana]

Well, when you think about it, you may realize that combobox and listbox by definition *display* a list. They have nothing to do with appending/updating so it's of course expected that we should be using a select query.

Now, you didn't exactly explain what you want to do with the number you pull from the combobox. Are we talking about adding a new entry to the combobox's list, or creating a new record based on the selection made in combobox, or maybe something entirely different? More information is needed.

 

[boblarson]

It is pulling a number from a Combobox on a form. How would I get around this?

Pulling a number from a combo is not a problem but if the query IS the ROW SOURCE of the combo box, that is a problem. You would then need to have two separate queries - one for the combo rowsource and one for the append.

Hopefully that makes sense.

 

[justice8965]

The row source for the combo box is from a different query

 

[justice8965]

Sorry let me tell you more information on what I'm doing.

The number in the combobox is a SiteID, basically it identifies a customer. The query then uses this to pull up an electric usage profile from that customer for the last 12 months. What I want to do is insert this information into a table whenever the user selects a customer from the combobox. I would display the query directly instead of inserting it into a table, but using DLookup makes it run extremely slow, so the table option is faster, when it works.

 

[Banana]

Well, it's possible to code the AfterUpdate of combobox to do an insert into another table based on the selection, but your description doesn't leap out to me as the best thing to do in this case.

You described it as basically a record selector, to pull up a profile, which is perfectly good use of combobox. You then mentioned another table, but I'm not sure what is this table's purpose and if it even makes sense to want to insert records based on selection in this context.

I know we've been asking more questions but the reasons is not so we don't just give you the technically correct but structurally deficient answer which will cause you more headache and ulcers down the road. That's why we ask questions to help us understand that we're giving you the best possible answer not just to your particular problem at hand but also to the whole application as whole.

Hope that helps.

 

[justice8965]

Oh I understand the questions, thats no problem.

Yes, the combobox is basically a search box that lets the user select a customer from our database.

The other table is basically one where I want to store the results from the query (StartDate, EndDate, and Use). And you're correct, I am using the AfterUpdate event to delete whats currently in the table and then append the new results. Well, thats what I'm trying to do at least.

The reasoning behind using this table is because when I used a subform which was linked to the query, it worked, but was extremely slow due to me using DLookup in the Use field to perform a calculation based on the previous record. It seems to be much quicker to insert these results into a table and then link the subform to the table to display results on the main form.

 

[justice8965]

Well I actually just solved my problem

Made a seperate Append query, which used the first query for its fields, and kept the first query as a Select. This seems to work fine. Thanks for the help.

 

[exec]

I know this thread has been dead for a year now, but I need some help on this one...

I basically have the same problem as justice8965, and I was wondering if someone could guide me through how to implement the last suggestion:

Made a seperate Append query, which used the first query for its fields, and kept the first query as a Select. This seems to work fine. Thanks for the help.

How can I post the results of the select query (from combobox) to an append/update query so that I can update fields in the corresponding table?
Thank you ahead of time!!