Create new row in table

[OldBirdman]

I want to have an "Edit" form=fEditLoc for rows in a subform=subfSho wLoc. Once the form opens, I want to be able to create a new record in the table=tLoc which I will then edit.

Or to put it another way, I want to edit a record, and then do a "Save As" to a new record. Seems simple, but I can't keep the data in all the fields when I move the form to the NewRecord position. So how do I do this?

[ADezii]

@OldBirdman
Hello OldBirdman, try this out and see what happens:

'Select Current Record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
 
'Copy Current Record
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
 
'Paste Append Record
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

[OldBirdman]

Thank you. These tools will work for this.

Just curious, but I think somewhere I read that DoMenuItem was to be avoided if possible. The relative position of the items may change, or something. Is this something I should be concerned about?

[OldBirdman]

I think I replied too quickly. I would like my "Save As" to leave the original record unchanged, as happens if we edit a file in say NotePad, make some changes, and decide to create a new file. We SaveAs and continure editing. At the end, the original file is unchanged, and the new file has the changes from before the SaveAs and after it (presuming a final Save before closing).

Line #2 of the code suggested in Post #2 saves the record. If I select before any changes are made, it is un-selected when form becomes dirty.

I could save all the fields using each control's OldValue property. Then I select the record, allowing Access to save the changed record. On FormClose, I would have to move back to the original record, and restore the original values and save it. Procedures would have to be recursive, because I might want to create a series of records, each a slight change from the previous one.

1. Call form
2. Make changes, say txtField = Test 1
3. "Save As" to create a new record
4 Change the 1 to a 2, avoiding typing the word "Test" and any other common data fields
5. "Save As" to create a new record
6. ... Repeat as necessary

This seems clumsy, crude, and amaturish. Like most of my code, I'm afraid.

[ADezii]

@OldBirdman
I do have a solution that I based on the [FirstName] and [LastName] Fields of the Employees Form in the Northwind Sample Database. Make changes to the First and Last Name Fields, then execute the following code within the context of the Form, making sure to previously Declare the Globals. An exact Duplicate of the Record will be created then Appended. The Original Record will revert back to its Original State prior to the changes. I know it is a little convoluted, but for now, see if it will do until a better solution comes along. I tested it and it actually does work! (LOL)!

Dim strSQL As String
 
'*******************************************************************************************
'Store Original Values in Public Variables  Declared in a Standard Code Module
glngEmployeeID = Me![EmployeeID]
gstrFirstName = Me![FirstName].OldValue
gstrLastName = Me![LastName].OldValue
'*******************************************************************************************
 
'*******************************************************************************************
'Define the SQL Statement that will restore the Original Values to the very Record from which
'changes were made, and a Duplicate created
strSQL = "UPDATE Employees SET Employees.FirstName = '" & gstrFirstName & "', " & _
         "Employees.LastName = '" & gstrLastName & "' Where Employees.[EmployeeID] = " & _
          glngEmployeeID
'*******************************************************************************************
 
'*******************************************************************************************
'Duplicate the Record with modifications
'Select Current Record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
 
'Copy Current Record
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
 
'Paste Append Record
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
'*******************************************************************************************
 
'*******************************************************************************************
'Restore Original Values to the Original Record
CurrentDb.Execute strSQL, dbFailOnError
'*******************************************************************************************
 
'P.S. - At some point you 'MUST' Reset the Public Variables before duplicating this Procedure

[OldBirdman]

OK, Good. I never used an UPDATE query pefore, so that was my missing piece. I don't get the Global Variables. Why? strSQL = "UPDATE ..." & Me![EmployeeID].OldValue & "..." & Me![FirstName].OldValue & ..... saves the data locally. It only has to last from line 13 to line 32.

Thank you for your time & effort.

[ADezii]

@OldBirdman
Anytime, sorry for the confusion, strSQL can be Declared Locally as is.

[OldBirdman]

This doesn't work, and I'm going nuts trying to figure why.

I have 2 tables, linked in Relationships. I'm trying to create a simple form to edit 2 of the fields in the child table, and be able to start an edit and then do a "Save As".

Tables
Table 1 Name = tPhotographers
Key as Autonum (Random) = PK
...
 
