Make Table Query

[Sondra]

Using Access 2002

I have a database that I need to have users extract specific data out of to
merge into a Word Document. I've created a "MakeTABLE" query that asks them
for there specific data. I've created the formatting in the query and
everything looks great. Here is my problem. When the query makes the
"mailmerge" table one field doesn't format the way that I need it to.

Example:

Query:

FormNumber = 83275 Formatted as = 08D-3275

Table

Formatted as = 83275

How can I force the table to maintain the same formatting each time a user
runs the query?

 

[Jeff Boyce]

Sondra

When you use a Make Table query, you get what Access decides you need.

If you want to control the format, there's a couple of alternate approaches.

First, you could do away with creating a new table altogether and just use a
query to assemble (and format) the data. You can export a query (actually
the data the query returns).

Second, you could create a "temp" table to hold the data, and create the
table with the formatting you want. Then your query would append to that
table. Of course, you'd also need to create a "delete" query that empties
that temp table first.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sondra]

Because my users aren't very savvy, I really think I have to have a table for
them to merge into the word document.

Can you explain the "temp" table a little more.

 

[Jeff Boyce]

Sondra

A "temp" table is really just a table, but you're only using it
"temporarily".

Create a table with the table definition you want.

Create a query that deletes all the records from that table.

Create a query that loads up that table with the records you'll want to use
in the Merge operation.

Use a procedure (or a macro) to run both the delete and the append queries.

Adjust to fit your situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sondra]

Jeff:

This is a fantastic idea; however, I can't get it to work to my needs and
I'm hoping you can advise.

Okay so here is how the process works....

1. Staff open a "datasheet" form and enter all the records they need to
merge into the Word Document form.
2. They choose the CLOSE Button on the form and a macro runs
a. Delete Query for "temp" Table.
b. Append Query for "temp" Table.
c. Closes "datasheet" form
d. Opens Master Switchboard

Here is what I believe should be happening at that point.

The staff can open Word and merge the "temp" table into the form.

What is happening:

It only allows this "macro" process to run once per opening the database. I
have to close the database each time and it doesn't always delete the
records.

Does that make any sense??

I hope.

Thanks as always.


----------------------------------------------------------

 

[Jeff Boyce]

I'm confused. Where are the records going when they "enter all the records
they need to merge"?

Running an Append query takes data from one source and appends/inserts it
into a table... wait?! didn't they just enter the data into a table?

It all starts with the data ... please describe the data structure
underneath all this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sondra]

I know this is confusing and I will try my best:

I have 300 users of the database; however, 10 of the users need to enter the
data into the database more often than others. So instead of them using the
elaborate form, which creates automatic numbers (sequesntial per year) we
created a simple "excel" type form for them to you. (As I said before, they
are very computer eliterate).

So example:

1. User 1 enter 10 records into the form getting 10 new numbers for the
customers.
2. Runs the macro "when it works"
3. Opens Word and merges and prints the forms.

4. User 2 comes in and enters 7 records
and follows the same steps as user 1; however, print just "their" records.

The reason I can't have them enter directly into the table is because there
is an event procedure on the form to automatically assign numbers. The table
requires the user to add the next available number (very complicated).

Hope this helps.

 

[Lord Kelvan]

why are you not just using an auto number on the table to add the
sequentional number its self

 

[Sondra]

Kelvan:

We have to have it set up this way to work with our program. Thanks for
everyone's help. I was able to work it out on my own this morning.

Sondra

 

[Jeff Boyce]

Sondra

Consider posting back the solution you found.

It could help someone else in the future.

Regards

Jeff Boyce
Microsoft Office/Access MVP