Solved Form Data to Save to Table AND APPEND another Table

[hbrehmer]

Private Sub SaveNew_Click()
If Me.Dirty Then
Me.Dirty = False
End If

Dim strSQL As String

strSQL = "INSERT INTO Part Inventory(PARTID, RevLevel, ODID, RELID, RelNum, RQty, INVTRXID, CreatedDate ) " _
& "SELECT OrderReleases.PARTID, OrderReleases.RevLevel, OrderReleases.ODID, OrderReleases.RELID, OrderReleases.ReleaseNum, OrderReleases.RelQty, OrderReleases.INVTRXID, OrderReleases.RDateEnt " _
& "FROM OrderReleases " _
& "WHERE PARTID = " & Me.PARTID

CurrentDb.Execute strSQL, dbFailOnError - It errors here.

End Sub

 

[Gasman]

Private Sub SaveNew_Click()
If Me.Dirty Then
Me.Dirty = False
End If

Dim strSQL As String

strSQL = "INSERT INTO Part Inventory(PARTID, RevLevel, ODID, RELID, RelNum, RQty, INVTRXID, CreatedDate ) " _
& "SELECT OrderReleases.PARTID, OrderReleases.RevLevel, OrderReleases.ODID, OrderReleases.RELID, OrderReleases.ReleaseNum, OrderReleases.RelQty, OrderReleases.INVTRXID, OrderReleases.RDateEnt " _
& "FROM OrderReleases " _
& "WHERE PARTID = " & Me.PARTID

Debug.Print strSQL ' Paste back what this displays in the debug window

CurrentDb.Execute strSQL, dbFailOnError - It errors here.

End Sub[

 

[hbrehmer]

I'm so sorry. It still gives me the Run-time error '3134': Syntax error in INSERT INTO statement. When I hit "DEBUG" on the error popup, it highlights the last line of the code: CurrentDB.Execute strSQL, dbFailOnError.

 

[Gasman]

Get into the VBA code window (ALT + F11). Make sure you have the Immediate Window visible
Set a breakpoint somewhere in that sub.

Comment out the CurrentDb.Execute strSQL

Click the button and it should jump to your breakpoint line. Then use F8 for line by line of F5 to continue, but post back what gets displayed in the Immediate Window.

That will be the contents of the strSQL varaiable and hopefully will show the error.?

https://www.fmsinc.com/tpapers/vbacode/debug.asp

 

[hbrehmer]

 

[hbrehmer]

I am doing some research on the error I keep getting. One question I have, is that I am not referencing the Primary Key field in the target table because I want to add a new record. Do I need to reference that field? I don't when I created an Append query in the Query Design Builder. Also, do I need to have this query built and then reference it? Or can I call up the INSERT INTO through an Event on a button on a form. I may be asking ACCESS to do to much.

 

[Gasman]

My Lord !
Fisrtly you have not put Debug.Print where I showed it should go.? How do you expect to see anything in that variable when you have not even created it yet.?
Secondly, you have not commented out the CurrentDB.Execute, so that is still going to fail.

Hopefully the key field in the target table is an autonumber field?, so no, that is created automatically, or will be if we even get past this stage.
First we need to see what is produced in strSQL, not just knock a few words together and hope for the best.?

The fact that you put the Debug.Print where you did, leads me to believe you need to look at that link I posted for tips on debugging.?
Also looks like you might not have Option Explicit at the top of your modules.?

 

[hbrehmer]

I am sorry. I was confused. I followed your directions above (I missed that post), and when I ran the event, the debug stopped on the last line of the strSQL. Could it be that the WHERE statement should say something like Part Inventory.PARTID = " & Me.PARTID ?

 

[Gasman]

You need to copy and paste the debug.print output here. A picture is no good, we can only see the first part.?

You set the breakpoint on the SrtSQL assignment, so it is always going to stop there, that is waht a breakpoint does?. Set it on the debug.print statement or just let it run, but still post back the output from the immediate window.

What was the error again?

 

[hbrehmer]

Good Gosh! I read the link on Debugging; placed the Break at the Debug.Print and now I get no errors, but in the immediate window the Insert Statement appears twice and the event stops at the Debug.Print strSQL line. The record adds to my FROM table, but the INTO table does not receive the data. I am about to abort this process and find another way. I know you are frustrated working with me. I apologize for that. This is just as frustrating for me, as I am trying to understand coding and the million ways to write the code.

 

[pbaldy]

Can you attach the db here? You haven't pasted the SQL that gets generated in the Immediate window here. The picture doesn't show it all.

 

[hbrehmer]

Here is my database. Please go to frmReleaseEntry. You will see the event on the "Save & Add New" button. Let me know what you find.

 

[Gasman]

I cannot open it with my 2007, so I will bow out.

 

[hbrehmer]

I cannot open it with my 2007, so I will bow out.

thank you for your help.

 

[pbaldy]

For starters you have a colon that causes an error here:

CurrentDb.Execute; strSQL, dbFailOnError

The code is failing because of the inadvisable space in the table name. It needs to be bracketed. Try:

    If Me.Dirty Then
        Me.Dirty = False
    End If

    Dim strSQL As String

    strSQL = "INSERT INTO [Part Inventory] (PARTID, RevLevel, ODID, RELID, RelNum, RQty, INVTRXID, CreatedDate ) " _
    & "SELECT PARTID, RevLevel, ODID, RELID, RelNum, RQty, INVTRXID, CreatedDate " _
    & "FROM OrderReleases " _
    & "WHERE PARTID = " & Me.PARTID
    
    CurrentDb.Execute strSQL, dbFailOnError

 

[hbrehmer]

I saw that too! THANK YOU!!!!! It was the brackets around Part Inventory! Whew! Now to move on to the rest of my program!

XOXOXOXO! Thank you so much!!!!

 

[pbaldy]

Happy to help!

 

[Gasman]

For starters you have a colon that causes an error here:

CurrentDb.Execute; strSQL, dbFailOnError

The code is failing because of the inadvisable space in the table name. It needs to be bracketed. Try:

    If Me.Dirty Then
        Me.Dirty = False
    End If

    Dim strSQL As String

    strSQL = "INSERT INTO [Part Inventory] (PARTID, RevLevel, ODID, RELID, RelNum, RQty, INVTRXID, CreatedDate ) " _
    & "SELECT PARTID, RevLevel, ODID, RELID, RelNum, RQty, INVTRXID, CreatedDate " _
    & "FROM OrderReleases " _
    & "WHERE PARTID = " & Me.PARTID
   
    CurrentDb.Execute strSQL, dbFailOnError

No colon was ever posted that I could see.

Apologies though for not recognising the table name had spaces, as it appears that was the problem all along.?

 

[hbrehmer]

No colon was ever posted that I could see.

Apologies though for not recognising the table name had spaces, as it appears that was the problem all along.?

All good! I missed that too! I'm glad to have help from people like you too! It takes many eyes to see the errors. Thank you so much!

 

[pbaldy]

No colon was ever posted that I could see.

It was in the sample, not posted that I noticed. I should have seen the space in the table name too. I think I was focused on getting the whole SQL statement.