Number that's generated from a DLookup. 2

[lars7]

Hi,
I'm not sure if this is possible but I'm trying to insert a number that's generated from a DLookup into a field in my table but only if the field is null. I'm getting a syntax error with the following code but if I remove the "varx" and the last Where clause it update the field. Can anyone help me with this or a better way of doing it. Thanks in advance.

Private Sub Form_Close()
Dim varx As String
varx = DCount("[Pay_Number]", "Trust Staff", "Pay_Number =[Forms]![FrmAddStaff]![PAYNO]")
CurrentDb.Execute "UPDATE [trust staff] SET additpayno= varx WHERE Pay_Number= Forms!frmaddstaff!PAYNO and Where additpayno = null"

 

[Remou]

Try:

[tt]varx = DCount("*", "[Trust Staff]", "Pay_Number = " & [Forms]![FrmAddStaff]![PAYNO])
CurrentDb.Execute "UPDATE [trust staff] SET additpayno= varx WHERE Pay_Number= " & Forms!frmaddstaff!PAYNO & " and additpayno is null"[/tt]

PS It is generally considered poor practice to store calculated fields in tables.

 

[lars7]

Hi Remou,
I got an error message with the code "You canceled the previous operation" and I'm not getting a count from varx will debugging.

 

[Remou]

Is that all of your code? I tried this and it seemed to work:
[tt]varx = DCount("*", "[Trust Staff]", "Pay_Number = " & Me.[payno])
CurrentDb.Execute "UPDATE [trust staff] SET additpayno= [red]" & varx & " [/red]WHERE Pay_Number= " & Me.payno & " and additpayno is null"[/tt]

I had forgotten to put varx in as a variable in the last example.

 

[lars7]

Hi Remou,
Yes that's all the code there is. I got the same error message again and the debugging is not showing a count for varx instead it's showing ="" but the paynumber is showing where it should.

 

[Remou]

I missed another point, you are defining varx as a string. I think it should be numeric or a variable.
Dim varx As Variable

However, I have not been able to reproduce the bit about cancelling the previous operation, which is why I wondered if there was any other code associated with this form.

 

[lars7]

Hi again,
I am getting an error message but this time it says "User-defined type not defined" with the yellow line highlighted:


[highlight]Private Sub Form_Close()[/highlight]
Dim varx As Variable

varx = DCount("*", "[Trust Staff]", "Pay_Number = " & Me.[PAYNO])
CurrentDb.Execute "UPDATE [trust staff] SET additpayno= " & varx & " WHERE Pay_Number= " & Me.PAYNO & " and additpayno is null"

 

[Remou]

I'm just not awake.

Dim varx As Variant

:-(

 

[PHV]

If by chance Pay_Number is NOT defined as numeric in Trust Staff:
Dim varx
varx = DCount("*", "[Trust Staff]", "Pay_Number=[tt][!]'[/!]"[/tt] & Me!PAYNO[!] & [tt]"'"[/tt][/!])
CurrentDb.Execute "UPDATE [Trust Staff] SET additpayno=" & varx & " WHERE Pay_Number=[tt][!]'[/!]"[/tt] & Me!PAYNO & [tt]"[!]'[/!][/tt] AND additpayno Is Null"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,
Pay number always starts with "G" so it's a text field. Thanks to you both for the help, I wanted to automate this as the users either put the wrong number in or forget completely to do so.

Private Sub Form_Close()
Dim varx As Variant
varx = DCount("*", "[Trust Staff]", "Pay_Number='" & Me!PAYNO & "'")
CurrentDb.Execute "UPDATE [Trust Staff] SET additpayno=" & varx & " WHERE Pay_Number='" & Me!PAYNO & "' AND additpayno Is Null"
End Sub