Tim Bedborough
Registered User.
- Local time
- Today, 17:31
- Joined
- Nov 16, 2015
- Messages
- 42
Hi all. Hope this makes sense.
I have a master table (tblJobs) and sub table (tblPlant). tblJobs contains the primary data about a job. tblPlant is linked so that plant costs can be added to the job. Data is entered using a single form for tblJobs (i.e. the job) and then a sub form linking the unique ID (primary key/autonumber). The sub form is a continuous form. There may be multiple plant costs for 1 job.
In order to try and avoid duplicating data input rather than search for a cost value each time I was trying to get an after update VBA event procedure to check the cost values in the filtered sub form and whether they are the same as a previous entry (for the filtered sub form not the whole table, tblPlant).
Duplicate entries are fine because we may have a £10 cost for plant 1 and a £10 cost for plant 2 but it would be useful on the larger forms that a check could be done and give the user a message to say the same value has already been entered and then maybe going to the duplicate record to make sure it is OK to add again.
I did some homework and was heading towards DCount but couldn't make it work based on the table as a whole or on the filtered data. The field/control I want to check is called Cost and format is Currency.
I'm not sure conditional formatting is the answer as I want to be able to give the user a message rather than simply highlight everything duplicated which might be offscreen, i.e. you need to scroll to see all the duplicated entries.
I also read something about how checking may slow down the efficiency as a check would be done on all the data (filtered data hopefully) every time a new record was created.
Any thoughts or feedback appreciated please. Thanks.
I have a master table (tblJobs) and sub table (tblPlant). tblJobs contains the primary data about a job. tblPlant is linked so that plant costs can be added to the job. Data is entered using a single form for tblJobs (i.e. the job) and then a sub form linking the unique ID (primary key/autonumber). The sub form is a continuous form. There may be multiple plant costs for 1 job.
In order to try and avoid duplicating data input rather than search for a cost value each time I was trying to get an after update VBA event procedure to check the cost values in the filtered sub form and whether they are the same as a previous entry (for the filtered sub form not the whole table, tblPlant).
Duplicate entries are fine because we may have a £10 cost for plant 1 and a £10 cost for plant 2 but it would be useful on the larger forms that a check could be done and give the user a message to say the same value has already been entered and then maybe going to the duplicate record to make sure it is OK to add again.
I did some homework and was heading towards DCount but couldn't make it work based on the table as a whole or on the filtered data. The field/control I want to check is called Cost and format is Currency.
I'm not sure conditional formatting is the answer as I want to be able to give the user a message rather than simply highlight everything duplicated which might be offscreen, i.e. you need to scroll to see all the duplicated entries.
I also read something about how checking may slow down the efficiency as a check would be done on all the data (filtered data hopefully) every time a new record was created.
Any thoughts or feedback appreciated please. Thanks.