SubForm Update (fill after checking the details)

[ragu111]

PART_NO   AMOUNT     INV_NO
S434343   100        6007
F534534   120        6007
H543455   225
J785677     0
k545345     0


on command click



if INV_NO = null and AMOUNT = 0 then
msgbox "No valid amount available for invoice"

if INV_NO = not null and AMOUNT > 0 then
msgbox "no data found"

if INV_NO = null and AMOUNT > 0 then
RUN CODE

i have the code to update the INV_NO.

what i want is when i command click i want the invoice no to update only where the amount column value is > 0 and INV_NO column is blank.

otherwise give msgbox as above

 

[PHV]

Nothing may be = Null (ven a Null value).
Have a look at the IsNull function.

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

 

[TheAceMan1]

How are ya ragu111 . . .

Here are two sample lines:

[blue]If [purple][b]IsNull([/b][/purple]INV_NO[purple][b])[/b][/purple] and . . .
   [purple]or[/purple]
If [purple][b]Trim([/b][/purple]INV_NO & ""[purple][b])[/b][/purple] = "" and . . .[/blue]

See Ya! . . . . . .

 

[ragu111]

ok this is my code

Private Sub Inv_Generate_Click()

Dim III As String
Dim YYY As String

III = IIf(IsNull([ID]), 0, DCount("[Master_ID_Ref]", "CusCharges", "[Master_ID_Ref]=" & [ID]))

YYY = IIf(IsNull([ID]), 0, DCount("[Inv_No]", "CusCharges", "[Master_ID_Ref]=" & [ID]))

If III - YYY = 0 Then
   MsgBox "no data found"

Else

  Me.txtinv_no = Nz(DMax("[inv_No]", "CusCharges"), 0) + 1
Me.txtInv_Date = Format(Now(), "dd/mm/yyyy")

  DoCmd.RunSQL "UPDATE CusCharges SET CusCharges.Inv_Date = forms.cusmaster.txtInv_Date, CusCharges.Inv_No = forms.cusmaster.txtInv_No WHERE (((CusCharges.Inv_No) Is Null) AND ((CusCharges.Master_ID_Ref)=[forms].[cusmaster].[id]) AND ((CusCharges.Amount)>0));"

    MsgBox "Invoice No """ & Me.txtinv_no & """ updated", vbOKOnly, "Rg"


End If
End Sub

everything working fine but when the AMOUNT = 0 and INV_NO = NULL the msgbox gives a msg "Invoice No "xx" updated"

i want the msg as "No valid amount available for invoice"

Ragu

 

[TheAceMan1]

ragu111 . . .

Have a look at the [blue]Nz[/blue] function . . .

See Ya! . . . . . .

 

[ragu111]

yes, but no luck..

i need help only on msgbox, as the code is working fine.

ragu

 

[TheAceMan1]

[blue]. . . [purple]everything working fine[/purple] but when the AMOUNT = 0 and . . .[/blue]

I have [blue]great doubts about your SQL!

in any case have a look at the following:

[blue]   If III - YYY = 0 Then
      MsgBox "no data found"
   ElseIf IsNull([inv_NO]) And [AMOUNT] = 0 Then
      MsgBox "No valid amount available for invoice"
   Else
      Me.txtinv_no = Nz(DMax("[inv_No]", "CusCharges"), 0) + 1
      Me.txtInv_Date = Format(Now(), "dd/mm/yyyy")
      [green]'
      'The rest of the code
      '[/green]
   End If[/blue]

See Ya! . . . . . .