Form Check for duplicate record before update?

[jonthequik]

I'll start by saying that I've used Access for a while just as a more powerful Excel. I haven't tinkered with the VB side of it. I tried searching for the answer to this puzzle in the FAQs and in the forums, but didn't find exactly what I was looking for.

I have two tables. One that tracks items and another that tracks specifications on those items. Many of the specification records are duplicates.

What I build was a form that allowed me to enter the specs of the item, and in a sub-form enter the item itself. This way if I had multiple items that had the same specs, I could enter the specs once, and all of the items.

Now, I'd like to be able to enter the specs and have the form look to see if the each of the specs were already in the database before adding a new record. One way I thought to do this was to have a new field in the specs table that creates a checksum of some sort of each value in the specs table. That way if my form's checksum is the same as the records, it can just add relationship between the item and that record. Otherwise it needs to add a new specs record.

My problem is that I was told never to store calculations in a database. Also, my form updates on the fly, or as I tab into the next record, so is there a built in way to do this check before updating?
Now I

Jonathan Hannan
Computer Repair, Webdesign
HTML, CGI, PERL, JavaScript, XML

 

[TheAceMan1]

How are ya jonthequik . . .

Post an example of specifications for an item! In realworld terms, your working backwards ... specifications don't have items ... [blue]items[/blue] have specifications!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

a built in way to do this check before updating
The BeforeUpdate event procedure of the form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jonthequik]

Here is a screenshot of my form. I know specs don't have items, When I generated the form, i had it create a sub-form so that I could enter information for an item and its specs at the same time. I wanted to try to keep the specs separate from the items because of the frequency of duplicate specs.

I have been reading up and It looks like I might be able to perform what I need if I can get the form to first query the database for any records that contain the exact same data as the form.

 

[CindyK]

When you say specs - do you mean EACH of those "things" on the form? Bonus ID, bonus cards... every single field?? If you were to sort your specification table by the BONUS ID are you saying that you have records that are TOTAL duplicates? Not even the BONUS ID field? (I'm guessing that might be an autonumber field, which is different for each record even if all other specifications are the same.)

What is the relationship now between your spec table and your item table?

You could use Access's filter by form to plug in all those specifications and then find records that meet that criteria... no code required. Check out Access help on the "filter by form". However - doing that would then mean if no records were found, you're stuck retyping everything in order to get THAT new specification in the database (or maybe writing code that then takes whatever you have typed and makes a new record out of that).

It does sound crazy to be entering specifications before you look for items - but if that's what you need/want to do - another possibility that would search for matching specifications (and easily allow an add of those specs if necessary) would be to make a copy of your main form - remove the data source - and then as each field is filled in above, a list of matching records in your specification table gets populated in a subform - using dynamic SQL to populate the subform.

???