I have over a dozen tables that together represent a final quote.
TblSys
TblOven
TblWasher
TblMisc
etc...
They are all linked by PK QuoteNum.
I want to duplicate any given quote so that with a new quote, most of the work is done with just a few changes that are needed. So I need to create a duplicate copy in each table with the new QuoteNum. I want to do this in VBA and haven't come across any good examples on this scale. Most examples that I have found duplicate based on a recordset or query. But because my form has multiple pages (tab control) and subforms, the recordset only involves the table TblSys. Each of the other pages and subforms are based on tables that are linked to the recordset. So when I run the code to duplicate a recordset, it only duplicates the record in TblSys.
I found a nice SQL example as follows
Code:
CREATE TEMPORARY TABLE temp_table ENGINE=MEMORY
SELECT * FROM tblSys WHERE id=1;
UPDATE temp_table SET id=NewID;
INSERT INTO tblSys SELECT * FROM temp_table;
DROP TABLE temp_table;
So I'm trying to do something like this for each table. Is this the right direction or should I approach this in a different way? Is this possible in vba?
How does the structure of tblOven differ from the structure of tblWasher? At first glance, and considering your claim of a dozen tables, this looks like a design problem. I would expect to see a single table, maybe called tblAppliance (or maybe tblProduct) and find washer, oven, microwave, fridge, etc, as rows in that table. What happens if you start selling beds? You have to make a new tblBed, but it won't be included in any of your system design.
Generally the table is the abstraction and the row is the specification. You make a tblPerson, not a tblMale and tblFemale, or tblMike and tblKarla, or tblBlonde and tblRedhead. tblOven is not abstract enough. It contains type data; it contains a specification, so tblOven is not sufficiently abstracted to be a table.
If the table structure is correct, you will not need to make temporary tables in the normal course of a business process.
How does the structure of tblOven differ from the structure of tblWasher? At first glance, and considering your claim of a dozen tables, this looks like a design problem. I would expect to see a single table, maybe called tblAppliance (or maybe tblProduct) and find washer, oven, microwave, fridge, etc, as rows in that table. What happens if you start selling beds? You have to make a new tblBed, but it won't be included in any of your system design.
Generally the table is the abstraction and the row is the specification. You make a tblPerson, not a tblMale and tblFemale, or tblMike and tblKarla, or tblBlonde and tblRedhead. tblOven is not abstract enough. It contains type data; it contains a specification, so tblOven is not sufficiently abstracted to be a table.
If the table structure is correct, you will not need to make temporary tables in the normal course of a business process.
These are industrial ovens and washers. They wouldn't fit in your house...most of them are larger than your house. Each oven and washer system contains dozens of fields (aspects). Aspects that apply to every oven will never apply to washers (and vice versa). Each oven is unique to that customer. We have never had two ovens with the exact same aspect values, so we can't just say that the customer ordered an oven. Same goes for the washer systems.
So why duplicate a record? Because each quote has hundreds of fields that have to be entered. A single quote can take several weeks to complete. So if we can get it most of the way with a similar sized system, that saves a lot of time creating the quote and the customer can get a ballpark cost while the sales person fine tunes the aspects to fit the customer's needs. A system may have 0 or more washers and may have 0 or more ovens. So I disagree that TblOven is not abstract.
I have the following code that builds and executes a SQL statement, but there is a syntax error somewhere in the final statement (getting Run-time error 3134 - Syntax error in INSERT INTO statement) and I am having trouble figuring it out.
Code:
Dim sqlStatement As String
Dim rs1 As Object
Dim rs2 As Object
Dim fld As Field
Dim strName As String
Dim f As Long
Dim SQL2Statement As String
Dim allFields() As String
Dim finalsql As String
finalsql = "INSERT INTO TblFans ("
sqlStatement = "SELECT * FROM TblFans WHERE ID=14;"
Set rs1 = CurrentDb().OpenRecordset(sqlStatement)
Open "C:\temp\testfile.txt" For Output As #1
f = rs1.Fields.Count
For x = 1 To f - 1 'Start at the second field - skip the autonumber field
'MsgBox rs1.Fields(x).Attributes -Used for debugging
If x < f - 1 Then
If rs1.Fields(x).Attributes = 34 Then
SQL2Statement = SQL2Statement & Chr(34) & rs1.Fields(x) & Chr(34) & ","
sql2fields = sql2fields & rs1.Fields(x).Name & ", "
Else
SQL2Statement = SQL2Statement & rs1.Fields(x) & ","
sql2fields = sql2fields & "[" & rs1.Fields(x).Name & "], "
End If
Else
If rs1.Fields(x).Attributes = 34 Then
SQL2Statement = SQL2Statement & Chr(34) & rs1.Fields(x) & Chr(34)
sql2fields = sql2fields & rs1.Fields(x).Name
Else
SQL2Statement = SQL2Statement & rs1.Fields(x)
sql2fields = sql2fields & "[" & rs1.Fields(x).Name & "]"
End If
End If
Next x
finalsql = finalsql & sql2fields & ") VALUES (" & SQL2Statement & ");" ';"
' MsgBox "total of " & f -used for debugging
'SQL2Statement = SQL2Statement ' & ";"
Print #1, finalsql
Close #1
CurrentDb.Execute (finalsql)
Set rs1 = Nothing
Set rs2 = Nothing
SOLVED>
Short story: The code below will duplicate a quote along with all the additional tables that are involved in the quote.
Long story for anyone who wants to do the same thing or is morbidly bored: I created a table (TblCopyQuoteTables) that has one field (CopyTableName). This table contains the name of each table that needs to have the associated quote data duplicated – that is, there are tables linked to the main table via the PK “QuoteNum” that contain additional quote specifications and all of those records need to be duplicated along with the main record in TblSys. Any table that I have linked to TblSys via the PK “QuoteNum” will need to be entered into this table.
The user is prompted to input a quote number to copy and then asks the “are you sure” to allow an abort if the operator fat fingers the number or chickens out.
The code pulls in the list of tables into the array “tablenames” which I have set to a max size of 1,000 table names (a lot more than I need – but allowing for future growth). I could have set the array size dynamically depending on how many records it finds in “tablenames”, but I digress…
Because the table TblSys is my primary quote table, I need to make sure that it gets duplicated first. If it is not duplicated first, other tables will not be able to duplicate the record because no record exist in the linked table. So I hard-coded this to be first in the array.
To get the new QuoteNum (which is an autonumber in TblSys), I use the following code: NewQuoteNum = Nz(DMax("QuoteNum", "[TblSys]")) + 1
[note: I use quote versions meaning that Quote 53241 may have a version 53241.1 and 53241.2 and so on…. so when I create a new quote number, I must make sure that the variable NewQuoteNum is an Integer so that it rounds DOWN and leaves off the decimal – otherwise I get unintended results]
For each table, the code will do a recordset for the SQL- “SELECT * FROM tablename WHERE QuoteNum=quotetoduplicate;” (see code for exact syntax used).
Then it loops through each field and gets the name of the field and value to build the final INSERT SQL statement. It does a check to see if rst.Fields(x).Name =
• “QuoteNum” then I’ll insert the new quote number.
• “LastUpdated” then I’ll insert Now()
• “UpdatedBy” then I’ll insert Environ(“Username”)
Or if rst.Fields(x).Attributes =
• 49 – this is an autonumber and the code will skip putting this in as it will automatically be done when the field is copied
• 33 – this is a number field and the field name needs to be enclosed in brackets [ ] and the values do not need quotes.
• 34 – this is a text field and the field names do not need to be enclosed in brackets but the values should be in quotes (and I used Chr(34) to make the quotes).
I also do a check to see if this is the last field in the recordset and if so, I’ll not add a comma after the data.
Then it finishes the final INSERT SQL statement and runs it: CurrentDb().Execute finalsql, dbFailOnError
Then it goes to the next table and goes through the same stuff.
I still have a little work to do to get to the tables that are linked to a linked table (IE – not linked to main table by QuoteNum). But what I have gets me most of the way there.
This is a huge relief. I struggled with this for a long time to get it to work and now making a duplicate of a quote (involving about a dozen tables and hundreds of fields) is a trivial mouse click.
Thanks to everyone who helped me with various issues on this. Regards!
Code:
Private Sub cmd_CopyQuote_Click()
Dim SQLStatement As String
Dim SQL2Fields As String
Dim rs1 As Object
Dim f As Long
Dim SQL2Statement As String
Dim finalsql As String
Dim fieldvalue As Variant
Dim quotenumber As Long
Dim tablenames(1000) As String
Dim gettablenames As String
Dim tblrecordset As Object
Dim x As Long
Dim y As Long
Dim z As Long
Dim NewQuoteNum As Integer
Dim TableFileName As String
quotenumber = InputBox("Enter the Quote Number to copy: ", "Copy Quote")
If MsgBox("Are you sure you want to create a new quote using " & quotenumber & " as a template?", vbYesNo, "Confirm New Quote") = vbNo Then Exit Sub
NewQuoteNum = Nz(DMax("QuoteNum", "[TblSys]")) + 1
'Open the table TblCopyQuoteTables and get the names of the tables that a quote comprises of so that we can copy all of the data.
y = 1
tablenames(0) = "TblSys"
gettablenames = "TblCopyQuoteTables"
Set tblrecordset = CurrentDb().OpenRecordset(gettablenames)
With tblrecordset
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
End If
While Not .EOF
tablenames(y) = tblrecordset.Fields("CopyTableName")
y = y + 1
.MoveNext
Wend
End With
tblrecordset.Close
Set tblrecordset = Nothing
Rem zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
For z = 0 To y 'loop for each table listed in TblCopyQuoteTables
gettablenames = tablenames(z)
If gettablenames = "" Then 'Make sure we have a table name
'MsgBox "No table name for variable " & gettablenames
Else
Rem Build SQL statements
SQLStatement = "SELECT * FROM " & gettablenames & " WHERE QuoteNum=" & quotenumber & ";"
Rem Open the table for the quote number
Set rs1 = CurrentDb().OpenRecordset(SQLStatement)
rs1.MoveLast
rs1.MoveFirst
Do Until rs1.EOF 'EOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOF
finalsql = "INSERT INTO " & gettablenames & " ("
'Open "C:\temp\testfile.txt" For Output As #1 ' used for debugging
f = rs1.Fields.Count 'total number of fields in the recordset
Rem ]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
If rs1.RecordCount > 0 Then
Rem >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
For x = 0 To f - 1 'Test for a null field - if it is null, we will need to insert "Null" in the SQL statement
If IsNull(rs1.Fields(x)) Then
fieldvalue = "Null"
Else
fieldvalue = rs1.Fields(x)
End If
Rem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If rs1.Fields(x).Name = "QuoteNum" Then
SQL2Statement = SQL2Statement & Chr(34) & NewQuoteNum & Chr(34) & ","
SQL2Fields = SQL2Fields & rs1.Fields(x).Name & ", "
ElseIf rs1.Fields(x).Name = "LastUpdated" Then
SQL2Statement = SQL2Statement & Chr(34) & Now() & Chr(34) & ","
SQL2Fields = SQL2Fields & rs1.Fields(x).Name & ", "
ElseIf rs1.Fields(x).Name = "UpdatedBy" Then
SQL2Statement = SQL2Statement & Chr(34) & Environ("Username") & Chr(34) & ","
SQL2Fields = SQL2Fields & rs1.Fields(x).Name & ", "
Else
If rs1.Fields(x).Attributes <> 49 Then 'Attributes = 49 is an autonumber field and we want to skip this
If x < f - 1 Then 'Not the last record
If rs1.Fields(x).Attributes = 34 Then 'Attributes = 34 is a text field
SQL2Statement = SQL2Statement & Chr(34) & fieldvalue & Chr(34) & ","
SQL2Fields = SQL2Fields & rs1.Fields(x).Name & ", "
Else 'Attribute 33 is a number field
SQL2Statement = SQL2Statement & fieldvalue & ","
SQL2Fields = SQL2Fields & "[" & rs1.Fields(x).Name & "], "
End If
Else 'last record in the recordset - so we close out the SQL statement properly
If rs1.Fields(x).Attributes = 34 Then 'text field
SQL2Statement = SQL2Statement & Chr(34) & fieldvalue & Chr(34)
SQL2Fields = SQL2Fields & rs1.Fields(x).Name
Else 'number field
SQL2Statement = SQL2Statement & fieldvalue
SQL2Fields = SQL2Fields & "[" & rs1.Fields(x).Name & "]"
End If
End If
End If
End If
Rem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Next x
Rem >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
finalsql = finalsql & SQL2Fields & ") VALUES (" & SQL2Statement & ");" ';"
Rem This executes the final SQL statement
TableFileName = "c:\temp\" & gettablenames & ".txt" 'used for debugging so that I can evaluate the final SQL statement for each table
Open TableFileName For Output As #2
Print #2, finalsql
Close #2
CurrentDb().Execute finalsql, dbFailOnError
finalsql = ""
SQL2Fields = ""
SQL2Statement = ""
rs1.MoveNext
End If 'end if for: If f > 0 Then
Rem ]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
Loop 'EOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOFEOF
Set rs1 = Nothing
End If 'end if for: If gettablenames <> "" Then
Next z
Rem zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
MsgBox "Completed copy from Quote: " & quotenumber & " to Quote: " & NewQuoteNum
Exit_Click:
Exit Sub
Err_Click:
MsgBox Err.Description
Resume Exit_Click
End Sub
