Copy record as a new record in a large number of tables (1 Viewer)

[myquery]

Scenario:
I have "Master" table with fields "Job No" and "Revision No". Both together is a primary key, so that combination of both cannot be duplicated. I have 100 other tables to be related with referential integrity(+update&delete) to Master for both fields. Apart from Job No and Revision No, all 100 tables have different set of fields which is why I had to come with so many tables. Due to 32 limit rule, I had to come up with workaround method to have all 100 tables in the relationship. So, I created 5 other SubMaster1, SubMaster2, ...., SubMaster5 which are related to Master with relationship with referential integrity (+update&delete). Then I assigned 20 tables to each SubMaster so that 20 tables are related to each SubMaster table. Whenever I create new record in Unit, the new record is generated in each SubMaster using update query for each SubMaster table. I have all the forms and necessary query laid out. The only missing part is being able to duplicate a record. I have limited knowledge in VBA, but I should be able to modify it to address to my requirement.

Feature Requested:
I want to copy a given record in Master, SubMasters and 100 tables as a new record. I need this feature so that I can select certain Job No and Revision No and copy that as a new Job No(assigned manually in a form) and 0 as the revision number. Possibly a button which will ask for new job number and copy everything from the active Job No and Revision No to a New Job No and "0" Revision No. The existing record may not be there on all 100 tables for the given Job No and Revision No. If it is there, then copy otherwise ignore for each of the tables.

Note
I have a table "ItemList" which lists all the unique name of the 100 tables. I do not know if this will come handy to build the code.

Looking forward for a solution from the experts. Please let me know if you need any more clarification.

 

[Uncle Gizmo]

>>>Looking forward for a solution from the experts. Please let me know if you need any more clarification<<<

Why 100 Tables? That's like too many!

Can you post the design of 5 tables so we can see if they can be improved?

 

[CJ_London]

From your description, I have to question whether your db is set up properly, 100 'sub tables' seems a bit excessive. However what you want to do is possible, but I would also question


you will need to use VBA in a loop along the following lines. This code isn't tested so check the punctuation!

 dim rst as dao.recordset
 dim newJobNo as long
 dim oldJobNo as long
 dim fieldStr as string
 dim fld as dao.field
  
 newJobno=123
 oldJobNo=33
 set rst=currentdb.openrecordset("SELECT * FROM ItemList")
 while not rst.eof
     fieldStr=""
     for each fld in tabledefs(rst!tblName)
         if fld.name<>"name of autonumberfield" and fld.name<>"version" and fld.name<>"JobNo" then fieldStr=FieldStr & fld.name & ", "
     next
     fieldstr=left(fieldstr, len(fieldstr)-2)
     currentdb.execute("INSERT INTO " & rst!tblName & " (JobNo, Version, " & fieldStr & ") SELECT " & newJobNo & ", 0, " & fieldstr & " FROM " & rst!tblName & " WHERE JobNo=" & OldJobNo)
     rst.movenext
 wend
 set rst=nothing
 set fld=nothing

If your table and field names have spaces or illegal characters such as # or are reserved words you will need to add square brackets otherwise the code will fail.

I presume you have a form to determine newjobno and oldjobno and clearly you will need to change the table and field names to your requirements

 

[myquery]

Yes they are too many. Each table has different fields which is why I could not come with any simplified way of doing it. Each table have its own form which is added as a subform on the Master form. So all the data are tied to the Job No and Rev No. Can you think of any other way of doing it? Everything has worked good so far except copying of new record.

 

[myquery]

I have not been able to successfully apply above code perhaps due to my limited knowledge in VBA. I would appreciate if you could provide a more refined code.

 

[CJ_London]

I have not been able to successfully apply above code

Please clarify what this means in detail

perhaps due to my limited knowledge in VBA

where is your knowledge limited?

I would appreciate if you could provide a more refined code.

Not possible without a lot more information from yourself.

 

[myquery]

CJ, Thanks for your input. I tried to use your code with changes to table name & field name to suit my need. TableDef was identified during compile as unknown function. I rearranged the code the way I know it works to get past that compile error. Then I found that the Select Inset statement that you had helped me with had values for job number and revision number on the select part of the statement and it was not executing. To go past that I started a select statement to get the values for each of the field name that are being extracted by your code and then started adding them to another string that will hold the values—just a copy of your string for holding field names and made the code work a little bit further. Now it is able to insert records on some tables but fails on other with the error. ”Too Few Parameters” . Also the date value when inserted ends up showing up as 12:00:48 AM (beginning of Microsoft time I believe-1900). I have attached the code below and I am sure you can understand my limitations within VB from the code. I am in the process of trying to retrieve data type for each field name and based on datatype add a single quote or leave it alone. Also I have check box fields where I believe I need to CInt() the variable before inserting into SQL. Any pointer in this regard will be greatly appreciated. Thanking you in anticipation.

