Add, Delete & Duplicate Record

Cosmos75 · Nov 6, 2003

I have leared that the code Access 97 generates for a delete button is obsolete.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48562

How about the button for Adding a new record?

Code:

    DoCmd.GoToRecord , , acNewRec

How about the button for Duplicate Record?

Code:

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 [color=green]'Paste Append[/color]

00

I've seen some thread that do this with DAO or ADO.

Duplicate / Copy / Clone Recordset (A2K)
http://www.access-programmers.co.uk...?s=&threadid=55971&highlight=Duplicate+Record

How to create new record with an old record - VBA (ghudson)
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=34060
- I think this will work but I am wondering is what is the code that Access 97 generates doing?

Cosmos75 · Nov 7, 2003

Magical Add New Record Button...copy info from form and subform.

ghudson's code will work but not what for what I want to do (or at least I haven't figured out HOW to do it with ghudson's code).

I have a three-level form (frmMain, fsubMain, fsub2Main)).

Let's say for example (this isn't what I am doing but has the same structure);
frmMain holds a Course info.
fsubMain (subform to frmMain) holds Test info (Date of Test, Subject of Test, Chapters, Number of Questions...).
fsub2Main (subform to fsub2Main) holds Student Scores (Student, Score, ...).

One Course has Many Tests
One Test has Many Scores

I can get ghudson's code to add a new Test with all the previous info but not for the Student Scores.

I would like to be able to add a new test using an add button but want to the new Test to keep all the Test Info AND Student Score info that is currently being shown (before I press this magical add button). The reason for this is that the information doesn't change much for each Test for a given Course, mostly just the date of Test, Students taking Test, and Student Scores but all the other fields CAN be diferent. Being able to do this would sgnificantly reduce the amount of time it took to enter records since it would mostly require the addition or deleteion of a few records in fsub2Main.

Is this possible?...
Can this button exist in reality or merely in the realm of fantasy?

Cosmos75 · Nov 11, 2003

My own solution using an append query

Changing form names to make it easier to follow
frmMain = frmCrouse
fsubMain = fsubTest
fsub2Main = fsubStudent

Now have this code on fsubTest

Code:

Private Sub cmdNew_Click()
Dim Course As Variant
Dim Test As Variant


Course = Me.CourseID
Test= Me.TestID

‘added two textboxes to hold current CourseID and TestID
Me.txtJobIDOLD = Job
Me.txtRunIDOLD = Run


DoCmd.GoToRecord , , acNewRec

If IsNull(Course) = False Then
     Me.CourseID = Course
End If

If IsNull(Test) = False Then
     Me.TestID = Test
End If

DoCmd.SetWarnings (False)

DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext

DoCmd.OpenQuery ("qApndNewTest")

DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext

DoCmd.SetWarnings (True)

End Sub

SQL for qApndNewTest ;
INSERT INTO tblStudents( TestID, StudentID )
SELECT [Forms]![frmCourse]![fsubTest].[Form]![fsubStudents] AS Expr1, tblStudent.StudentID
FROM tblStudent
WHERE (((tblFuelUsage.RunID)=[Forms]![ frmCourse]![ fsubTest].[Form]![txtRunIDOLD]));

AM not sure why, but I need to have the two occurances of

Code:

DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext

before and after

Code:

DoCmd.OpenQuery ("qApndNewTest")

for it to work right.

May not be the best way to do it, but it works as far as I can tell!!

Cosmos75 · Nov 17, 2003

Changed code from

Code:

DoCmd.SetWarnings (False)

[color=red]DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext[/color]

DoCmd.OpenQuery ("qApndNewTest")

[color=red]DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext[/color]

DoCmd.SetWarnings (True)

to

Code:

[color=red]DoCmd.RunCommand acCmdSaveRecord[/color]

DoCmd.SetWarnings (False)

'DoCmd.GoToRecord , , acPrevious
'DoCmd.GoToRecord , , acNext

DoCmd.OpenQuery ("qApndNewRunTest")

'DoCmd.GoToRecord , , acPrevious
'DoCmd.GoToRecord , , acNext

DoCmd.SetWarnings (True)

Add, Delete & Duplicate Record

Cosmos75

Registered User.

Cosmos75

Registered User.

Cosmos75

Registered User.

Cosmos75

Registered User.

Similar threads

Users who are viewing this thread