Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Transactions, and passing Null

Status
Not open for further replies.

hmlhml

Technical User
Oct 25, 2011
31
GB
I'm afraid these are two separate questions. I'm a bit of a noobie so these are two subjects with which I struggle:

(1) Transactions: I don't understand how much I can wrap in a transaction, and what happens to the public exposure of any record made after a transaction has started, but before it is finished.

The scenario is this: User A is creating a new sales record, but the sale is to someone completely new, so at the same time he is creating a new customer record (Customers are not allowed to be duplicated). Imagine the two creations are wrapped in a single transaction, which terminates only after A has finished everything he feels like on the form, had a cup of tea, and finally pressed "I'm done".

Meanwhile User B is also processing something, and also attempts to create a customer record for the same customer. If "A"'s customer isn't exposed to the world until the transaction is complete, then A's creation will look fine until the transaction is finished, where suddenly the database will discover it has a duplicate, and panic, which isn't really fair on A because he should have been warned at the time of creation. If "A"'s customer is exposed as soon as it's created, then B will naturally assign his sale to the customer using the CustomerID created by "A" - but if A decides not to go ahead with completing his transaction, the CustomerID shouldn't exist.

What prevails? What happens to records created in a transaction if, between their creation and the end of the transaction, some other activity by another database user makes the record illegal? Does Access at least avoid assigning autonumbers that have already been used by another instance within a transaction?

(2) Passing null values. I don't think I understand this; every time I try to pass the contents of a control on a form that happen to be null, to any function, I get a complaint that I'm illegally assigning NULL. But Null can be quite meaningful! If there really is nothing in the box, I might want to tell the function this. At the moment I'm passing nz(Me!MyControl, -1) and then testing on arrival in the function whether the parameter is -1, which is tantamount to setting up my own internal "NULL" value. I'm paranoid that one day Microsoft will allow Autonumber things to have a value of -1 rather than their current, incrementing positive values, and then everything will fall apart because -1 might be the value of a valid entry in my original control. Is it possible programmatically to pass NULL? If so, when?

Sorry if this is a stupid question.
 
Is it possible programmatically to pass NULL
Yes, declare the parameter in the function As Variant
In the body of the procedure use the IsNull function to test for Null value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Number 2... The only way to pass null is if the function or procedure input variable datatype is VARIANT. The other datatypes are scrict not database datatypes that do not allow nulls.

Number 1... So either the database indexes allows the new duplicate customer or it doesn't... If not the transaction including the order would fail. Two ways to look at this in my mind... 1 Enter all contacts and have a customer flag. Set it when an order is made. OR 2 have the code run two transactions. I am assuming that if there is an order there is intention to have a customer record... If you detect a duplicate, you can always prompt the user to assign to the other new customer or continue with a new one before begining the order transaction.
 
Thanks so much, both! Suddenly NULL becomes a much more usable thing. Appreciated...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top