Writing data in a table from a look-up in a form

[Gweny]

Can anyone help me?

I have a form which is created from a combination of customer details
table and quote details table. When I enter the customer ID, the
customer address details automatically update on the form. However,
this information does not enter back in to the corresponding quote
details table.


Can anyone help me write VBA code to have the lookup details entered
into the table as soon as a change is made? Is there some sort of way I
can commit to the changes made in the form so that it writes to the
table after the change has been executed?


I have tried various different ways including queries, which I know
don't save data but seem to get
the same problem that the information does not show up on the quote
details table to allow me to do a merge in to word.


I am seriously tearing my hair out on this one!!!!


Cheers,


Gwen

 

[Steve Schapel]

Gwen,

The Customer ID is the only customer-related field that should be stored
in your Quote Details table. The Address field should be removed from
the Quote Details table. Your mail merge to a Word document should be
sourced to a query that includes the Customer table in order to retrieve
the customer's address.

 

[Gweny]

Dear Steve,

I originally tried to do this but had continuous problems with the word
merge. When I did this, the merge worked fine until such time as I
exited the word document (even if I did save the changes), the document
lost the link to the source document and no matter what macro I
recorded, I couldn't seem to get word to automatically link to the
source document of the query.

I then went back to the table to see if there was anything I could do
there. Therefore, if I can get the word document to automatically open
the correct source document I will be set!

I suppose this is the wrong group to be asking the question, but would
you happen to have any ideas on the SQL or macro I can create to ensure
the right source document is opened in word when I try to do the merge?

I did set a macro to run on opening but it promptly reset itself
straight after the macro ran.

Argh......

Kind Regards,

Gwen

 

[Steve Schapel]

Gwen,

Do I understand you correctly? You mean you made a merge document in
Word, and set up the merge with the Access query as the data source for
the merge? And it was successful? And then you saved and closed the
Word document, but the next time you opened it the data source was no
longer set up? Can you post back with the SQL view of the query that
you are using? And also what version of Access and Word? And when you
are referring to a macro, do you mean in Word or Access? Can you give
details? Thanks.

There are some circumstances under which it is a good idea to either
export the data from the query to a Mail Merge format file, or else to a
temporary table in your database. Another alternative, which may be
applicable, is to have a look at Albert Kallal's "Super Easy Word Merge"
idea here...
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html