Create new row in table

[OldBirdman]

I want to have an "Edit" form=fEditLoc for rows in a subform=subfShowLoc. 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=tPhotographers. 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.

[ADezii]

@OldBirdman
Try giving this Logic, or a modification thereof, a try:

1. Declare the following Global/Public Variables in a Standard Code Module:
   

   'To avoid the problem of potential Null Values, all
   'Variables were Declated as Variants
   Public gstrType As Variant
   Public gstrLocation As Variant
   Public glngKey As Long
   Public glngPhotographer As Long
   Public gdteLastDate As Variant

2. Execute the following code within the context of your Form:
   

   Dim strSQL As String
   Dim MyDB As DAO.Database
   Dim rstTest As DAO.Recordset
    
   Set MyDB = CurrentDb
   Set rstTest = MyDB.OpenRecordset("tPhotographerLocation", dbOpenDynaset, dbAppendOnly)
    
   '*******************************************************************************************
   'Store Original Values in Public Variables  Declared in a Standard Code Module
   gstrType = Me![txtType].OldValue
   gstrLocation = Me![txtLocation].OldValue
   glngKey = Me![txtKey]
   glngPhotographer = Me![txtPhotographer].OldValue
   gdteLastDate = Me![txtLastDate].OldValue
   '*******************************************************************************************
    
   '*******************************************************************************************
   'Duplicate the Record with modifications using DAO, avoiding DoMenuItem, and creating
   'a Related Record
   With rstTest
     .AddNew
       ![Type] = Me![txtType]
       ![Location] = Me![txtLocation]
       ![ptrPhotographers] = Me![txtPhotographer]
       ![LastDate] = Me![txtLastDate]
     .Update
   End With
    
   rstTest.Close
   Set rstTest = Nothing
    
   '*******************************************************************************************
    
   '*******************************************************************************************
   'Restore Original Values to the Original Record
   Me![txtType] = gstrType
   Me![txtLocation] = gstrLocation
   Me![txtLastDate] = gdteLastDate
   '*******************************************************************************************
    
   '*******************************************************************************************
   'Clear all Public/Global Variables
   gstrType = Null
   gstrLocation = Null
   glngKey = 0
   glngPhotographer = 0
   gdteLastDate = Null
   '*******************************************************************************************
    
   Me.Requery

3. Let me know how you make out.

[NeoPa]

Without wishing to interrupt what is already a fairly involved conversation, have you considered using the Default attribute of the controls on the form you want to use to save the record away from?

There are techniques that can make this process less laborious (For Each Control etc). Let me know if this is an approach you would consider using.

[OldBirdman]

The problem is to open a form to edit a row of a table. During changing the fields, using this form, I may:
1) Save the changes - Automatic at form close, or command button. Easy
2) Undo the changes - Command button or ESC Key. Easy "DoCmd.UnDo"
3) Move the changes to a new record, restore original record, and continue where 1,2,3 are again possibilities. I'll look at any ideas here.

[NeoPa]

Well, I don't see much benefit in progressing with this unless you feel it's a viable solution. I know you're always open to any suggestions, but if this doesn't look like a decent solution, I won't push it.

[OldBirdman]

ADezii has supplied such a solution to me personally, but I need more time to grasp all the implications. As I actually have 3 different places this would be useful, I will be trying to write a function that will work in the general case. Leave it alone for now, and I will repost in this thread when I know more.

[FishVal]

Some code to illustrate suggestions made by ADezii and NeoPa.

'[keyID] is PK, Autonumber field in the form recordsource
 
Private Sub btn_Click()
 
    Dim rs As DAO.Recordset
    Dim lngID As Long
 
    'get an unbound copy of the form recordset
    Set rs = Me.RecordsetClone
 
    With rs
 
        .AddNew
 
        'skip possible errors related to unbound or not updateable form controls
        'and read-only recordset fields
        On Error Resume Next
        For Each ctrl In Me.Controls
            'copy control value to recordset field the control is bound to
            'IsNull() check is used since silly DAO.Recordset allows to modify Autonumber field
            If IsNull(.Fields(ctrl.ControlSource)) Then .Fields(ctrl.ControlSource) = ctrl.Value
        Next
        On Error GoTo 0
 
        'remember PK of created record
        lngID = !keyID
 
        .Update
        .Close
 
    End With
 
    Set rs = Nothing
 
    With Me
        'undo changes to the current record
        .Undo
        'form requery
        .Requery
        'go to the record created above
        .Recordset.FindFirst "keyID=" & lngID
    End With
 
End Sub
 

[ADezii]

Nice Hybrid approach, FishVal.