Copy subform data

[remuremmi]

Hi!

I started from forum thread t=225857 and tried to do some adjustments... Not working so good.

First part works:

Private Sub CmdLisääTyhjä_Click()

Dim iNewID As Long
DBEngine.BeginTrans
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblTilaukset", dbOpenDynaset)
With rs
.AddNew
!TilausNro = Me!cboTilausNro
!Rivi = Nz(DMax("Rivi", "tblTilaukset", "TilausNro=" & Me!cboTilausNro)) + 1
.Update
.Bookmark = .LastModified
.Close

End With
Set rs = Nothing

But when I try to add there sql query I get error

Private Sub cmdKopio_Click()

DBEngine.BeginTrans
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblTilaukset", dbOpenDynaset)
Set muuttuja1 = Me.cboTilausNro
Set muuttuja2 = Me.NykyRivi
With rs
.AddNew
!TilausNro = Me!cboTilausNro
!Rivi = Nz(DMax("Rivi", "tblTilaukset", "TilausNro=" & Me!cboTilausNro)) + 1
.Update
.Bookmark = .LastModified
.Close
End With
Set rs = Nothing
CurrentDb.Execute = "INSERT INTO tblTilaukset (ListaNimi, Materiaali, Väri, Määrä, MääräYks, Pituus, PituusYks) " & _
"SELECT ListaNimi, Materiaali, Väri, Määrä, Määräyks, Pituus, PituusYks " & _
"FROM tblTilaukset " & _
"WHERE TilausNro = 'Me.cboTilausNro' " & _
"AND Rivi = 'Me.NykyRivi' ;"

Can't get it working (error: Argument not optional)
Can someone help me?

 

[jzwp22]

The values from the form are variables and thus cannot be included in the string. They have to be concatenated to the string. Also, you should not have an = sign after the currentDB.execute


CurrentDb.Execute "INSERT INTO tblTilaukset (ListaNimi, Materiaali, Väri, Määrä, MääräYks, Pituus, PituusYks) " & _
"SELECT ListaNimi, Materiaali, Väri, Määrä, Määräyks, Pituus, PituusYks " & _
"FROM tblTilaukset " & _
"WHERE TilausNro = '" & Me.cboTilausNro & "' " & _
" AND Rivi = '" & Me.NykyRivi & "'"

 

[remuremmi]

Thanks.

I've been thinking about those quotations...
And I hoped that problem is somewhere there.
But still I get Run-time error 3464...
Does it got to do something with null values, error handlig or ...?
Something small but where to start look at...

Here is what i got now:

Private Sub cmdKopio_Click()

DBEngine.BeginTrans
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblTilaukset", dbOpenDynaset)
With rs
.AddNew
!TilausNro = Me!cboTilausNro
!Rivi = Nz(DMax("Rivi", "tblTilaukset", "TilausNro=" & Me!cboTilausNro)) + 1
.Update
.Bookmark = .LastModified
.Close
End With
Set rs = Nothing
CurrentDb.Execute "INSERT INTO tblTilaukset (ListaNimi, Materiaali, Väri, Määrä, MääräYks, Pituus, PituusYks) " & _
"SELECT ListaNimi, Materiaali, Väri, Määrä, Määräyks, Pituus, PituusYks " & _
"FROM tblTilaukset " & _
"WHERE TilausNro ='" & CLng(Me.cboTilausNro) & "' " & _
"AND Rivi = '" & CLng(Me.NykyRivi) & "'"

End Sub


Don't worry I've still got my dayjob
But not getting this work drives me insaine

 

[spikepl]

1. What is error 3464? We don't have the entire list of errors in our heads.
2. In which line? Letting us guess is kind of pointless since you already have that information

Also, code your sql like this: http://www.baldyweb.com/ImmediateWindow.htm

 

[RainLover]

Place this in a query and see if it works.

 Nz(DMax("Rivi", "tblTilaukset", "TilausNro=" & Me!cboTilausNro)) + 1

You should also step through your code with F8 and check the values as you progress.

 

[jzwp22]

"WHERE TilausNro ='" & CLng(Me.cboTilausNro) & "' " & _
"AND Rivi = '" & CLng(Me.NykyRivi) & "'"

What is the data type of the tilausNro and Rivi fields? If they are numbers, the you must remove the single quotes around the two form values.


"WHERE TilausNro ='" & CLng(Me.cboTilausNro) & "' " & _
"AND Rivi = '" & CLng(Me.NykyRivi) & "'"

The single quotes are only needed for text values. The # signs are need for date/time values. No delimiters are needed for numeric values.

 

[remuremmi]

Thank you for all the answers.

But no I can't get them work.

to spikepl: error code 3464 is "tyyppivirhe ehtomäärityksessä"
SQL code is highlighted yellow and arrow pointing last row.

I think this is here and I try to do it different way.
Don't know how but if someone got sugestions I'm listening

 

[jzwp22]

For troubleshooting purposes, you could put the SQL text in a variable that way you can copy the constructed text to the VBA immediate window using the debug.print statement. From there you can inspect the SQL text for errors. You can also copy that text to a new query and test the query to see if it returns any errors. See red areas below that I have changed/added to your code.

Private Sub cmdKopio_Click()

DBEngine.BeginTrans
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblTilaukset", dbOpenDynaset)
With rs
.AddNew
!TilausNro = Me!cboTilausNro
!Rivi = Nz(DMax("Rivi", "tblTilaukset", "TilausNro=" & Me!cboTilausNro)) + 1
.Update
.Bookmark = .LastModified
.Close
End With
Set rs = Nothing

[COLOR="Red"]Dim mySQL as string

mySQL= "INSERT INTO tblTilaukset (ListaNimi, Materiaali, Väri, Määrä, MääräYks, Pituus, PituusYks) " &_
"SELECT ListaNimi, Materiaali, Väri, Määrä, Määräyks, Pituus, PituusYks " & _
"FROM tblTilaukset " & _
"WHERE TilausNro =" & CLng(Me.cboTilausNro)  & _
"AND Rivi = " & CLng(Me.NykyRivi)

debug.print mySQL

CurrentDb.Execute  mySQL, dbfailonerror[/COLOR]

End Sub