Set rs with WHERE + AND, SQL-Expression

[Umbauwfb]

Hello, ich do not know how to write the SQL at position



& " AND TeilnehmerID = " & vID_zu, dbOpenDynaset)

Private Sub cmdTeilnehmerVerschieben_Click()

Dim dB As DAO.Database
Dim rs_von As DAO.Recordset
Dim rs_zu As DAO.Recordset

Dim vID_von As Long
Dim vID_zu As Long

Dim sSQL As String

Set dB = CurrentDb
vID_von = Me.txtFirmenID_von.Value                    'do I need the .Value? Or should I use Me.txtFirmenID_von.Value in the code?
vID_zu = Me.txtFirmenID_zu.Value                        'do I need the .Value? Or should I use Me.txtFirmenID_zu.Value in the code?

Set rs_von = dB.OpenRecordset("SELECT" _
& " * FROM TeilnehmerAdressdatenT" _
& " WHERE AdressdatenID = " & vID_von, dbOpenSnapshot)

Do Until rs_von.EOF

Set rs_zu = dB.OpenRecordset("SELECT" _
& " Count (*) As Anz FROM TeilnehmerAdressdatenT" _
& " WHERE AdressdatenID = " & rs_von!TeilnehmerID, dbOpenDynaset)                      'this group works
'warum läuft der Block mit AND nicht?

' Set rs_zu = dB.OpenRecordset("SELECT" _
' & " Count (*) As Anz FROM TeilnehmerAdressdatenT" _
' & " WHERE AdressdatenID = " & rs_von!TeilnehmerID" _
' & " AND TeilnehmerID = " & vID_zu, dbOpenDynaset)                'the same group with "AND" does not work...
'                                                                                                          'how do I have to write the SQL ???

Debug.Print rs_zu!Anz

If rs_zu!Anz = 0 Then
rs_zu!AdressdatenID = rs_von!AdressdatenID
Exit Do
End If

rs_von.MoveNext
Loop

sSQL = "DELETE FROM TeilnehmerAdressdatenT WHERE AdressdatenID = & vID_von.Value"
CurrentDb.Execute sSQL, dbFailOnError


'Aufräumen
Set rs_von = Nothing
Set rs_zu = Nothing
Set dB = Nothing

End Sub

can somebody help please?
Best regards

Harry

[Welshgasman]

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.

Then I can debug.print them to see if I have the syntax correct.

The when correct, I can use them in the function.

Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Dates with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything

[Micron]

No, you don't need .Value If vid_von is text data, you need quote delimiters (for dates, must use #). So maybe

AdressdatenID = '" & vID_von & "'"Note the nested single quotes there.

If you are getting an error please state the error number and description.
If it's just invalid results, you can create the sql, insert Debug.Print line before the .Execute line and step through the code. Read the sql output in the immediate window after Debug line runs and check it. If it looks ok you can copy/paste to a new query in sql view and switch to datasheet view. If it balks, Access usually highlights the problem portion in sql view.

[Minty]

Put Your SQL into a string then Debug.Print it to make sure it is what you are looking for;

DIM strSql as String


 strsql = "SELECT Count (*) As Anz FROM TeilnehmerAdressdatenT " 
 strsql = strSql & " WHERE AdressdatenID = " & rs_von!TeilnehmerID & " "
 strsql = strSql & " AND TeilnehmerID = " & vID_zu

Debug.Print strSql           
		
Set rs_zu = dB.OpenRecordset( strSql, dbOpenSnapShot)

You won't be able to open a dynaset on aggregated query

[Umbauwfb]

Thanks for your help...I am still struggling...

[Minty]

