Update table code

[ZeidHaddad]

Good day to you all. I'm having a problem with this code:

Private Sub speichern_Click()
On Error GoTo speichern_Click_Err

    On Error Resume Next

    Dim MaxID As Variant
    Dim StrSQL As String
    If Me.Vertragsnummer = DLookup("[Vertragsnummer]", "Dokumentation", "[Vertragsnummer] = '" & Me.Vertragsnummer & "'") Then
        MsgBox ("Entry already exists - please save")
        
    
        StrSQL = "UPDATE Dokumentation SET Name = '" & Me.txtName & "',Bausparsumme = '" & Me.txtBausparsumme & "',Abschlussdatum = '" & Me.txtAbschlussdatum & "',Saldo_EUR = '" & Me.txtSaldo_EUR & "',Saldo_3112_Vorjahr_EUR = '" & Me.txtSaldo_3112_Vorjahr_EUR & "',Sollzins_Gebunden_Fest_JAEhrlic = '" & Me.TXTSollzins_Gebunden_Fest_JAEhrlic & "',Guthabenszins_Jaehrlich = '" & Me.txtGuthabenszins_Jaehrlich & "',V_Name = '" & Me.txtV_Name & "',Saldo_EUR = '" & Me.txtSaldo_EUR & "',Saldo_3112_Vorjahr_EUR = '" & Me.txtSaldo_3112_Vorjahr_EUR & "',ZK_vorhanden= '" & Me.txtZK_vorhanden & "',Abtretung_vorhanden = '" & Me.txtAbtretung_vorhanden & "' WHERE Vertragsnummer = '" & Me.Vertragsnummer & "'"

        
        DoCmd.RunSQL (StrSQL)
     Else
    MaxID = DMax("[FallNr]", "Dokumentation")
    If Not IsNull(MaxID) Then
    Me.FallNr = MaxID + 1
    End If
    
        StrSQL = "UPDATE Santander SET Anruf_1 = '" & Me.txtAnruf_1 & "', Anruf_2 = '" & Me.txtAnruf_2 & "', Anruf_3 = '" & Me.txtAnruf_3 & "', Kunde_erreicht = '" & Me.txtKunde_erreicht & "'  WHERE Vertragsnummer = " & Me.Vertragsnummer & ""
    DoCmd.RunSQL (StrSQL)
  
    End If
    
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
    DoCmd.Close acForm, "Sant_lists"
    
speichern_Click_Exit:
    Exit Sub

speichern_Click_Err:
    MsgBox Error$
    Resume speichern_Click_Exit

End Sub

I'm Using the code to update my target table "Documentation" i've already used it on another Form and it worked perfectly but when i copied it and used it on another form when i click the save button ( Speichern_Click) I get that the object is not found and txt.name is highlighted although Me.txtName if inside the form and the name is correct as well as Name is inside table dokumentation.

Any help ?

 

[Galaxiom]

I can't see the issue but your lack of formatting does make it difficult to spot errors.

Are you familiar with using Line Continuation in VBA?

 

[ZeidHaddad]

i'm really new to VBA that's why.
Yes I've done in the previous code ( other form )

