This IDC code prevents duplicate records from being entered unless the LocationBarcode starts with an 'L' (case is ignored). Notice the use of 2 Select statements.

SQLStatement:
+ INSERT INTO [Item Transactions] ( 
+  ItemID, ProductID, [InventoryItem Barcode], 
+  PersonID, TransactionTypeID, [Transaction Date], 
+  [Location Barcode] )
+ SELECT Items.ItemID, Items.ProductID, Items.BarCode, 
+  '%PersonID%' AS Person, 'loc' AS TransType,  Now() AS TransDate, 
+  '%LocationBarcode%' AS LocBarcode
+ FROM  Items
+ WHERE Items.Barcode = '%ItemBarcode%'
+   AND Items.ItemID NOT IN 
+ (SELECT ItemID
+  FROM [Item Transactions]
+  WHERE [Location Barcode] = '%LocationBarcode%'
+    AND Left('%LocationBarcode%', 1) <> 'L'
+ );

Normally, I use the MS Access query builder to write the SQL for me ... then I make simple mods (like adding the plus signs - +) by hand. However, notice that the MS Access query builder is not able to handle the

WHERE Field1 not in (select ...)

• Microsoft Jet SQL is an excellent reference - Select statement
  Use a FROM .. IN .. clause to join tables in different mdb files.
  Use a WHERE .. NOT IN (SELECT .. ) clause to limit the results.

• My 3 IDC/HTX pages have several examples. These examples demonstrate how to use the VBA commands Format and iif in a Select query.

• The World Wide Web Database Developer's Guide with Visual Basic® 5 contains good information on The IIS Internet Database Connector (IDC). The associated SQL reference covers MS Access, SQL Server, and their differences.

URL: http:// cpcug.org / user / clemenzi / technical / Databases / SQL.html