How do evaluate tables and insert record if none exist

[Kevin]

Any help would be appreciated,

Does anyone know how I can evaluate 2 different tables i.e. JobPurchases and
JobParts, and insert a record in the tables using the following code?

If there is already a record in the two tables I do not want to insert the
record. if a record does not exist then insert record.

DoCmd.RunSQL "INSERT INTO JobPurchases (WorkOrderID, EmplID
)" _
& "SELECT WorkOrderID, EmplID " _
& "FROM [Work Orders] WHERE WorkOrderID = " & Me.WorkorderID

DoCmd.RunSQL "INSERT INTO JobParts (WorkOrderID, EmplID )" _
& "SELECT WorkOrderID, EmplID " _
& "FROM [Work Orders] WHERE WorkOrderID = " & Me.WorkorderID


Thanks, Kevin

 

[John W. Vinson]

Any help would be appreciated,

Does anyone know how I can evaluate 2 different tables i.e. JobPurchases and
JobParts, and insert a record in the tables using the following code?

If there is already a record in the two tables I do not want to insert the
record. if a record does not exist then insert record.

DoCmd.RunSQL "INSERT INTO JobPurchases (WorkOrderID, EmplID
)" _
& "SELECT WorkOrderID, EmplID " _
& "FROM [Work Orders] WHERE WorkOrderID = " & Me.WorkorderID

DoCmd.RunSQL "INSERT INTO JobParts (WorkOrderID, EmplID )" _
& "SELECT WorkOrderID, EmplID " _
& "FROM [Work Orders] WHERE WorkOrderID = " & Me.WorkorderID


Thanks, Kevin

The simplest way to prevent duplicate entries is to put a unique Index on the
field or combination of fields which identify a duplicate. The insert will
fail and generate an error message, which you can trap if you use the Execute
method instead of RunSQL:

On Error GoTo Proc_Error
Dim strSQL1 As String, strSQL2 As String
Dim db As DAO.Database
Set db = CurrentDb
strSQL1 = "INSERT INTO JobPurchases (WorkOrderID, EmplID)" _
& "SELECT WorkOrderID, EmplID " _
& "FROM [Work Orders] WHERE WorkOrderID = " & Me.WorkorderID
strSQL2 = "INSERT INTO JobParts (WorkOrderID, EmplID )" _
& "SELECT WorkOrderID, EmplID " _
& "FROM [Work Orders] WHERE WorkOrderID = " & Me.WorkorderID
db.Execute strSQL1, dbFailOnError
db.Execute strSQL2, dbFailOnError

Proc_Exit:
Exit Sub
Proc_Error:
<check for duplicate entry error message and Resume Next if it is found,
otherwise issue appropriate warning messages>

 

[John W. Vinson]

John Thanks for the quick response!

Actually both tables may have numerous entries in them if parts or purchases
are are used against the job "WorkOrderID". But if no parts or purchases are
used againts the job I need to insert 1 record in both tables in order for
the calculations on my reports to work properly.

Ummm... I doubt that this is required. Blank "placeholder" records are
essentially never needed. I suspect that the queries you're using for the
calculations could be revised to use "Outer Joins" in order to retrieve a
record even if there is no matching record in these tables. What is the nature
of the calculation? Could you post the SQL of the query, or the code?

I curently have the procedure running when the Work Order to be edited is
selected from a combobox on my main form and would like the code to determine
if either of these tables have entries and if not insert a single record in
each when needed.

Again... DON'T insert placeholder records just for this purpose. Fix the
calculations instead.

 

[Kevin]

Basically, I just want the code to check both tables for the WorkorderID and
if they do not already contain the WorkorderID, add the records to both
tables. if the tables contain at least 1 instance of the WorkorderID then do
nothing.

Kevin

 

[Kevin]

Unfortunatly, there are several reports, and many of them are a main report
with 3-4 subreports, the overall problem that I have is that if one of the
subreports does not have a record then the calculations in the report footer
ends up #Error. as long

Here is the SQL for one of the two subreports that generally cause the error
do to no nentry.

SELECT JobParts.WorkorderID, JobParts.PartQty, PartsTbl.Desc,
PartsTbl.NetPrice, ([JobParts]![PartQty]*[PartsTbl]![NetPrice]) AS [Total
Net], ([JobParts]![PartQty])*([PartsTbl]![NetPrice]*[PartsTbl]![MultX]) AS
[Total Retail], PartsTbl.MultX
FROM PartsTbl INNER JOIN JobParts ON PartsTbl.PartID = JobParts.PartID;

 

[John W. Vinson]

Here is the SQL for one of the two subreports that generally cause the error
do to no nentry.

Try changing it to

SELECT JobParts.WorkorderID, JobParts.PartQty, PartsTbl.Desc,
PartsTbl.NetPrice, (NZ([JobParts].[PartQty])*[PartsTbl].[NetPrice]) AS [Total
Net], NZ([JobParts].[PartQty])*([PartsTbl].[NetPrice]*[PartsTbl].[MultX]) AS
[Total Retail], PartsTbl.MultX
FROM PartsTbl LEFT JOIN JobParts ON PartsTbl.PartID = JobParts.PartID;

Note the LEFT JOIN (return all records in JobParts and any matching records in
PartsTbl) and the NZ() functions to convert NULL values to 0.

 

[Kevin]

Thanks John for your help however, in my reports the master and child links
are the WorkorderID, if a record does not exist in the JobParts tbl and the
JobPurchases tbl then nothing is returned. Sorry to be such a rock! The NZ
would probably work if if there was a record in the tables with the
WorkorderID but there is not.

Here is the SQL for one of the two subreports that generally cause the error
do to no nentry.

Try changing it to

SELECT JobParts.WorkorderID, JobParts.PartQty, PartsTbl.Desc,
PartsTbl.NetPrice, (NZ([JobParts].[PartQty])*[PartsTbl].[NetPrice]) AS [Total
Net], NZ([JobParts].[PartQty])*([PartsTbl].[NetPrice]*[PartsTbl].[MultX]) AS
[Total Retail], PartsTbl.MultX
FROM PartsTbl LEFT JOIN JobParts ON PartsTbl.PartID = JobParts.PartID;

Note the LEFT JOIN (return all records in JobParts and any matching records in
PartsTbl) and the NZ() functions to convert NULL values to 0.