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"
set rs = nothing
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!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..)
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)
set rx = nothing
set rs = nothing
docmd.close acform, "MyPatientDataEntryFormName"
docmd.openform "MyDataUpdatedDataEntryFormName",,"[RecordID] = " & NewRecID & "" (again, RecordID is the autonumber field in the DataUpdated table.)
- 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 20:54.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)