If Exists help

[anil]

Hi all
I need to append results from one table into other every week,So I want
to make sure that by mistake last weeks results(Already entered) are
not added again.

For that I have tried this query

SELECT tblImport.[SITE CODE], tblImport.[SAMPLE NO], tblImport.[REF
NO], tblImport.[SAMPLE DATE], tblImport.[PA NAME], tblImport.RESULT
FROM tblImport
WHERE (((tblImport.[SITE CODE]) not In (select Sitecode from qryIR))
AND ((tblImport.[SAMPLE NO]) not In (select labSampleNo from qryIR))
AND ((tblImport.[REF NO])Not In (select sampleID from qryIR)) AND
((tblImport.[SAMPLE DATE]) Not In (select sampletakenDate from qryIR))
AND ((tblImport.[ PA NAME]) not In (select ParameterName from qryIR))
AND ((tblImport.RESULT)Not In (select ResultValue from qryIR)));

Records from tblImport are appended in tblresults while matching the
sampleId from tblsample.
QryIR is join of tblResults and tblsample .Also in tblResults sample
No,Ref No,sample Date,PA Name and result can be many times so I need to
put trigger on all of these.

Since I am appending the results as well as updating the resultIsPass
In VBA.So I want to use code in VBA that only those results are
appended which are new and this week only to avoid any duplicacy.

can any one help me for using code like 'IF Exists' for these all
Field Values.I have tried but not sucessful.
Any other idea is appreciated.
thanks
anil

 

[AccessVandal via AccessMonster.com]

Try EXISTS or NOT EXISTS instead of NOT IN.

anil wrote:

 

[anil]

Dear
Thanks for your advice but I want to use the VBA code instead of query.

I know that I can make recordset using str=" QUERY" and then rst.open
str.

but if there is any programming skill used in VBA like (just an idea)

if exists((tblImport.sampleId = qryIR.[REF NO]) and
(tblImport.ParameterName)=(qryIR.[PA NAME])) then
msgbox"sampleId already there"
else
.......Append Results.....
end if

It would be much helpful.
I hope I make more clear
thanks
anil

 

[AccessVandal via AccessMonster.com]

Why don’t you use the Update statement with subquery? It’s much easier and
faster.

Dim strSQL As String
StrSQL = “Update table set ……. Not In (your query)
Docmd.SetWarnings False
Docmd.RunSQL “strSQLâ€
Docmd.SetWarnings True

Here is a sample for adding new records.
Assuming the Named Query is the result of the records you want to insert into
the table.

Dim rst As ADODB.Recordset ‘your tblimport
Dim rst1 As ADODB.Recordset ‘ your tblresults where you want to insert
Dim con As ADODB.Connection
Dim strSQL As String

Set con = CurrentProject.Connection
Set rst = New ADODB.Recordset

strSQL = “YourQueryName†‘or your select statement here

rst.Open strSQL, con, adOpenKeyset, adLockOptimistic
rst1.Open “tblresultsâ€,con,adOpenKeyset, adLockOptimistic

With rst
Do while Not rst.EOF
rst1.AddNew
rst1!Sitecode = rst![SITE CODE]
rst1!labSampleNo = rst![SAMPLE NO]
rst1!sampleID = rst![REF NO]
rst1!sampletakenDate = rst![SAMPLE DATE]
rst1!PerameterName = rst![PA NAME]
rst1!ResultValue = rst!RESULT
rst1.Update
rst.MoveNext
Loop
End With

rst.Close
rst1.Close
con.Close
Set rst = Nothing
Set rst1 = Nothing
Set con = Nothing

However, if you wish to use the IF ELSE THEN, you’ll need to modify the code
to suit your needs.

rst.Open “tblImportâ€, con, adOpenKeyset, adLockOptimistic
rst1.Open “tblresultsâ€,con,adOpenKeyset, adLockOptimistic

with rst1
Do While Not rst1.EOF
‘move rst to first record if rst1.EOF = false,repeat until rst1.EOF = False
‘and exit Loop
If rst.EOF = True Then rst.MoveFirst
If rst1![REF NO] = rst!sampleID Then ‘ assume record exist
If rst1![PA NAME] = rst!ParameterName Then
MsgBox “sample existâ€
rst.MoveNext ‘move to next record of tblImport
Else
rst1.AddNew ‘if no record in tblresults, insert as new record
rst1!Sitecode = rst![SITE CODE]
rst1!labSampleNo = rst![SAMPLE NO]
rst1!sampleID = rst![REF NO]
rst1!sampletakenDate = rst![SAMPLE DATE]
rst1!PerameterName = rst![PA NAME]
rst1!ResultValue = rst!RESULT
rst1.Update
rst.MoveNext ‘move to next record of tblImport
rst1.MoveNext ‘move to next record of tblresults and to match next record
End If
End If

You’ll need to check this code and set breakpoints when the code is running.

 

[AccessVandal via AccessMonster.com]

Note the typo in

with rst1
Do While Not rst1.EOF
‘move rst to first record if rst1.EOF = false,repeat until rst1.EOF = False
‘and exit Loop
If rst.EOF = True Then rst.MoveFirst

It should be,
‘move rst to first record if rst1.EOF = false,repeat until rst1.EOF = True