Solved Form Data to Save to Table AND APPEND another Table

[hbrehmer]

Hi all,
I have searched both Google and this forum to find the answer to my issue. I am trying to enter data into a table through a Form and then use the data in the form to "Append" another table. I can create the Append Query just fine, but when I try to add the code to a ONCLICK event, I get all kinds of errors. I can't seem to find a similar example to what I am trying to accomplish.

Form "RELENTRY" collects information about a purchase order release. I need some of that data to create another entry on my inventory table to account for the quantity ordered.

This is where I am with my coding:

Private Sub SaveNew_Click()
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 Me.PARTID = PARTID (Is this where I get only the record from the form to create only 1 record in the Inventory table?)

On Error GoTo SaveNew_Click_Err
On Error Resume Next
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If

Command38_Click_Exit:
Exit Sub

Command38_Click_Err:
MsgBox Error$
Resume Command38_Click_Exit

End Sub


Help!

Heidi

 

[pbaldy]

You stopped the string, and need to concatenate the value from the form. You were also missing some spaces. Try

  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

 

[hbrehmer]

Thank you! I'll give it a try.

 

[pbaldy]

No problem, post back if you're still stuck. I assume you know you still have to execute the string.

 

[hbrehmer]

No problem, post back if you're still stuck. I assume you know you still have to execute the string.

Um. I think that is why it is not working. What would I need to type (and where) to execute the string? Sorry for seeming stupid about this. I am still learning.

 

[pbaldy]

CurrentDb.Execute strSQL, dbFailOnError

 

[pbaldy]

Oh, and that would be after building the string. And don't worry about seeming stupid. It's not stupid to not have learned something yet, and we all started at the same place.

 

[hbrehmer]

Oh, and that would be after building the string. And don't worry about seeming stupid. It's not stupid to not have learned something yet, and we all started at the same place.

Okay, so just to make it clear. I place the execute command after my last line of the strSQL and before End Sub?

I hope when I have learned all this, I can help others too.

 

[pbaldy]

Yes, but you've got some junk in there that is either meaningless or will throw an error. Try with just:

  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

And go from there. More on proper error handling:

Error trapping

www.baldyweb.com

 

[hbrehmer]

Is this what you mean? I get an error message.

Private Sub SaveNew_Click()

Dim strSQL As String
CurrentDb.Execute strSQL, dbFailOnError

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


End Sub

 

[pbaldy]

See post 9. You can't execute the string until you've populated it.

 

[hbrehmer]

I copied what you typed, but now I get a Run-time error '3134" - Syntax error in INSERT INTO statement. I'm really scratching my head. When I created the Append query it worked fine. So I copied the SQL to create this statement. The only thing I can think of is that the record needs to be saved first, then run the append command. Am I right in thinking that?

 

[pbaldy]

Use this method to see what the finished SQL is and debug it:

Debugging

If you don't spot the problem, post the SQL here.

 

[pbaldy]

Oh, and yes, the record has to be committed so there's an ID.

 

[hbrehmer]

How would recommend would be the best way to accomplish this? If I want to run the APPEND query on a click event, how would I write the code to save the record first?

 

[pbaldy]

Try

If Me.Dirty Then Me.Dirty = False

 

[hbrehmer]

Use this method to see what the finished SQL is and debug it:

Debugging

If you don't spot the problem, post the SQL here.

I hope you had a wonderful Easter. I am back to working on this. I really want this to work. With adding the Me.Dirty statement, the record is saved to the first table, but the append does not happen.

Here is what I have. I removed the execute statement, because it errors out on it. Maybe I had the execute statement in the wrong spot.

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

End Sub


What do you see wrong with this statement?

 

[Minty]

Paul is offline at the moment so I'll attempt to assist - what is the error?

 

[hbrehmer]

Thank you! With the code in response #17, I get a Run-time Error '3134': Syntax error in INSERT INTO statement. What do you see wrong with my coding?

 

[Gasman]

Debug.Print strSQL and post back what it shows. ?