Table 2 Name = tPhotographerLocations
Key as Autonum (Random) = PK
Photographers  as Long 'Points to Key in table = tPhotographers
LastDate       as Date
Type           as String
Location       as String

The form has 5 textboxes for the 5 fields in the table, named txtXXX where XXX is the name in the table. For testing, this form is being opened from the DataBase (Forms) window, and no other forms are open.

Private Sub Form_Load()
'Photographers key = 782320442
    Me.RecordSource = "SELECT * FROM tPhotographerLocation WHERE Key=1251495069"
End Sub 'Form_Load
 

I have 1 record (Key=1251495069 ) a test record with Photographers pointing to a test record. Everything displays correctly at this point.

In txtLocation, I change the current value "Test 1" to my new value "Test 2". I press button = cmdSaveAs to create a new record, and to restore the initial record to its original state ("Test 1").

Private Sub cmdSaveAs_Click()
Dim strSQL As String
Dim iiHoldPtr As Long
 
    iiHoldPtr = txtPhotographer
    If Me.Dirty Then
        'Build SQL to restore original record
        strSQL = "UPDATE tPhotographerLocation " & _
                "SET Type='" & Nz(txtType.OldValue, txtType) & _
                "', Location='" & Nz(txtLocation.OldValue, txtLocation) & _
                "' WHERE Key=" & txtKey
    Else
        strSQL = ""
    End If
 
    'Select the record now being edited
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'Copy the Record
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    'Paste append record to create new record
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
    Me.Dirty = False 'Force Save for test purposes only
    txtPhotographer = iiHoldPtr
    Me.Dirty = False 'Force Save for test purposes only
    'Restore original record (if necessary)
    If strSQL <> "" Then CurrentDb.Execute strSQL, dbFailOnError
End Sub

With a breakpoint at Line 22, I have the following in Immediate (window). First, I did a cntl+V to paste the clipboard into Immediate.

txtKey  txtPhotographer txtLastDate txtType txtLocation
1251495069  782320442   4/8/2009    X   Test 2

I then printed some controls and variables:

?txtKey, txtPhotographer, txtLastDate, txtType, txtLocation
 1976622305    0            Null          X             Test 2
 
?me.Dirty 
True

Note that the foreign key txtPhotographer is 0 and invalid as there is no key=0 in table=tPhotogra phers. The date field is Null, also invalid, but the other fields are correct.
Attempting to run Line 22 gets me

Run-time error '3201':  You cannot add or change a record because a related record is required in table 'tPhotographers'. 

which is because txtPhotographer is now 0 and invalid. If I skip Line 22, and run Line 23, Line 24 also works. I really don't want to do this, because now UnDo in not possible, but for now, OK.
Line 26 restores the original record, and closing the form leaves me with 2 records, but the date is null in the 2nd record.

If I now change txtLocation to "Test 3" and continue, more problems occur. So I need to understand why this part doesn't work, rather than just force it with more code like Line 23.
A real-life example would be if a photographer visits a city briefly, the location might be entered as txtLocation =

AnyCity, SomeState, Country

If he returns for a more extensive visit, he might want to edit this, adding local locations such as

Central Park, AnyCity, SomeState, Country
Waterfront Park, AnyCity, SomeState, Country
Courthouse Park, AnyCity, SomeState, Country
Civic Center, AnyCity, SomeState, Country
...
 

Line 21 is the code that is producing incorrect results. Something is very wrong with this approach. Giving up the office clipboard is an undesired side-effect of this method, but I could live with it if it is the only way. Can it be fixed?

It seems like the code might be:

rst = tPhotographerLocations
rstclone = tPhotographerLocations.clone
rstclone = set to current record
rst = set to new record
assign rst from rstclone
rstclone.undo

Any ideas here?

[ADezii]

@OldBirdman
Can you send me a Copy of the Database if I give you my E-Mail Address in a Private Message?

[OldBirdman]

That presented was a tiny piece of a larger system, including linked tables. I'll see if I can isolate this into a test database, as the problem is fairly simple.

When I've sent my problems to others, they don't recreate on their machines. That implies that mine are corrupt, although I run two different versions of Office (2000 and 2002). Both machines, 2 versions of a program produce the same symptoms.

I am going to try to figure how to do this with direct manipulation of the tables, as I hinted at the end of my previous post.

Thank you for your effort and concern.