Update table code (1 Viewer)

Z

ZeidHaddad

Registered User.
Local time
Yesterday, 19:55
Joined
Oct 29, 2019
Messages
44
Good day to you all. I'm having a problem with this code:


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 ?
 
Last edited by a moderator:
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:55
Joined
Jan 20, 2009
Messages
12,851
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?
 
Z

ZeidHaddad

Registered User.
Local time
Yesterday, 19:55
Joined
Oct 29, 2019
Messages
44
i'm really new to VBA that's why.
Yes I've done in the previous code ( other form )

Code: 
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
 
Last edited by a moderator:
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:55
Joined
Jan 20, 2009
Messages
12,851
ZeidHaddad said:
i'm really new to VBA

DoCmd.RunSQL (StrSQL)
Click to expand...

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

Currentdb.Execute strSQL, dbFailOnError
Click to expand...

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.
 
Last edited:
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:55
Joined
Jan 20, 2009
Messages
12,851
"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.
 
Z

ZeidHaddad

Registered User.
Local time
Yesterday, 19:55
Joined
Oct 29, 2019
Messages
44
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
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:55
Joined
Jan 20, 2009
Messages
12,851
ZeidHaddad said:
Currentdb.Execute strSQL dbFailOnError i'm trying to add it but it's not working keeps appearing in red,r
Click to expand...

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

ZeidHaddad

Registered User.
Local time
Yesterday, 19:55
Joined
Oct 29, 2019
Messages
44
Okay it's in but i got the same problem
object not found txt.name is highlighted!
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:55
Joined
Jan 20, 2009
Messages
12,851
It may be a corruption of the form. Try deleting txtName from the form and adding it again.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:55
Joined
Jan 20, 2009
Messages
12,851
ZeidHaddad said:
Okay it's in but i got the same problem
object not found txt.name is highlighted!
Click to expand...

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

isladogs

MVP / VIP
Local time
Today, 03:55
Joined
Jan 14, 2017
Messages
18,209
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.
 
You must log in or register to reply here.

Similar threads

Z
Update Code
Replies
11
Views
584
theDBguy
theDBguy
Z
Update code
Replies
7
Views
527
Gasman
Gasman
Z
Combining 2 codes under one button
Replies
2
Views
373
ZeidHaddad
Z
Z
filling a form from a specific table
Replies
11
Views
785
alaaaboulela
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom