Search if same combination is already in the table, add if not found.

[DesoxyN]

Hi,

I have made some changes to the file that I think would make it faster. Because you would be using this to add new records I think it would make sense to save with each record the combination qualifier (basically the search entry in format <Label1 Label2 Quantity:Label1 Label2 Quantity:....>. I know some will say that it goes against the normalization rules but it is accurate at the time of the data entry. Doing this would save having to dynamically build that qualifier for every combination every time you do a search using the slow qryCombinationCondensed query. We are now checking for the existence of the search string straight into the table SHUNK_DATA_NEW.

I also added some code to validate the search string to look for new entries in Label1 (notice I don't use spaces in field or object names as it makes it easier to program against them, no need to wrap them in square brackets) - that would have help you spot the misspell earlier today.

As for tutorials I think you should start with reading about database normalization first, then start with some basic SQL and VBA tutorials, I don't have specific ones but Google would give you plenty of options (I have some nice Access resource links on my website but I haven't updated that in quite a while).

Cheers,

Hi, the latest version works much better, thanks for the latest modifications. Have some ideas for if and when you could take a look at it;

1- Empty search pops out a runtime error.
2- Instead of having the combination id with the SK* prefix, it would be much better to have them just 1,2,3 etc. I rewrote the first part of the database to give you an example, you also don't need to add the whole database, just the ones in the example database would suffice as I will go through all the combinations one by one because the database already has some duplicates.

exampledatabase.zip

[Gicu]

Here you go.

Cheers,