problem with runsql update

[fedum]

I would like to update a table and use the code:
Private Sub btnWijzigTekstKorting_Click()
Dim tekst As String
If IsNull(DLookup("tblNummeringDocumenten.TekstKortingBetaling", "tblNummeringDocumenten")) Then
tekst = ""
Else
tekst = DLookup("tblNummeringDocumenten.TekstKortingBetaling", "tblNummeringDocumenten")
End If
gTekstKortingBetaling = InputBox("Geef de tekst", "Ingave tekst")

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblNummeringDocumenten Set [TekstKortingBetaling] ='" & gTekstKortingBetaling & "'where tblNummeringDocumenten.[TekstKortingBetaling]=' & tekst'"
DoCmd.SetWarnings True
KortingBetalingTekst = gTekstKortingBetaling


End Sub

The table has only one record with default values. The field TekstKortingBetaling is normal Null. If the user would like to change the default value for this field he uses this code with an inputbox etc. I get now error but there is allso now update in the table. What is wrong? Thanks

 

[Andrzejek]

DoCmd.RunSQL "Update tblNummeringDocumenten Set [TekstKortingBetaling] ='" & gTekstKortingBetaling & "' where tblNummeringDocumenten.[TekstKortingBetaling]='[red]"[/red] & tekst [red]& "[/red]'"

but do yourself a favor and try:

Dim strSQL As String
...

strSQL = "Update tblNummeringDocumenten Set [TekstKortingBetaling] ='" & gTekstKortingBetaling & "' where tblNummeringDocumenten.[TekstKortingBetaling]='" & tekst & "'"
[blue]
Debug.Print strSQL
[/blue]
DoCmd.RunSQL strSQL

Have fun.

---- Andy

 

[fedum]

Thanks Andy. It works.

 

[fedum]

Andy,
Still have a problem if the field is null. Now update.

Thanks

 

[Andrzejek]

Do you mean your [tt]tekst[/tt] field is NULL?
What about your [tt]TekstKortingBetaling[/tt] field in your [tt]tblNummeringDocumenten[/tt] table - does it accept NULL?

You can do:

If [blue]IsNull(tekst)[/blue] Then
    strSQL = "Update tblNummeringDocumenten 
    Set [TekstKortingBetaling] ='" & gTekstKortingBetaling & "' 
    where tblNummeringDocumenten.[TekstKortingBetaling][blue] = NULL[/blue]"
else
    strSQL = "Update tblNummeringDocumenten 
    Set [TekstKortingBetaling] ='" & gTekstKortingBetaling & "' 
    where tblNummeringDocumenten.[TekstKortingBetaling]
    = '" & [blue]tekst[/blue] & "'"
end if

DoCmd.RunSQL strSQL

Germans (?) - you do like looooong words....

Have fun.

---- Andy

 

[fedum]

Hi Andy,
Sorry for the late response.
Yeh why those long words? Maybe we need a lot of words to understand

But the code is not working. I don't get a error but the field is not updated.
The problem is that I start with the fieldvalue NULL in the table tblNummeringDocumenten.[TekstKortingBetaling]. There is only 1 record. When the user changes the value for the field TekstKortingBetaling then the code has to look for the NULL in the table.

And yes the field TekstKortingBetaling accepts NULL.

Thanks!