Problems attempting to add new record to table

[GraeagleBill]

I have a form with RecordSource "tblInvNew", which is empty and is identical in structure to table "tblInv". All of the controls in the form, where applicable, are bound to the same table names as are defined in the two tables. The user has the option of either saving or canceling. When the user opts to SAVE, I've attempted to update the table "tblInv" thus:

Private Sub lblSAVE_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Okay, everything required to enter a new record has been satisfied, copy fields to our main table.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
If strMode = "New" Then
    CurrentDb.Execute "INSERT INTO tblInv SELECT * FROM tblInvNew", dbFailOnError
    CurrentDb.Execute "DELETE * FROM tblInvNew", dbFailOnError
End If


DoCmd.Close acForm, "frmItemDescriptor", acSaveYes
End Sub

I get error 3022 and I've not been able to determine why. So, I changed the code to below to run in debug looking for what might be the culprit, but it too gets the 3022 error when I execute the ".Update" statement.

Private Sub lblSAVE_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Okay, everything required to enter a new record has been satisfied, copy fields to our main table.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*


Dim rsSav As DAO.Recordset
Dim strTemp As String


If strMode = "New" Then       'Current RecordSet is QInvNew
    Set rsSav = DBEngine(0)(0).OpenRecordset("tblInv")
    With rsSav
    
        .AddNew
        !Description = Me.tbItemDescription
        !Keep = Me.chkKeep
        !Donate = Me.chkDonate
        !Sold = Me.chkSold
        !Sell = Me.chkSell
        !Comment = Me.tbComment
        !Im1 = Me.tbImage1
        !Im2 = Me.tbImage2
        !Im3 = Me.tbImage3
        !Im4 = Me.tbImage4
        !Im5 = Me.tbImage5
        !Im6 = Me.tbImage6
        .Update
        
    End With
    
    rsSav.Close
    Set rsSav = Nothing
    CurrentDb.Execute "DELETE * FROM tblInvNew", dbFailOnError
End If


DoCmd.Close acForm, "frmItemDescriptor", acSaveYes
End Sub

The 3022 error suggests some sort of duplicate condition, but that makes no sense to me simply adding a record. And yes, both tables are indexed on primary autonumber keys. Any ideas what's causing the conflict?

[Welshgasman]

Without seeing your DB (or at least enough to see the issue) you are asking the impossible?
Perhaps supply all the fields for the Update sql?, but as your recordset does not work, it appears there is more to this than you are aware?

All this

  !Im1 = Me.tbImage1
        !Im2 = Me.tbImage2
        !Im3 = Me.tbImage3
        !Im4 = Me.tbImage4
        !Im5 = Me.tbImage5
        !Im6 = Me.tbImage6

shows a non optimized db, but that would not be your problem here.

So your question becomes a 'how long should my piece of string be', when we have no clue as to what the string is meant to do?

[GraeagleBill]

Here's table "tblInvNew" in the front end of the DB.


And here's table "tblInv" in the backend:


Table "tblInvNew" has a single record that I want to move to "tblInv".

BTW, what is it that you conclude my DB is not optimized?

Maybe this will help. The form at Open:


The form poised for a SAVE:


More information but not necessarily pertinent to the current issue. The form is multi-functional, i.e., if OpenArgs contains a number, it is taken to be the ID of a record in the table "tblInv" and the form's recordsource is set to that single record for the purpose of updating.

[June7]

Multiple similar name fields (Im1, Im2, etc) indicates a non-normalized structure. Normalization would call for a related table where each ImX entry is a record, not a field.

Why bother with temp table? "Moving" records is usually bad design. Bind form to tblInv and abort record commitment if user decides not to save.

[GraeagleBill]

abort record commitment

I like it! If I knew how to do that it would have been my first choice. How do I abort when user chooses "CANCEL".

Ah! I see what you mean about ImX entry.

GPT suggests this, would your agree?

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
    If Me.Dirty Then Me.Undo
    If Me.NewRecord Then Me.Undo
End Sub

I would do that conditionally of course.

[Welshgasman]

When you decide on Select *, you are including the autonumber field?
No idea as to why the recordset does not work, but then I have never used DBEngine(0)(0)

[NoellaG]

If you try to load an image into an OLE field, you can use a stream object to read the binary data. Code example from Copilot:

