PLEASE HELP - How can I refer to a form field in SQL code?

[imakemedia]

Hi. I'll start by apologising that my question may seem a little novice to some, but this one's tearing my hear out!!

I am trying to create 'delete agent' button. So I have about six tables, and I have a delete agent form, where the user is selected from a list, confirmed in another field, then when the user clicks the button, any record in all six tables containing the selected agent's name will be deleted.

The confirmation field on the Delete Agent page is not associated to any tables. Every time I click on the delete agent button, it asks me to enter a parameter value for DeleteAgent.DelAgt. Here's an example of the code I'm using in the macro:

DELETE * 
FROM Table1
WHERE (EXISTS
      (SELECT * FROM Table1 WHERE Table1.Agent = DeleteAgent.DelAgt = True));

I'm sure it's a matter of syntax, and I think it's because the "DeleteAgent.DelAgt" bit is referring to a table, not a form.

Is this possible to do, or do I have to link it to a table?



If it's unclear what I need, please say so.

Thanks people. .

[June7]

What do you mean by 'delete agent page'? Page is where? Is this a web page?

If code is behind an Access form, it is incomplete. The DELETE is an SQL action. Try:

DoCmd.SetWarnings = False
DoCmd.RunSQL "DELETE ..."
DoCmd.SetWarnings = True

Test this on copy of table first.

Why do you want to delete agent and related records? Could have a field in Agents table that would set the agent as inactive (a Yes/No field would do). Then you can filter out all inactive agents.

[imakemedia]

Hi, THANKS FOR THAT.

I'm a complete noob to access, so am probably not being very efficient with the structure of the db, haha.

I have one main 'Agents' table, and other tables (e.g. call monitoring, attendance etc.) that have the agent field in it (they are all related). The inactive agents option makes sense, then I'm guessing after a while, they could run a query / macro that deletes all inactive agents?

It is being designed for a call centre team leader as a way to administer his team. There needs to be an option to delete an agent if they leave the company, so that's the bit I'm stuck on at the minute.

In answer to your question, the DeleteAgent 'page' is a form, with a list of agents in the Agents table. Once the agent is selected, the 'DelAgt' field is populated.

Thanks for the tip. I'm sure once I'm used to access a bit more I'll create better structures.

Cheers, .

[June7]

My point is that there is seldom a real justification for deleting records. If the accumulation of records will cause db to exceed Access size limit, then maybe purge old data or migrate to another db program like SQL.

You should periodically run Compact & Repair on the project, if not set to automatically do this. Design editing causes file size to grow.