Create a form to run an append query

[Kregg]

I am creating a database for users that no nothing about access nor do they want to learn. So the database needs to be as simple as possible.

This being the case I would like to avoid users from creating/editing querys.

I would like to have a form that would run the append query. The query would have a date column and a status column that the query would filter.

The query would have a criteria for status that would equal to "BACKED OUT" and the date would need to link to the two text fields on the form. My problem is writing the VBA code to make the query append in the background.

For sake of argument:
Query = qryappend
Form = frmappend
Table = tblappend

Can anyone help me with this issue? If more info is needed let me know.

 

[plog]

Action queries always trigger a 'bad-structure' reaction in my brain. Why do you need an append query? Even more of a trigger is providing the ability of users to run them. Why?

 

[Kregg]

You make a good point Plog. This would not be a feature that I would want me users to have access to, however it is a feature the administrator needs access to and currently I am the only one at my company that knows Access. My fear is that if I do not make this process simple for the person who has my job after me that it will go unused and cause unnecessary build up in the system.

Eventually I would like these records to be exported to another db entirely so as to not clutter the main db.

 

[plog]

Eventually I would like these records to be exported to another db entirely so as to not clutter the main db.

That's not soothing my fears of your database structure, its only making me feel more right in my assessment.

 

[Kregg]

If you could elaborate it would be much appreciated. I have only been using access for 2 weeks and taught myself everything from six books and the Internet so any information on structure would be appreciated.

 

[plog]

Like I said Action queries and exporting old records are flags that make me think you've structured your database incorrectly. It may in fact be properly laid out, but I see signs.

The action queries (APPEND, MAKE TABLE, DELETE) I've come across are usually used to overcome poor structure--i.e."once a record is 3 weeks old, we run these queries to move it from Under Construction Table to In service Table", "Every Friday we run an update query to recalculate everyone's age in the Contacts table", "This table makes a new table of just people seen by the doctor in the last week", etc. All those action queries overcame a poor structure.

When you said you want to move data to make the tables less cluttered, it just reinforced my initial reaction. Instead of moving records, you usually set up a new field to your table to mark records as inactive, or retired or whatever term you want to use so that you can identify those that shouldn't be easily accessible by every day users. You leave them in the initial table, you just use a field to mark them as such.

If you explain your process and thinking a little more I can tell you if your on the right track.

 

[Kregg]

I understand what you mean. I took a deeper look into the database and will not require this as it is not necessary and could cause potential problems to the structure in the future. Thanks Pog.