Monthly limit in VBA?

C

cjnow

how can i limit my staff purchase monthly in the invoice form(which t
fill in product that they buy)?

Which mean staff need to register in customer form and hav to fill i
the monthly purchase there. and when come to the invoice form when the
need to buy thing, if they purchase more than the amount that they fil
in in the customer form, they cant proceed and need to do som
adjustment.


How can i limit them in the invoice form
_in_between_that_month,_and_with_the_same_customer_no._ where should
put the VBA command and how to write the command? do u understand wat
mean ? sorry for my poor english. hope u understand.


Thanks for helping here, im glad that i found this forum .Thank
 
M

Michel Walsh

Hi,


In the design of the table, with a CHECK constraint. A CHECK constraint
fires after INSERT or UPDATE (but not after DELETE). The SQL expression in
the (single) argument of the CHECK constraint is evaluated, after the
INSERT/UPDATE and if the expression returns true, or Null, the transaction
is committed, else, it is rolled back and an error is generated.

CurrentProject.Connection.Execute "ALTER TABLE tableName ADD CONSTRAINT
constraintNameUniqueInTheDatabase CHECK( sql_here ) "


would add such a constraint, to a table. Use DELETE instead of ADD to
delete the constraint. In Jet, you cannot temporary invalidate a constraint,
just have it, or not. You MUST use ADO, not DAO (neither the query designer,
based on DAO) to be able to define a CHECK constraint. As example, use
CurrentProject.Connection.Execute in the Immediate (Debug) window. There is
no User Interface telling you that a table has a CHECK constraint, or not.
The sql_here statement must not make call to the evaluation service (that
means, no purely VBA based function are allowed).


CHECK( 0 <= (SELECT SUM(amount) FROM myTable) )


as example, would "impose" that the sum under the field amount, of table, to
be >=0. If you need to refer to the same table than the one you add the
constraint, use alias for that table, in the sql_here.


CHECK( constant_amount >= (SELECT SUM(c.amount)
FROM tablename As c
WHERE
MONTH(c.dateTime) = MONTH(dateTime)
) )




Note: MONTH() does not require call to the evaluation service, but Nz( )
does, as example. iif is also define, internally, in Jet, so you can use iif
without having to call the evaluation service and thus, Nz(a, b) can be
replaced by iif( a IS NULL, b, a)




Back to your case, if the constant depends on the actual AccountNumber,
supplied in another table, Limits, then try something like (for the CHECK
constraint) :

CHECK(
(SELECT b.AccountLimit
FROM Limits As b
WHERE b.AccountNumber=AccountNumber)
=
Click to expand...

(SELECT SUM(c.amount)
FROM tablename As c
WHERE MONTH(c.dateTime) = MONTH(dateTime)
)
)




Having made the check() constraint part of the design of the table, it will
follow "every where" you use the table.

Since there is no UI for the check constraint, you may have to explore the
system tables to find if there are any implied. That makes the CHECK
constraint a little bit not-so-friendly.

Note the CHECK(), under MS SQL Server, only accept very limited expression,
in comparison. In fact, you can only refer, in that case, to fields of the
actual record! Basically, you can use

CHECK( myField >= 0 )

for a field level check,


CHECK( endingDate >= startingDate )

for a record level check

but, in MS SQL SERVER, you cannot make a inter-tables (or inter records)
level check (while you can, with Jet).




Hoping it may help,
Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


rectification, use DROP, not DELETE to remove a constraint:


CurrentProject.Connection.Execute "ALTER TABLE tablename DROP CONSTRANT
constraintName"


Vanderghast, Access MVP
 
Top