Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: saving a record from a table into another table

    I have 2 tables: patientdata and dataupdated

    patient data contains all my database
    dataupdated is where i need to store my updated records

    i need to copy 4 fields from the database to my dataupdated table
    i designed a form and was able to find the record i need to copy from the database.
    How can i automatically fill the fields in the form with the record so that i can save it in dataupdated table?
    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database is this? mysql? db2? informix? sybase? oracle?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    i am using access and it is a DBF file

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i'll move your question to the MS Access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What I might do is make a query (or write some code) which copies the current record (ie. the 4 fields) on the current form (recordsource being based on PatientData table) to the DataUpdated table and then goes to that new record on another form (where the recordsource of that form is based on the DataUpdated table.)

    Example of code to do this might be (this is from memory so there may be syntax errors):

    (note - this ADO coding)

    Dim RecID as variant
    RecID = me!RecordID (ie. RecordID is the autonumber field in the table.)
    dim rs as adodb.recordset (for patientdata table)
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from patientdata where RecordID = " & RecID & ""
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    msgbox "Error - No RecordID"
    rs.close
    set rs = nothing
    exit sub
    else
    dim rx as adodb.recordset (for dataupdated table)
    set rx = new adodb.recordset
    strSQL = "Select * from dataupdated"
    rx.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rx.addnew
    rx!FieldX = rs!FieldX (Field1 you want to copy to the dataupdated table)
    rx!FieldY = rs!FieldY (Field2 you want to copy to the dataupdated table)
    rx!FieldZ = rs!FieldZ (etc..)
    ....
    rx.update
    Dim NewRecID as variant
    NewRecID = rx!RecordID (store new autonumber value in dataupdated table to go to that record when opening the new form - RecordID is the autonumber field in the DataUpdated table)
    rx.close
    set rx = nothing
    rs.close
    set rs = nothing

    docmd.close acform, "MyPatientDataEntryFormName"

    docmd.openform "MyDataUpdatedDataEntryFormName",,"[RecordID] = " & NewRecID & "" (again, RecordID is the autonumber field in the DataUpdated table.)

    end if

    - You would then trigger this code to fire either with a command button or after a certain event when you want the copying process to the DataUpdated table to happen.
    Last edited by pkstormy; 02-28-09 at 21:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Feb 2009
    Posts
    3
    I feel so stupid because it is the first time i work with codes. i
    will try to explain again:
    Table 1: patientdata
    fields: case-nbr, date, name, recordid

    Table2: dataupdated
    fields: case-nbr, date, name

    The search should be done based on the case-nbr not on the recordid. I might find more than one record for the same case-nbr. I need then to choose the record per date. afer selecting the record, it has to be updated to the fields in the form: frmdataupdated and to be saved later on to the table dataupdated

Posting Permissions

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