I was hoping I could get some help with the scenario below:
This is based on a Call logging system created in MSAccess 2000.
We have User1 and User 2. There is a table called Call and a table called Company.
Call - Stores a new call in the system.
Company - Stores a Call_Ref Counter ID against each company.
When a new Call is added the Call_Ref ID is incremented by 1.
The Users can create a new Call in a form called "Add New Call". The Form is linked to the Call Table.
Goes into "Add New Call" Screen. Selects a company from the drop down box. A Call_Ref Field is updated (reads the value of the Counter against the Company name in table Company) Rest of the details are filled in.
Goes into "Add New Call" Screen. At the same time as User1. Selects the same company
from the drop down box. A Call_Ref Field is updated (reads the value of the Counter against the Company name in table Company) Rest of the details are filled in.
As User1 has not yet saved the Company Counter has not yet incremented. So the Call_Ref ID's are the same for User1 and User2.
User1 Saves - Call Created with Call_Ref i.e 100 in table Call. Company Counter Incremented by 1.
User2 Saves - Call Created with the same Call_Ref as User1 (100) in table Call. Company Counter Incremented by 1.
This creates Duplicate Call_Ref ID's
against a company in table Call. The Call_Ref ID for a company needs to be unique.
I want to lock the Record in the Company table that matches the selected Company from the drop down box in “Add New Call” Screen.
User1 chooses company Microsoft from the drop down box.
Looks up Call_Ref counter value in table Company against Microsoft
Populates Call_Ref field in “Add New Call Screen”
User2 comes in. Selects Microsoft.
Message displays saying record locked please wait.
Call_Ref ID incremented in table Company.
User2 tries to select Microsoft again.
No problem as record is now unlocked.
Is there a way I can do this with DAO or ADO?
Code examples would be greatly appreciated. As I’m new to VB
and unsure on syntax. :)