How to Copy Records to different table

[arnodys]

Hi all

This gonna be a long one.... I've search the whole forum for answer to this problem, but couldn't find the one suitable enough.

I have a quote table with the following field:

QuoteName
QuoteDescription
QuotePrice

and booking Table:

BookingName
BookingDescription
BookingPrice

The real table is much more complex, but this will do for now.

When a client call up for a quote, their request will be added into the "QuoteTable". One name can occur many times depend on how many they ask for quote. Once they decided to proceed with the booking, all the records need to be transfered into "BookingTable".

Currently, I use either one of the following codes found in this forum:

Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset

Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
  "WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")

Do Until rstQuote.EOF
  rstBooking.AddNew
    For Each Field In rstQuote.Fields
      rstBooking.Fields(Field.Name).Value = _
          Nz(rstQuote.Fields(Field.Name).Value, "")
    Next Field
    rstQuote.MoveNext
  rstBooking.Update
Loop

or

Dim lngOuterCounter, lngInnerCounter As Long
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset

Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
  "WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")

If Not rstQuote.EOF And Not rstQuote.BOF Then
  rstBooking.AddNew
    For lngInnerCounter = 0 To rstQuote.Fields.Count - 1
      rstBooking.Fields(lngInnerCounter) = _
          Nz(rstQuote.Fields(lngInnerCounter), "")
    Next lngInnerCounter
  rstBooking.Update
End If

But none seem to work. Can anyone help?


Cheers
arnodys

 

[TanisAgain]

It is problably simpler to use an append query, 'cos that is what you are essentially doing. Something like this will do it.

Dim strSQL As String

strSQL = "INSERT INTO BookingTable([Field1], [Field2], [Field3]) "
strSQL = strSQL & "SELECT QuoteTable.[Field1], QuoteTable.[Field2], QuoteTable.[Field3]"
strSQL = strSQL & "FROM [QuoteTable]"
strSQL = strSQL & "WHERE (((QuoteTable.[PK])=[Forms]![frmYourFormName]![PK]));"

DoCmd.RunSQL strSQL

This will take the specified record from a form and put it into the table BookingTable. Run it from behind a command button. Change and add the field names to suit. Also, think about deleting the record from the Quote table?

 

[ions]

The first error I see is that your declared rtsBooking but lower in the code you use rtsClient. You should get a compile error.


Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset

Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable") ***** Compile Error here***

 

[RV]

You need to use an append query as suggested by TanisAgain.

One important remark regarding your variables.
You need to explicitally declare all variables.
If you don't, the default Variant will be used.

For instance, this piece of code you posted

Dim lngOuterCounter, lngInnerCounter As Long
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset

results in:

lngOuterCounter being declared as Variant
rstQuote being declared as Variant

Above code should be

Dim lngOuterCounter As Long
Dim lngInnerCounter As Long
Dim dbs As DAO.Database
Dim rstQuote As DAO.Recordset
Dim rstBooking As DAO.Recordset

To prevent this (common) incorrect declaration, define each variable on a new line.
This forces you to explicitally declare the appropiate type.

RV

 

[aingram]

Im trying to use the following code for a database i have (see pict.gif)

Private Sub Command433_Click()

Me.CF_Date = Me.DateENT_CF
Dim strSQL As String

strSQL = "INSERT INTO Inspections_CF([SBS_Code],[Date_done],[Route]) "
strSQL = strSQL & "SELECT Main_Table.[SBS_code],main_table.[CF_date],main_table.[CF_Area],"
strSQL = strSQL & "FROM [main_table]"
strSQL = strSQL & "WHERE (((main_table.[sbs_code])=[Forms]![Main_Form (Streets)]![sbs_code]));"

DoCmd.RunSQL strSQL

Me.DateENT_CF = Null


End Sub

I get an error message saying that the syntax is wrong in the INSERT INTO line(see attached) i obviously have changed the code to suit each of the three buttons

any ideas why??

 

[neileg]

Why two tables? Why not have a field that indicates a quote or a booking and just change that. You could periodically purge old quotes that have not turned into bookings.

 

[allan57]

Aingram

I have modified your piece of code (removed a comma and added a space in a couple of places) check and see if it works now.

Private Sub Command433_Click()

Me.CF_Date = Me.DateENT_CF
Dim strSQL As String

strSQL = "INSERT INTO Inspections_CF([SBS_Code], [Date_done], [Route]) "
strSQL = strSQL & "SELECT Main_Table.[SBS_code],main_table.[CF_date],main_table.[CF_Area] "
strSQL = strSQL & "FROM [main_table] "
strSQL = strSQL & "WHERE (((main_table.[sbs_code])=[Forms]![Main_Form (Streets)]![sbs_code]));"

DoCmd.RunSQL strSQL

Me.DateENT_CF = Null


End Sub

 

[aingram]

Perfect Thanks

 

[Pat Hartman]

Your original code probably didn't work because of the way you dim'd the recordset -
Dim rstQuote, rstBooking As DAO.Recordset

rstQuote is defined as a varient rather than a recordset. You need to specify EACH type:

Dim rstQuote AS DAO.Recordset, rstBooking As DAO.Recordset

Personally, I never dim two variables on a single line. It doesn't save any space and it just makes it harder to see that two variables are defined. I would code this as:

Dim rstQuote AS DAO.Recordset
Dim rstBooking As DAO.Recordset

And finally, keeping the quotes and bookings in the same table reduces the number of forms, queries, reports, etc. that you need to define. Just add a field that indicates whether a record is a quote or a booking. Then you can use the where argument of the OpenForm/OpenReport method to specify which you want to show.

If you insist on using two tables, the append query is the better method since it will be faster than a VBA code loop.