multiple append queries

  • Thread starter Thread starter Gina Wolfe
  • Start date Start date
G

Gina Wolfe

Our database creates quotes to our customers. We want to
keep the history of all quotes submitted. The customer
will request a minor change to the quote, so we submit a
new quote with the altered infomation. As the database
works today, the sales group has to re-type the entire
quote on a new record because of my lack of programming
abiltiy.

I want to be able to copy an existing quote to a new
record which includes data from 4 different tables. I not
having any problem doing the first append, that copies the
main header information into a new record. My trouble
comes in with the unique identifier which links all the
tables together. Once I have my first append completed.
I need to know the new quoteID (unique identifier-
autonumber)and then have it added to the new records in
the other three tables along with the original quoted
information.

These are my tables.
Table 1 - header info ( Company Name, Address, Date of
Quote)
Table 2 - Standards to the Quote
Table 3 - Add'l requirements
Table 4 - Specific Requests

Table 1 & Table 2 have a one to one relationships
QuoteId = QuoteID of each table.
Table 2 QuoteID Once -- Table 3 and 4 QuoteID many times

Some how I need to get the new append records "quoteid"
into the following tables with the corresponding data from
the previous record.

My plan is to do this in a macro based upon the current
record they are viewing.

Thanks in advance for any direction given...
 
Gina,

There are several approaches that could be taken here. Let me say
first, though, that I would recommend combining Table1 and Table2
together into one table. There is absolutely no reason to have this
information split across 2 tables, and a number of reasons not to.
Particularly as apparently a valid record in Tables 3 and 4 depends on
the existence of a record in Table2.

One approach is to use a VBA procedure to create your new Table1
record in a recordset, and retrieve the new QuoteID from there. This
would look something like...
Dim rst As DAO.Recordset
Dim NewQuote As Long
Set rst = CurrentDb.OpenRecordset("Table1")
With rst
.AddNew
!QuoteDate = Date
.Update
.Bookmark = .LastModified
NewQuote = !QuoteID
.Close
End With
Set rst = Nothing
.... and then you would use code to define the SQL of an Update Query
to fill in the rest of the fields in Table1 and then the Append
Queries to insert the required data into Tables 3 and 4, referring to
the NewQuote variable to get the value of the QuoteID to use in your
queries.

Another idea is to open a form at the new Table1 record after it has
been created, and then refer in your Append Queries to the value of
the new QuoteID, using syntax such as [Forms]![NameOfForm]![QuoteID]

Please post back if you need further help with any of these
suggestions.

- Steve Schapel, Microsoft Access MVP
 
Back
Top