Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Atlanta GA

    Question Unanswered: Copy and Move record from table to table problem

    Functionally I want to take a record from this table update it with the final information and then transfer it to another table Making it "Completed" and seperate.

    I have an idea on how to get most of the code to work but am too new to VB (don't Know how to code from scratch).

    What is a good way to code this.

    I wanted to start with this code selecting my record

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[WO] = '" & Me![Combo51] & "'"
    Me.Bookmark = rs.Bookmark

    Is there any code I can place after this to copy the selected record and the place it into another table. Maybe create an object and copy that object append it to the other table????

    Does anyone have any ideas? Any help would be appreciated I am due to take my VB course in July bu this project can't wait.

    Thanks for reading...

  2. #2
    Join Date
    Mar 2002
    Calgary, Alberta

    A Class-y Solution

    Hi Jimbo 62,

    The easiest way to solve this problem is to create your own custom class. The subroutines and functions you write in your class will become the methods you use to: read the data from the record you want to use as the basis for your transfer; add a new record to the table you want to transfer this data to, and; delete the record from the original table.

    Go to the Modules window and click on Insert/Class Module. When the input screen is visible, click on save and give your class a name of your choosing, say clsTransfer.

    In the declarations section of this class, just under Option Compare Database type in the following code:

    ' Define layout of incoming record: match the field names in your table.

    Type RecIn
    VariableName01 as Datatype (e.g. String, Long or whatever)
    VariableName02 as Datatype
    VariableName03 as Datatype
    End Type

    ' Define the layout of outgoing record: match it's table field names

    Type RecOut
    VariableName01 as Datatype (e.g. String, Long or whatever)
    VariableName02 as Datatype
    VariableName03 as Datatype
    End Type

    ' Having defined the layout of the incoming and outgoing records by
    ' the Type clauses, you now set up two variable structures to hold
    ' the information you're going to fill in. By dimensioning as Private they
    ' will be available to all the procedures in your class until you destroy it

    Private RecordIn as RecIn
    Private RecordOut as RecOut
    Private Success as Boolean
    Private datDate as Date
    Private datTime as Date

    Public Property Get RecordOp() As Boolean
    RecordOp = Success
    End Property

    Public Property Let RecordOp(ByVal BBB As Boolean)
    Success = BBB
    End Property

    ' Since I'm working on a medical database I will simply cut and paste
    ' some routines from a simple class I'm building; and modify it enough to
    ' give you the direction will have to take to modify it further to suit
    ' your own purposes

    Public Sub GetDoctor(RecordID as Long)

    Dim rstGet As ADODB.Recordset
    Set rstGet = New ADODB.Recordset

    rstGet.CursorType = adOpenForwardOnly
    rstGet.LockType = adLockReadOnly
    rstGet.ActiveConnection = CurrentProject.Connection

    strSQL = "SELECT * " & _
    "FROM [01000_DOCTOR] " & _
    "WHERE [01000_DOCTOR].DoctorID = " & RecordID & ";"

    rstGet.Open _

    With rstGet

    If Not .BOF And Not .EOF Then
    RecordIn.Variable01 = .Fields("RecordIn.Variable01")
    RecordIn.Variable02 = .Fields("RecordIn.Variable02")
    RecordIn.Variable03 = .Fields("RecordIn.Variable03")
    RecordIn.Variable04 = .Fields("RecordIn.Variable04")
    RecordOp = True
    RecordOp = False
    End If
    End With

    Set rstGet = Nothing

    End Sub

    Public Sub AddDoctor(RecordID as Long)

    Dim rstAdd As ADODB.Recordset
    Set rstAdd = New ADODB.Recordset

    datDate = Date
    datTime = Time()

    rstAdd.CursorType = adOpenKeyset
    rstAdd.LockType = adLockPessimistic
    rstAdd.ActiveConnection = CurrentProject.Connection

    ' When this SQL statement is executed it will position your cursor
    ' at the end of the 02000_DOCTOR table, ready for insertion. In
    ' effect we are asking the DB engine to locate a record that has
    ' its DateAdded field set to today and it's TimeAdded field set to now.
    ' Given that this combination of date and time have never occured
    ' the engine will fail to find a record, hence run you off the end of
    ' the table (which is exactly what we want).

    ' Since any commercial system you build will benefit from an audit
    ' trail, adding the fields DateAdded / TimeAdded to every table you
    ' make will provide you with the foundation for it in the future, even
    ' if you do not take much advantage of it right away.

    strSQL = "SELECT * " & _
    "FROM [02000_DOCTOR] " & _
    "WHERE [02000_DOCTOR].DateAdded = " & "#" & datDate & "#" & _
    " AND [02000_DOCTOR].TimeAdded = " & "#" & datTime & "#"

    rstAdd.Open _

    With rstAdd

    ' Expect the following condition to be true. The date and time
    ' are set equal to the moment this event is added to the system.
    ' Date and time are unique, so the record we add will be unique.

    If .BOF Or .EOF Then
    .Fields("DateAdded") = Date
    .Fields("TimeAdded") = Time()
    .Fields("RecordOut.Variable01) = RecordIn.Variable01
    .Fields("RecordOut.Variable02) = RecordIn.Variable02
    .Fields("RecordOut.Variable03) = RecordIn.Variable03
    .Fields("RecordOut.Variable04) = RecordIn.Variable04

    ' This next statement assumes you have an autonumber field called
    ' DoctorID on the table you are transfering 'to' and places the data in
    ' the variable name that you passed into the sub. By passing in a blank
    ' value you can actually retrieve the primary key of the record you just
    ' added (in effect, making this sub a kind of weak-function)

    RecordID = .Fields("DoctorID")
    RecordOp = True
    RecordOp = False
    End If
    End With

    Set rstAdd = Nothing

    End Sub

    Public Sub DeleteOldDoctor(RecordID as Long)

    Dim rstDel As ADODB.Recordset
    Set rstDel = New ADODB.Recordset

    rstDel.CursorType = adOpenKeyset
    rstDel.LockType = adLockPessimistic
    rstDel.ActiveConnection = CurrentProject.Connection

    strSQL = "SELECT * " & _
    "FROM [01000_DOCTOR] " & _
    "WHERE [01000_DOCTOR].DoctorID = " & RecordID & ";"

    rstDel.Open _

    If rstDel.RecordCount = 1 Then
    rstDel.Delete adAffectCurrent
    RecordOp = True
    RecordOp = False
    End If

    Set rstDel = Nothing

    End Sub

    I think this class will work although it's always possible I have forgotten something. To finish the solution we go back to the form where you will (presumeably) display the original record. Since your Transfer class
    needs to have a record id in order for the Get routine to operate
    correctly I will assume the record you want to update and transfer is
    already available to you.

    There are a variety of ways to do this. I will assume the simplest case.
    Add a command button to the form called: cmdTransfer

    In the OnClick Event, put the following code

    Dim Transfer as Transfer
    Set Transfer = New Transfer
    Dim NewRecordID as Long

    Transfer.GetDoctor MyRecordID
    If Transfer.RecordOp = True Then
    Transfer.AddDoctor NewRecordID
    If Transfer.RecordOp = True Then
    Transfer.DeleteOldDoctor MyRecordID
    If Transfer.RecordOp = True Then
    MsgBox ("Record Transfered")
    MsgBox ("Describe Error to user")
    End If
    End If
    End If

    Set Transfer = Nothing

    If you try this approach I think you will like the results. For starters,
    by placing your record management routines in a class you can have
    them at your disposal in various places in your system - but if they
    ever require updating (e.g. the users decide they want more fields
    added to the record) they only have to be updated and recompiled in
    one place.

    You can also make the class significantly MORE generic (e.g. by passing in a table name you can have these routines perform this
    same function on virtually ANY TWO TABLES. The incoming and outgoing table names would merely be properties you provide values to BEFORE you execute the methods. Just to finish this thought, your typical strSQL variable would now look like this:

    strSQL = "SELECT * " & _
    "FROM [" ThisTable & "] " & _
    "WHERE [" & ThisTable & "].DoctorID = " & RecordID & ";"

    By adding Get/Let pairs for the primary key as well, the same statement might look like this:

    strSQL = "SELECT * " & _
    "FROM [" ThisTable & "] " & _
    "WHERE [" & ThisTable & "]." & ThisKey & " = " & RecordID & ";"

    I am sure you get the idea.

    Hope this helps.

  3. #3
    Join Date
    Sep 2001
    Chicago, Illinois, USA
    Why not just:

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qap_RecordFromFirstTableToSecondTable"
    DoCmd.OpenQuery "qdl_RecordCopiedFromFirstTableToSecondTable"
    DoCmd.SetWarnings True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts