Hi All,
I have a Access 2013 database which imports a dataset from MS Excel, makes changes based on a set of rules (setup in another table and run via VBA using DAO recordsets), then exports the amended dataset. The rules need to be assigned a number for order of priority they are to be applied, which is done by the user via a form bound to the rules table.
How do I ensure that the order has no gaps or duplicates (except for 0)? Lets say I have 5 rules prioritised 1 to 5, then the user i.changes rule E to priority 2, then ii. deletes rule A (by setting priority to 0)...
The priority changes would be expected as follows:
Rule No. - Original Priority - After step i - After step ii
A.............1......................1..................0
B.............2......................3..................2
C.............3......................4..................3
D.............4......................5..................4
E.............5......................2..................1
FYI the form has a helper textbox, informing the user of the next available priority number (DMAX + 1 expression), and inputs the required priority number in another control which is bound to the table field. I understand this needs to be captured in the BeforeUpdate event for the bound control?
Can anyone help advise what the VBA should be to :
1. Validate the number input for new records is >= 0 and <= the Next Available Priority OR for existing records is >= 0 and < Next Available Priority
2. For new records, if the number input is > 0 and <= Next Available priority OR for existing records, if the number input is < Next Available priority then recalculate all existing priorities in the table that need to be changed
Thanks in advance
Tooley
I have a Access 2013 database which imports a dataset from MS Excel, makes changes based on a set of rules (setup in another table and run via VBA using DAO recordsets), then exports the amended dataset. The rules need to be assigned a number for order of priority they are to be applied, which is done by the user via a form bound to the rules table.
How do I ensure that the order has no gaps or duplicates (except for 0)? Lets say I have 5 rules prioritised 1 to 5, then the user i.changes rule E to priority 2, then ii. deletes rule A (by setting priority to 0)...
The priority changes would be expected as follows:
Rule No. - Original Priority - After step i - After step ii
A.............1......................1..................0
B.............2......................3..................2
C.............3......................4..................3
D.............4......................5..................4
E.............5......................2..................1
FYI the form has a helper textbox, informing the user of the next available priority number (DMAX + 1 expression), and inputs the required priority number in another control which is bound to the table field. I understand this needs to be captured in the BeforeUpdate event for the bound control?
Can anyone help advise what the VBA should be to :
1. Validate the number input for new records is >= 0 and <= the Next Available Priority OR for existing records is >= 0 and < Next Available Priority
2. For new records, if the number input is > 0 and <= Next Available priority OR for existing records, if the number input is < Next Available priority then recalculate all existing priorities in the table that need to be changed
Thanks in advance
Tooley