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.
(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.
