Highlighting a duplicated entry in a continuous form (1 Viewer)

Tim Bedborough

Registered User.
Local time
Today, 01:32
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:32
Joined
May 7, 2009
Messages
19,169
there is a same concept of what you want days before.
on the sample it check the New record if the Component and Machine number, already exists (have been entered)
on either machine1, machine2 or machine3.
 

Attachments

  • compoMachine.accdb
    704 KB · Views: 378

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:32
Joined
Feb 19, 2002
Messages
42,981
You can check in the price control if the combination of item and price has already been entered. If it has, you can cancel the update, undo the changes and position the form to the existing record. This gives the user no option to create a "duplicate" but I'm not sure it will be foolproof. If you elect to do this, you should probably add an unbound control to the form to hold the + quantity and not allow direct updates to the quantity once it has been saved. This prevents the user from decreasing the quantity and it prevents errors in addition. You would also have to ensure that the user couldn't enter a - sign in the + quantity field.

If the entry order puts the qty in front of the price, you can do the addition for the user automatically. There are multiple options. It depends on how rigid your rules are and what the client perfers.
 

Tim Bedborough

Registered User.
Local time
Today, 01:32
Joined
Nov 16, 2015
Messages
42
You can check in the price control if the combination of item and price has already been entered. If it has, you can cancel the update, undo the changes and position the form to the existing record. This gives the user no option to create a "duplicate" but I'm not sure it will be foolproof. If you elect to do this, you should probably add an unbound control to the form to hold the + quantity and not allow direct updates to the quantity once it has been saved. This prevents the user from decreasing the quantity and it prevents errors in addition. You would also have to ensure that the user couldn't enter a - sign in the + quantity field.

If the entry order puts the qty in front of the price, you can do the addition for the user automatically. There are multiple options. It depends on how rigid your rules are and what the client perfers.
Hi Pat, thanks. I'm the self taught developer as well as the client. Our rules are relatively relaxed. I had a play around with arnelgp previous post and example database. The example works a treat but I couldn't easily adapt it to my requirements. I don't necessary want to delete a new entry as a duplicate value could easily be relevant. I only want to search on the value as other free type data could throw up anomolies if I try to include those in the search criteria. It's also important that the search is on the filtered data not all the data in the table, i.e. I only want to check costs for this particular job not all the jobs we have. My playing with the previous post sort of got me in the right direction but I'm not sure i was filtering the data properly as it kept telling me a duplicate value was there (it wasnt for the individual job but could easily be in the unfiltered table for all the jobs).
You can check in the price control if the combination of item and price has already been entered. If it has, you can cancel the update, undo the changes and position the form to the existing record. This gives the user no option to create a "duplicate" but I'm not sure it will be foolproof. If you elect to do this, you should probably add an unbound control to the form to hold the + quantity and not allow direct updates to the quantity once it has been saved. This prevents the user from decreasing the quantity and it prevents errors in addition. You would also have to ensure that the user couldn't enter a - sign in the + quantity field.

If the entry order puts the qty in front of the price, you can do the addition for the user automatically. There are multiple options. It depends on how rigid your rules are and what the client perfers.
 

Tim Bedborough

Registered User.
Local time
Today, 01:32
Joined
Nov 16, 2015
Messages
42
there is a same concept of what you want days before.
on the sample it check the New record if the Component and Machine number, already exists (have been entered)
on either machine1, machine2 or machine3.
Thanks. Please see response to Pat in next post.
 

Tim Bedborough

Registered User.
Local time
Today, 01:32
Joined
Nov 16, 2015
Messages
42
You can check in the price control if the combination of item and price has already been entered. If it has, you can cancel the update, undo the changes and position the form to the existing record. This gives the user no option to create a "duplicate" but I'm not sure it will be foolproof. If you elect to do this, you should probably add an unbound control to the form to hold the + quantity and not allow direct updates to the quantity once it has been saved. This prevents the user from decreasing the quantity and it prevents errors in addition. You would also have to ensure that the user couldn't enter a - sign in the + quantity field.

If the entry order puts the qty in front of the price, you can do the addition for the user automatically. There are multiple options. It depends on how rigid your rules are and what the client perfers.

Hi Pat

This doesn't work but does it give you an idea of what I'm trying to achieve (or just confuses things)

1614081008214.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:32
Joined
Feb 19, 2002
Messages
42,981
Please don't post "pictures" of code. There is no way for us to copy and paste from a picture.
The dLookup() needs a compound crtieria clause:

" Cost = " Me.txtCost & " AND ItemID = " & Me.txtItemID
 
Last edited:

Tim Bedborough

Registered User.
Local time
Today, 01:32
Joined
Nov 16, 2015
Messages
42
Please don't post "pictures" of code. There is no way for us to copy and paste from a picture.
The dLookup() needs a compound crtieria clause:

" Cost = " Me.txtCost & " AND ItemID = " & Me.txtItemID
Hi Pat. Sorry about the pic. Thanks for help. I'll have a play.
 

Users who are viewing this thread

Top Bottom