Argument Not Optional

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

This was working fine untill I changed the form and table to a variable.(
frm As Form.tbl As DAO.TableDef.) Now I'm getting an Argument Not Optional
message. Any help appreciated.
Thanks DS

Public Function NOEX(frm As Form, tbl As DAO.TableDef) 'Item Totals No
Discounts
NOEX = Round(Nz(DSum("(CDQuantity*CDPrice)", "tbl.NAME", "CDCheckID = " &
frm!TxtCheckID.Value & "AND CDDiscountDP = 0"), 0), 2)
End Function

Public Function DOLNEX(frm As Form, tbl As DAO.TableDef) 'Item Totals With
Dollar Discounts No Tax
DOLNEX = Round(Nz(DSum("(CDQuantity*CDPrice)+CDDiscountAmount", "tbl.NAME",
"CDCheckID = " & frm!TxtCheckID.Value & " " & _
"AND CDDiscountDP = 1 AND CDDiscountWhere ='A' AND CDKillTax = -1"), 0), 2)
End Function

Public Function SUBEX() As Currency
SUBEX = NOEX() + DOLNEX()
End Function
 
So I tried this and I'm getting "Expected List Separator" at the( frm AS
Form, tbl AS DAO.TableDef)
Any help is appreciated.
Thanks
DS

Public Function SUBEX() As Currency
SUBEX = NOEX(frm As Form, tbl As DAO.TableDef) + DOLNEX(frm As Form, tbl As
DAO.TableDef)
End Function
 
You'll have to pass the frm and tbl references to SUBEX, and then have SUBEX
pass them to the other functions:

Public Function SUBEX(frm As Form, tbl As DAO.TableDef) As Currency
SUBEX = NOEX(frm, tbl) + DOLNEX(frm, tbl)
End Function

Note that your reference to tbl.Name is incorrect in both NOEX and DOLNEX.
It should be:

Public Function NOEX(frm As Form, tbl As DAO.TableDef)
NOEX = Round(Nz(DSum("(CDQuantity*CDPrice)", tbl.NAME, _
"CDCheckID = " & frm!TxtCheckID.Value & "AND CDDiscountDP = 0"), 0), 2)
End Function

Public Function DOLNEX(frm As Form, tbl As DAO.TableDef)
DOLNEX = Round(Nz(DSum("(CDQuantity*CDPrice)+CDDiscountAmount", _
tbl.NAME, "CDCheckID = " & frm!TxtCheckID.Value & " " & _
"AND CDDiscountDP = 1 AND CDDiscountWhere ='A' AND CDKillTax = -1"), 0),
2)
End Function

Actually, if there's a chance that the table name might include a blank,
make that

Public Function NOEX(frm As Form, tbl As DAO.TableDef)
NOEX = Round(Nz(DSum("(CDQuantity*CDPrice)", "[" & tbl.NAME & "]", _
"CDCheckID = " & frm!TxtCheckID.Value & "AND CDDiscountDP = 0"), 0), 2)
End Function

Public Function DOLNEX(frm As Form, tbl As DAO.TableDef)
DOLNEX = Round(Nz(DSum("(CDQuantity*CDPrice)+CDDiscountAmount", _
"[" & tbl.NAME & "]", "CDCheckID = " & frm!TxtCheckID.Value & " " & _
"AND CDDiscountDP = 1 AND CDDiscountWhere ='A' AND CDKillTax = -1"), 0),
2)
End Function
 
Douglas, Once again Thank You,,,I coudn't figure that out for the life of
me! This is something that I'm going to use a lot in the future, othing
beats learning!
Thank You
DS
 
Back
Top