run query

[mlswyo]

I have populated an append query and now need to run it without opening it. Then I want the data deleted so it can be run again with a different set of numbers. How to do this with vba code?

thanks

 

[jdraw]

Interesting request. Could you elaborate on why you want to run the Append query, then delete the newly Inserted data, and the run again with different numbers?
What are you trying to achieve?

 

[mlswyo]

I select a number of employees from a list box and a training topic from a text box and put these into an append query that data is then appended to a table. since the data is still in the query, I assume that next time I do this all the data will be append, not just the new. I use this process over and over so I thought it needed to be dumped after each time the appending is done. If there is a better or more correct way to do such a thing, I'm open to any thing that will allow me to relate these two tables.

thanks

 

[jdraw]

Not quite, but how about a suggestion.

You have a ListBox and a TextBox (on a form presumably).
So why not use the Form.
Add a button that executes a query (an Append query)

Now if you set up your table, where you're storing the data, such that NO DUPLICATEs are allowed. You should have tables with keys that prevent duplicates.

You didn't mention how the ListBox and textBox were getting their data. Perhaps you could tell us more about your setup and your table structures.

How about the idea of using the Form?

 

[mlswyo]

On a form I have a multi-select list box for the list of employees which comes from employees table. Then I have a combo box from which to select a single training topic. This data cones from a table of training topics. Since the two tables are not related or do I say they have a many to many relationship, I've created a link table whose key is a composite made of the pk from each of these these two table. No duplicates are allowed. This link table then becomes my table of required training for each employee. By using the list box I can get required training input faster. What I've tried to do, but simply do not know how, is to have a matrix with training topics at the head of column and name of employees as the head of rows. Then at the row-column junctions would be a indication of whether an employee has that topic as a requirement.

I'm working with about 120 employees and over 30 training topics and these numbers are growing.

My current form has a button to append the data to the link table. All seems to work. My goal is to make input as painless and time consuming as possible.

From this information we plan to track each employees training to ensure that it is current for jobs they will be working on. Clients expect people working on their project to have specific training; we have to make sure that is the case before we send an employee to a job. We are not trying to track details of their training which most systems I've look at do. All that matters to us is that they do have current certificates. All employees do not have the same requirements.

Hope this helps and I welcome any suggests.