Find if Record Exists, if it doesn't create it.

  • Thread starter Thread starter Pringle9984
  • Start date Start date
P

Pringle9984

Basically I want to do something like this..

If Record X Exists The
Run SQL Update Quer
Els
Create Record
Run SQL Update Quer
End I

Basically the query updates the Qty of a particular item on a Van, bu
it needs to check whether that item exists on the Van first. Thus i
will need to find if a record exists where 'Location' = 'Van 1' an
'Material' = 'Aerial' or similar, if no such record exists it wil
need to create one (with a Qty of 0) before running the Query

I've briefly looked at DLOOKUP but I don't know if I'd be able to us
that to find a record based on two criteria

Any help would be greatly appreciated
 
You can use a complex WHERE condition in a DCount() or DLookup(),
something like this untested air code:

Dim strLocation As String
Dim strMaterial As String
Dim strSQL As String
Dim lngQTY As Long

strLocation = "Van 1"
strMaterial = "Aerial"
strQty = 99

If DCount("1", MyTable, "(Location='" & strLocation _
& "') AND (Material='" & strMaterial & "')") = 0 Then

'Build SQL statement to append new record
strSQL = "INSERT INTO MyTable (Location, Material, Qty) " _
& "('" & strLocation & "', '" & strMaterial & "', " _
& lngQty & ");"

Else

'Build SQL statement to update existing record
strSQL = "UPDATE MyTable SET Qty = " & lngQty _
& " WHERE (Location='" & strLocation _
& "') AND (Material='" & strMaterial & "');"
End If

'Execute the statement
DBEngine(0)(0).Execute strSQL, dbFailOnError

To append or update the record I'd use code to build


On Sun, 20 Mar 2005 00:20:08 -0600,
 
Back
Top