Using VBA to automate Access 2007 queries with a form for users (1 Viewer)

[KateM]

Hi All

Apologies - I am new to VBA so am probably trying to run before I can crawl.....

I've been using Access 2007 to run queries on a database where we eventually export results as separate Excel spreadsheets for individual clients. The process is quite involved, using queries to change fields from code letters to words and splitting the database up into different client tables, saving the tables under date order and with different client codes.

I now need to pass this role on to colleagues, so need to make everything as straightforward as possible.

I had thought to use a Form as the user interface, with a minimum number of buttons, however I need either the system or the user to amend the date for the initial table, then to use this new table and run a series of standard queries on it, then produce the separate tables.

I don't think I can just use macros behind the buttons, because the database name is changing each time.

Does anyone have any suggestions as to how I can start, please?

I assume some parts will be too tricky to automate - it will be necessary for colleagues to follow instructions instead........

Thank you !

KateM

 

[spikepl]

I'd suggest you describe what it is you are doing in your db, and attach a screenshot of the relation window. What you have said so far sounds like a band aid to fix issues caused by inappropriate design.

 

[KateM]

Hi Spikepl

I can't do a screenshot, unfortunately.



The process I use currently is as follows:

1. Run a make table query on the initial results database and prefix the new table name with the date in the format yyyy-mm-dd.
2. Use that new table and run 7 x update queries on it to convert code letters into words for the clients.
3. Split this table into separate tables for the individual clients, as necessary.
4. Each individual client table is then exported to Excel and emailed to the client in sequence.
5. Our meas_letter database is then updated with date, letter type and unique id to show that the result has been reported. This is then visible against each householder's test record.
6. The original results database is updated to show that the result has been reported to the client, so colleagues can see that they can now send individual result letters to the householders.

It is vital that each client only gets results which they have paid for and that the code letters used within the database are converted into words ready for the Excel spreadsheets - hence why I want to automate at least part of this process, to reduce the risk of manual errors.

Any suggestions, please?

Regards
KateM