Public Sub insertdataNew()

Dim dbs As Database
Dim rst As DAO.Recordset
Dim rstfield As DAO.Recordset
 Dim newJobNo As Long
 Dim oldJobNo As Long
 Dim tblname As String
 Dim tdf As DAO.TableDef
 Dim fieldStr As String
 Dim fieldStrval As String
 Dim fld As DAO.Field
 Dim sqlstmt As String
  
 newJobNo = 111
 oldJobNo = 101
 
 Set dbs = CurrentDb()
 Set rst = dbs.OpenRecordset("SELECT * FROM ItemList")
 
 While Not rst.EOF
 tblname = rst.Fields("TBLName"): 'MsgBox (tblname)
 Set tdf = dbs(tblname)
 
 
     fieldStr = ""
     fieldStrval = ""
     For Each fld In tdf.Fields
         If fld.Name <> "ID" And fld.Name <> "RevisionNumber" And fld.Name <> "JobNumber" Then
         fieldStr = fieldStr & fld.Name & ", "
            Set rstfield = dbs.OpenRecordset("SELECT " & fld.Name & " FROM " & tblname & " WHERE JobNumber = " & oldJobNo)
            fieldStrval = fieldStrval & rstfield.Fields(fld.Name).Value & ", "
        End If
     Next
     fieldStr = Left(fieldStr, Len(fieldStr) - 2)
     fieldStrval = Left(fieldStrval, Len(fieldStrval) - 2)
     MsgBox (fieldStrval)
     sqlstmt = "INSERT INTO " & tblname & " (JobNumber, RevisionNumber, " & fieldStr & ") VALUES (" & newJobNo & ", 0, " & fieldStrval & ");"
     MsgBox (sqlstmt)
     dbs.Execute ("INSERT INTO " & tblname & " (JobNumber, RevisionNumber, " & fieldStr & ") VALUES ('" & newJobNo & "', 0, " & fieldStrval & ");")
     rst.MoveNext
 Wend
 Set rst = Nothing
 Set rstfield = Nothing
 Set fld = Nothing
End Sub

 

[CJ_London]

sorry, did not realise you had posted

You seem to be doing something somewhat different from what I was suggesting - please can you put notes in the code to explain each step

in particular, I can't see what you are doing with these two lines

Set rstfield = dbs.OpenRecordset("SELECT " & fld.Name & " FROM " & tblname & " WHERE JobNumber = " & oldJobNo)
fieldStrval = fieldStrval & rstfield.Fields(fld.Name).Value & ", "

also

Set tdf = dbs(tblname)

should be

Set tdf = dbs.tabledefs(tblname)

 

[RainLover]

Scenario:
I have "Master" table with fields "Job No" and "Revision No". Both together is a primary key, so that combination of both cannot be duplicated. I have 100 other tables to be related with referential integrity(+update&delete) to Master for both fields. Apart from Job No and Revision No, all 100 tables have different set of fields which is why I had to come with so many tables. Due to 32 limit rule, I had to come up with workaround method to have all 100 tables in the relationship. So, I created 5 other SubMaster1, SubMaster2, ...., SubMaster5 which are related to Master with relationship with referential integrity (+update&delete). Then I assigned 20 tables to each SubMaster so that 20 tables are related to each SubMaster table. Whenever I create new record in Unit, the new record is generated in each SubMaster using update query for each SubMaster table. I have all the forms and necessary query laid out. The only missing part is being able to duplicate a record. I have limited knowledge in VBA, but I should be able to modify it to address to my requirement.

You have started on the wrong foot.

I have "Master" table with fields "Job No" and "Revision No". Both together is a primary key, so that combination of both cannot be duplicated.

You have succeeded in designing a Primary Key that does not do anything. You could have a dozen fields and join two together which would also give you a Unique Primary key.

If you had a person what would go in the table.

Primary Key AUTONUMBER
FirstName Text
LastName Text
DOB Text
AddressLineOne Text
AddressLineTwo Text
PostalCode Text
MobilePhoneNumber Text

This Table is just fine. One could even add more fields as long as they meet with Normalisation Rules.

So your first thing to do is to read up on Normalisation. This is most important as the method you are currently employing will not allow you to succeed.