Copy multiple fields from one table to another

liquidfusion

Software: Access 2003

Objective: I have two tables in my database. I want to be able to execute code in a button on a form that verifies that a record in table2 exists via user input with 'InputBox'. If the record doesn't exist, then I would like to have the record copied from table1 to table2.

What I have currently done: I currently use 'FindFirst' along with 'InputBox' to verify if a record exists in a table, however, I am unable to figure out the code to switch to another table and check if the record exists there.

Additionally, I don't know how to copy specified fields from one table to another.

If any of this causes any confusion then let me know and I'll be happy to try and better explain.

missinglinq

The first question that needs to be asked is why you would want identical records in two different tables? This pretty much violates concept of a relational database! Perhaps if you could provide us with more comprehensive explanation of what you're trying to do we could provide some guidance.

Welcome to The Scripts!

Linq ;0)>

NeoPa

Each recordset in your code is simply stored in a different variable. Set a recordset up for each table you need to access.
To add a record you can either use a SQL command or the {recordset variable}.AddNew() method.

NB. Unless you're doing some sort of logging process, linking to data rather than simply copying the data across is generally the preferred way of providing access to it (See Linq's post).

liquidfusion

I understand what you are saying about linking the data but this particular situation is rather unique in which the data in table1 is erased and updated every 24 hours and table2 keeps occasional records from table1. This is why I need to verify if a particular record exists in table2 and if it doesn't, copy the record over from table1.

The first question that needs to be asked is why you would want identical records in two different tables? This pretty much violates concept of a relational database! Perhaps if you could provide us with more comprehensive explanation of what you're trying to do we could provide some guidance.

Welcome to The Scripts!

Linq ;0)>

ADezii

Software: Access 2003

Objective: I have two tables in my database. I want to be able to execute code in a button on a form that verifies that a record in table2 exists via user input with 'InputBox'. If the record doesn't exist, then I would like to have the record copied from table1 to table2.

What I have currently done: I currently use 'FindFirst' along with 'InputBox' to verify if a record exists in a table, however, I am unable to figure out the code to switch to another table and check if the record exists there.

Additionally, I don't know how to copy specified fields from one table to another.

If any of this causes any confusion then let me know and I'll be happy to try and better explain.

Here's some code based on an Employees2 Table, which would parallel your Table2 scenario. This code should do everything that you had requested in your Post. I'll just Post the code for now, since it is getting past my bedtime, but should you have any questions whatsoever, please feel free to list them, and either myself or one of the other fine gentlemen (yes NeoPa, you too! (LOL)), will be happy to assist you.

Expand|Select|Wrap|Line Numbers

 
Dim MyDB As DAO.Database

Dim MyRS As DAO.Recordset

Dim varLastName As Variant

Dim strMsg As String

Dim intResponse As Integer
 
Set MyDB = CurrentDb

Set MyRS = MyDB.OpenRecordset("Employees2", dbOpenDynaset)
 
'varLastName = Trim(InputBox("Enter a Last Name to Search For."))

                            'OR

'Forgive my confusion, but why not simply read the value from an

'existing Text Box on the Form

varLastName = Me![txtLastName]
 
If Len(varLastName) = 0 Then Exit Sub
 
With MyRS

 If .RecordCount > 0 Then

   'Traverse the Recordset to populate it, if it contains Records

   .MoveLast

 End If

  .FindFirst "[LastName]='" & varLastName & "'"

    If .NoMatch Then

      strMsg = "No records found with a Last Name of " & varLastName & _

              ". Do you wish to Add " & varLastName & " to the Database?"

      intResponse = MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton1, "Add Record")

        If intResponse = vbYes Then

          .AddNew

            ![LastName] = Me![txtLastName]

            ![FirstName] = Me![txtFirstName]

            ![EmployeeID] = Me![txtEmployeeID]

          .Update

        End If

    Else

      MsgBox varLastName & " already exists in Table Employees2."

    End If

End With
 
MyRS.Close

Set MyRS = Nothing

liquidfusion

I really appreciate your post, it helped quite a bit. Is there an easier way to copy a complete record from one table to another? In the example you gave, you copied 3 fields from the record and in my example I have well over 3 fields that would need to be copied and it would require quite a bit of code to copy all of the fields in the record. Is there a function that will copy a complete record instead of needing to copy the record one field at a time?

NeoPa

There should be a .Fields() collection for each recordset. You could loop through them all transferring each value across by index or even by name if preferred.

Copy multiple fields from one table to another

Post your reply

Similar topics