Append query issue

[Jgr4ng3]

Hello

I have an append query that has a criteria to be regarding a certain employee, however as this employee has no records in this table, I get an error saying 'No records'.

Normally this would be fine if it was only one query being ran but I have several queries running one after the other once this command is ran - this error stops the remainder of the queries running meaning I cant finish the command!

Is there a way to say 'If no records, never mind?'

Or something to that affect

Cheers!

 

[apr pillai]

If you are running the Queries in a Macro then disable the warning messages by setting SetWarnings to No and reset it at the end with SetWarnings to Yes.

In VBA do this with Docmd.SetWarnings False and at the end True.

 

[Jgr4ng3]

If you are running the Queries in a Macro then disable the warning messages by setting SetWarnings to No and reset it at the end with SetWarnings to Yes.

In VBA do this with Docmd.SetWarnings False and at the end True.

Hello

I am already surpressing the warnings (such as are you sure you want to append data etc. etc.) but this one will not be surpressed, presumably because the macro ceases to run when it hits this error.

 

[Jgr4ng3]

Any help please anyone??

 

[pr2-eugin]

Could you show the Code you have?

 

[Jgr4ng3]

Could you show the Code you have?

Okay so the onclick event runs two Macros - 'Add to inactive' and 'Delete from Active'.


Both run a series of 11 different queries, each for a different table. One of the queries as an example is (they're all very similar though).

INSERT INTO [AgentAssist QA] ( [Date], Agent, [Holly ID], Utterance, [Audio Quality], [Timeout?], [Timed out?], [Service Selected], [Subject Selected], [Correct Service], [Correct Subject], Comments, [Monitored by], Supervisor )
SELECT [Inactive AgentAssist QA].Date, [Inactive AgentAssist QA].Agent, [Inactive AgentAssist QA].[Holly ID], [Inactive AgentAssist QA].Utterance, [Inactive AgentAssist QA].[Audio Quality], [Inactive AgentAssist QA].[Timeout?], [Inactive AgentAssist QA].[Timed out?], [Inactive AgentAssist QA].[Service Selected], [Inactive AgentAssist QA].[Subject Selected], [Inactive AgentAssist QA].[Correct Service], [Inactive AgentAssist QA].[Correct Subject], [Inactive AgentAssist QA].Comments, [Inactive AgentAssist QA].[Monitored by], [Inactive AgentAssist QA].Supervisor
FROM [Inactive AgentAssist QA]
WHERE ((([Inactive AgentAssist QA].Agent)=[Forms]![Navigation Form]![NavigationSubform].[Form]![NavigationSubform].[form]![Combo30]));

I know everything is poorly named... I started the database before I knew enough to care!

 

[pr2-eugin]

You mentioned that some Error occurs that stops the entire operation, you need a proper Error handler to "skip" this.. Unfortunately I am not that great with Macros.. If you could convert it to VBA, I might be able to look over it..

Are they all DoCmd.OpenQuery? Is the Forms kept open whilst the queries are run?

 

[Jgr4ng3]

Hello

So I realise I had made a completely novice mistake, about 6 of the queries somehow had the incorrect combo field in their criteria! Having resolved that, I now get the 'An INSERT INTO query that contains a multivalued field cannot contain another field'

I previously had the 'An INSERT INTO query cannot contain a multi-valued field' error however having changed the field to just the 'value' for the multi-valued field and 'data' for the attachment field, I now get the new one!

The situation is... I have two tables - 'Appraisals' and 'Inactive Appraisals'. They are the same table, just a duplicate that basically hides all the data when I no longer need access to it, but stores it incase that employee returns and I want their previous data again as we have a lot of seasonal staff.

The query copies the data from Appraisals, appends to Inactive Appraisals, then another query deletes that data from Appraisals.

Why did Access introduce Multi-valued fields when it cant handle them... AAARRGGHHH - I will never use them again...

 

[Jgr4ng3]

-- Forgot to add, all these queries have to have criteria against another field to ensure the correct data and only that data is being moved/deleted.

 

[Jgr4ng3]

Any suggestions?

 

[Jgr4ng3]

Changing the way I manage whether they are active/inactive. There will now be a status field in the AgentList table and every query will provide only Active agents... Hopefully it wont be too slow!!