Sub SaveImageToDatabase()
    Dim conn As Object ' ADODB.Connection
    Dim rs As Object ' ADODB.Recordset
    Dim stream As Object ' ADODB.Stream
    Dim filePath As String
    Dim dbPath As String

    ' Path to the Access database
    dbPath = "C:\Path\To\Your\Database.accdb"
    
    ' Path to the image file
    filePath = "C:\Path\To\Your\Image.jpg"
    
    ' Initialize ADO objects
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set stream = CreateObject("ADODB.Stream")
    
    On Error GoTo ErrorHandler
    
    ' Open the database connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    
    ' Open the recordset (replace 'YourTable' and 'ID' with your table and primary key column)
    rs.Open "SELECT * FROM YourTable WHERE ID = 1", conn, 1, 3 ' adOpenKeyset, adLockOptimistic
    
    If Not rs.EOF Then
        ' Open the image file as a binary stream
        stream.Type = 1 ' adTypeBinary
        stream.Open
        stream.LoadFromFile filePath
        
        ' Save the binary data to the OLE Object field (replace 'ImageField' with your field name)
        rs.Fields("ImageField").Value = stream.Read
        
        ' Update the record
        rs.Update
        MsgBox "Image saved successfully!"
    Else
        MsgBox "Record not found!"
    End If

Cleanup:
    ' Clean up resources
    If Not rs Is Nothing Then rs.Close
    If Not conn Is Nothing Then conn.Close
    If Not stream Is Nothing Then stream.Close
    Set rs = Nothing
    Set conn = Nothing
    Set stream = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description
    Resume Cleanup
End Sub

[GPGeorge]

Here is a link to a series of blog posts explaining the problem of Repeating Fields and how to correct it. It refers here to the use of multiple image fields in a table.

I see that you are not actually storing images, though. Your fields are defined as ShortText, so they can't be the actual images. They would seem to be the path the external images, which is preferred.

I agree that this design using what amounts to a temp table is redundant. The problem there is that you want to allow a user to abort entry of a new record if they change their mind or something. That's partly a workflow issue. Why and when would that actually occur? Under what circumstances would a user enter some of the data for a new record and then decide not to save it? Explaining that might help someone offer a more efficient method.
I'm also wondering if data validation on the attempted data entry would be useful. Use the form's BeforeUpdate event to check the values in various controls on the form to ensure they meet requirements before allowing a save to occur. Again, we'd need more detail to offer suggestions about doing so.

[Micron]

I'm guessing that the image fields only contain the image file path since they are text fields. Trying to insert a record into a table with a wildcard means you will include indexed fields such as autonumber pk's. That's probably what's raising the error and I mention that for future consideration because I agree - don't shift records around. As for the issue of the tables not being optimized, what if you ever want to include an additional image? Bad enough that you will have to redesign your form. You'll have to redesign your tables, queries and code as well.

[June7]

Where does user choose "CANCEL" - is this a button on form? What is its code? Did you try the Unload code - is it triggered? What do you want to happen when user clicks CANCEL - should the form stay open but clear the controls?

As for normalizing the image data, if you want to accept empty fields and future possibility of having to add fields for more images, stick with what you have.

[GraeagleBill]

Hi June,
I took your council and dropped all the nonsense with temporary table and the MOVE approach. The RecordSource for the form is now "tblInv", the entire set. Here's the opening of the form:

Private Sub cmdNewItem_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  User wants to add a new item to the inventory.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
DoCmd.OpenForm "frmItemDescriptor", , , , acFormAdd, acDialog
End Sub

DataEntry = YES and AllowAdditions = YES

However, when the form closes the new record DOES NOT SAVE.

As for the ImX fields, the OnClick event simply uses the Dir function to see if the named image is in the image library (External Windows Folder). If so, it set's the Picture property of the Image control. I would have the same issue were the image names tabled via a relationship ID. Moreover, the folder contents could be independently changed leaving the tabled names invalid.

[xps35]

I guess you empty tblInvNew later in the proces. If you do a compact and repair the autonumber will start with 1 again. That might cause duplicates.

[Welshgasman]

Are you using that code in post #5

[GraeagleBill]

Table "tblnvNew" is now gone. See #10

[GraeagleBill]

No. There is no UnLoad event code at all. Nor is there a OnClose event.

I just added this and the record still does not save:

Private Sub Form_Unload(Cancel As Integer)
If Me.Dirty Then Me.Dirty = False
End Sub