So what is the result of the debug.print ? (Press ctrl +G in the VBA editor if you can't see it)

You should be able to copy and paste that into the query designer and try and run it.
It will hopefully give you a more meaningful error?

[Umbauwfb]

Thank you for your help so far...
I am still struggling...

I do not know how to handle pos
sSQL = "SELECT Update set AdressdatenID = '" & vID_zu & "' (*) FROM TeilnehmerAdressdatenT " 'THIS IS NOT OK !!!!!
Sorry...I am rather new with Access VBA

Harry



Private Sub cmdTeilnehmerVerschieben_Click()

Dim dB As DAO.Database
Dim rs_von As DAO.Recordset
Dim rs_zu As DAO.Recordset


Dim vID_von As Long
Dim vID_zu As Long


Dim strSQL1 As String
Dim strSQL2 As String
Dim sSQL As String


Dim Anz As Long


Set dB = CurrentDb
vID_von = Me.txtFirmenID_von
vID_zu = Me.txtFirmenID_zu

strSQL1 = "SELECT * FROM TeilnehmerAdressdatenT"
strSQL1 = strSQL1 & " WHERE AdressdatenID = " & vID_von & " "

Debug.Print strSQL1
Set rs_von = dB.OpenRecordset(strSQL1, dbOpenSnapshot)
Do Until rs_von.EOF
strSQL2 = "SELECT Count (*) As Anz FROM TeilnehmerAdressdatenT "
strSQL2 = strSQL2 & " WHERE AdressdatenID = " & vID_zu & " "
strSQL2 = strSQL2 & " AND TeilnehmerID = " & rs_von!TeilnehmerID

Debug.Print strSQL2; Anz

Set rs_zu = dB.OpenRecordset(strSQL2, dbOpenSnapshot)
If Anz = 0 Then
sSQL = "SELECT Update set AdressdatenID = '" & vID_zu & "' (*) FROM TeilnehmerAdressdatenT " 'THIS IS NOT OK !!!!!
sSQL = sSQL & " WHERE AdressdatenID = " & vID_von & " "
sSQL = sSQL & " AND TeilnehmerID = " & rs_von!TeilnehmerID
CurrentDb.Execute sSQL, dbFailOnError


Debug.Print vID_zu
Exit Do
End If
rs_von.MoveNext
Loop

sSQL = "DELETE FROM TeilnehmerAdressdatenT WHERE AdressdatenID = & vID_von.Value"
CurrentDb.Execute sSQL, dbFailOnError



'Aufräumen
Set rs_von = Nothing
Set rs_zu = Nothing
Set dB = Nothing


End Sub

[orange]

What is this suppose to do?

Set rs_zu = dB.OpenRecordset(strSQL2, dbOpenSnapshot)
    If Anz = 0 Then
   sSQL = "SELECT Update set AdressdatenID = '"  & vID_zu & "' (*) FROM TeilnehmerAdressdatenT "     'THIS IS  NOT OK !!!!!
     sSQL = sSQL & " WHERE AdressdatenID = " & vID_von & " "
     sSQL = sSQL & " AND TeilnehmerID = " & rs_von!TeilnehmerID
      CurrentDb.Execute sSQL, dbFailOnError

The word SELECT is not relevant if you want an UPDATE (ACTION QUERY).

[Umbauwfb]

The debug.print is a very big help!

[Umbauwfb]

I know that the code is terribly false...
The selected values are ok...
I want to update the selected record with

AdressdatenID = '" & vID_zu & "' FROM TeilnehmerAdressdatenT

[orange]

See this for reference

[Micron]

Then you want to Update someTable SET someTable.someField = and maybe apply criteria.
I suggest you use the query builder (or wizard) to create a query that works, then do something with that. If you're sure you need vba rather than just calling this query, then you can put the working sql into your code. Wondering why you've chosen the vba route instead of just running a query - you're opening a rs but not putting it in Edit mode so I'm not seeing how that will work even when you fix the sql statement(s).

You put 1st code in code tags, but not the second code post??

[ssanfu]

There were some errors in the queries: this is how I would write the code - (untested)

Private Sub cmdTeilnehmerVerschieben_Click()

    Dim dB As DAO.Database
    Dim rs_von As DAO.Recordset
    Dim rs_zu As DAO.Recordset

    Dim vID_von As Long
    Dim vID_zu As Long

    Dim sSQL As String

    Set dB = CurrentDb
    vID_von = Me.txtFirmenID_von
    vID_zu = Me.txtFirmenID_zu

    sSQL = "SELECT * FROM TeilnehmerAdressdatenT "
    sSQL = sSQL & " WHERE AdressdatenID = " & vID_von
    ' Debug.Print sSQL
    Set rs_von = dB.OpenRecordset(sSQL, dbOpenSnapshot)

    Do Until rs_von.EOF

        sSQL = "SELECT Count(*) As Anz FROM TeilnehmerAdressdatenT"
        sSQL = sSQL & " WHERE AdressdatenID = " & rs_von!TeilnehmerID            ' << -- had an extra double quote at the end of this line
        sSQL = sSQL & " AND TeilnehmerID = " & vID_zu                            ' hatte ein zusätzliches doppeltes Anführungszeichen am Ende der Zeile
       ' Debug.Print sSQL
        Set rs_zu = dB.OpenRecordset(sSQL, dbOpenDynaset)

        Debug.Print rs_zu!Anz

        If rs_zu!Anz = 0 Then
            rs_zu!AdressdatenID = rs_von!AdressdatenID
            Exit Do
        End If

        rs_von.MoveNext
    Loop

    sSQL = "DELETE FROM TeilnehmerAdressdatenT WHERE AdressdatenID = " & vID_von    ' <<-- had an extra double quote at the end of the line
    ' Debug.Print sSQL                                                              ' hatte ein zusätzliches doppeltes Anführungszeichen am Ende der Zeile

    CurrentDb.Execute sSQL, dbFailOnError


    '--- clean up ---
    On Error Resume Next
    rs_von.Close
    rs_zu.Close

    'Aufräumen
    Set rs_von = Nothing
    Set rs_zu = Nothing
    Set dB = Nothing

End Sub

[Umbauwfb]

After a hard fight, the code finally is working perfectly
Thank you very much!
Your hints were very helpful!
Best regards
Harry

Private Sub cmdTeilnehmerVerschieben_Click()                 
Dim dB As DAO.Database


Dim rs_von As DAO.Recordset
Dim rs_zu As DAO.Recordset


Dim vID_von As Long
Dim vID_zu As Long


Dim sSQL As String
Dim sSQL1 As String


Set dB = CurrentDb
vID_von = Me.txtFirmenID_von
vID_zu = Me.txtFirmenID_zu
 
            Set rs_von = dB.OpenRecordset("SELECT" _
            & " * FROM TeilnehmerAdressdatenT" _
            & " WHERE AdressdatenID = " & vID_von, dbOpenSnapshot)
                
                Do Until rs_von.EOF
            
                    Set rs_zu = dB.OpenRecordset("SELECT" _
                    & " Count (*) As Anz FROM TeilnehmerAdressdatenT" _
                    & " WHERE AdressdatenID = " & vID_zu _
                    & " AND TeilnehmerID = " & rs_von!TeilnehmerID, dbOpenSnapshot)
                    Debug.Print rs_zu!Anz
  


                        If rs_zu!Anz = 0 Then
'                            TEST-Druck
'                            sSQL = "SELECT (*) FROM TeilnehmerAdressdatenT "
'                            sSQL = sSQL & " WHERE AdressdatenID = " & vID_von & " "
'                            sSQL = sSQL & " AND TeilnehmerID = " & rs_von!TeilnehmerID
'                            Debug.Print sSQL
                                                
                        
                            sSQL1 = "UPDATE TeilnehmerAdressdatenT SET AdressdatenID = '" & vID_zu & "'"
                            sSQL1 = sSQL1 & " WHERE AdressdatenID = " & vID_von & " "
                            sSQL1 = sSQL1 & " AND TeilnehmerID = " & rs_von!TeilnehmerID
'                            Debug.Print sSQL1
                            CurrentDb.Execute sSQL1, dbFailOnError


'                            Debug.Print vID_zu
'                            Exit Do
                        Else
                        End If
                rs_von.MoveNext
                Loop
            
    sSQL = "DELETE * FROM TeilnehmerAdressdatenT WHERE AdressdatenID = " & vID_von & " "
    CurrentDb.Execute sSQL, dbFailOnError
 
'Aufräumen
Set rs_von = Nothing
Set rs_zu = Nothing
Set dB = Nothing


End Sub

[ssanfu]

Happy you got it sorted out..