VBA - Query in a Form --> Recordset Select ( SUM (NZ())

[Susy]

Hi everyone,

I am having problems with a recordset and don't really now where the problem is.

Private Sub ArtikelAnzahl_AfterUpdate()

Dim zumArtikelStand As Recordset
Dim newArtikelName As String

newArtikelName = Me.ArtikelName.Value


Set zumArtikelStand = CurrentDb.OpenRecordset("SELECT" _
        & "SUM([tblTrans]![ArtikelAnzahl]) AS ArtikelStandA" _
        & "FROM tblArtikel INNER JOIN tblTrans ON tblArtikel.ArtikelName = tblTrans.ArtikelName" _
        & "GROUP BY tblTrans.ArtikelName" _
        & "HAVING (((tblTrans.ArtikelName)='" & newArtikelName & "'))", , dbOpenDynaset)


MsgBox "Der ArtikelStand ist: " & zumArtikelStand.Fields("ArtikelStand").Value & " ."

In the form the name of the field is "ArtikelStand". I have also used

zumArtikelStand.Fields("ArtikelStandA").Value

but it did not work.

I really don't know where the problem is.

Help, please

[June7]

Need space after the first quote mark on each continuation line (or before the last quote mark). Without the spaces, the text runs together. Instead of "SELECT SUM" you have "SELECTSUM" and that is meaningless to the query engine.

[Susy]

Hi June7,

thanks very much for the hint. Now I can continue making sense :-)