Private Sub speichern_Click()
On Error GoTo speichern_Click_Err

    On Error Resume Next
     Dim StrSQL As String
    Dim MaxID As Variant
    
    If Me.Vertragsnummer = DLookup("[Vertragsnummer]", "Dokumentation", "[Vertragsnummer] = '" & Me.Vertragsnummer & "'") Then
        MsgBox ("Entry already exists - please save")
        
    
        StrSQL = "UPDATE Dokumentation SET Name = '" & Me.txtName & "', Vetrag_eingerichtet = '" & Me.Vetrag_eingerichtet & _
        "', Strasse_Kunde = '" & Me.Strasse_Kunde & "', Anrede = '" & Me.txtAnrede & "', TELEFONNR_Kunde = '" & Me.txtTELEFONNR & "', E_Mail_Kunde = '" & Me.txtE_Mail & _
        "',Vorname = '" & Me.txtVorname & "', Direktgeschaeft_versendet = '" & Me.Direktgeschaeft & "', Bausparsumme_angelegt = '" & Me.Bausparsumme_angelegt & _
        "', HNR_Kunde = '" & Me.txtHNR_Kunde & "',PLZ_KUNDE = '" & Me.txtPLZ_KUNDE & "',ORT_Kunde = '" & Me.txtORT_KUNDE & _
        "',Geburtsdatum_KUNDE = '" & Me.txtGeburtsdatum_KUNDE & "',Aktionsnr = '" & Me.AKTIONSNR & "',Aktion = '" & Me.txtAktion & _
        "',Aktionsdatum = '" & Me.Aktionsdatum & "',Struktur = '" & Me.Struktur & "',Vertreternr = '" & Me.Vertreternr & _
        "',Tarif = '" & Me.txtTarif & "',Bausparsumme = '" & Me.txtBausparsumme & "',Abschlussdatum = '" & Me.txtAbschlussdatum & _
        "',Saldo_EUR = '" & Me.txtSaldo_EUR & "',Saldo_3112_Vorjahr_EUR = '" & Me.txtSaldo_3112_Vorjahr_EUR & _
        "',Sollzins_Gebunden_Fest_JAEhrlic = '" & Me.TXTSollzins_Gebunden_Fest_JAEhrlic & "',Guthabenszins_Jaehrlich = '" & Me.txtGuthabenszins_Jaehrlich & _
        "',V_Name = '" & Me.txtV_Name & _
        "',VL_Eingang = '" & Me.txtVL_Eingang & _
        "',ZK_vorhanden= '" & Me.txtZK_vorhanden & _
        "',Abtretung_vorhanden = '" & Me.txtAbtretung_vorhanden & _
        "' WHERE Vertragsnummer = '" & Me.Vertragsnummer & "'"
        
        
        
        
        
        DoCmd.RunSQL (StrSQL)
   
        
        '"', Datum_Neuabschluss = '" & Me.Datum_Neuabschluss & ( A problem occurs when this code is added )

    
    Else
      MaxID = DMax("[FallNr]", "Dokumentation")
      If Not IsNull(MaxID) Then
      Me.FallNr = MaxID + 1
      End If
      
         StrSQL = "UPDATE Impulsweitergabe SET Anruf_1 = '" & Me.txtAnruf_1 & "', Anruf_2 = '" & Me.txtAnruf_2 & "', Anruf_3 = '" & Me.txtAnruf_3 & "', Kunde_erreicht = '" & Me.txtKunde_erreicht & "'  WHERE Vertragsnummer = " & Me.Vertragsnummer & ""
      DoCmd.RunSQL (StrSQL)
          

    End If
    
    DoCmd.RunCommand acCmdSaveRecord
    
    
     DoCmd.Close acForm, "Impulsweitergabe_lists"
    
speichern_Click_Exit:
    Exit Sub

speichern_Click_Err:
    MsgBox Error$
    Resume speichern_Click_Exit

End Sub

 

[Galaxiom]

Is txtName on a subform?

 

[ZeidHaddad]

No there is no subform

 

[Galaxiom]

i'm really new to VBA

DoCmd.RunSQL (StrSQL)

It isn't the issue here but instead of this line use:

Currentdb.Execute strSQL, dbFailOnError

Execute avoids the series of questions you get with RunSQL
dbFailOnError raises an error if there are any real problems with the Update.

Also note that, although it does not matter here, you would not normally use parentheses around the parameters of a Sub, only for a Function. This catches many VBA newbies and is quite bewildering until you understand why. An explanation is beyond the scope of this answer.

 

[Galaxiom]

"Name" is a reserved word and should be avoided. It is one of the worst and can cause unexpected problems.

At the very least surround it in square brackets in your query, but preferably change it to something else.

 

[ZeidHaddad]

Currentdb.Execute strSQL dbFailOnError i'm trying to add it but it's not working keeps appearing in red, excuse me for my little knowledge in this

maybe i should add another thing for the dbFailOnError

 

[Galaxiom]

Currentdb.Execute strSQL dbFailOnError i'm trying to add it but it's not working keeps appearing in red,r

Sorry I left out the comma between the two parameters. Corrected now,

 

[ZeidHaddad]

Okay it's in but i got the same problem
object not found txt.name is highlighted!

 

[Galaxiom]

It may be a corruption of the form. Try deleting txtName from the form and adding it again.

 

[Galaxiom]

Okay it's in but i got the same problem
object not found txt.name is highlighted!

I assumed txt.Name is a typographical error as I can't see it in the SQL. But maybe it is the crux of the problem?

 

[isladogs]

ZeidHadded
You may have noticed that I added code tags to posts #1 and #3 to make it easier to read.
Please can you do so for any future code using the # button on the edit post toolbar.