Help with SQL UPDATE query

[Guest]

Hi all,
I am using an SQL command to update a record and can get it to do so
perfectly. However, I would like to put in an error check that will check to
see if a value already exists so that nothing will be overwritten. My SQL is
as follows:

DoCmd.FindRecord "Item_ID", , True, , True
DoCmd.RunSQL "UPDATE [IKR Items] " & _
"SET [IKR Items].Shipped = Date(), [IKR Items].[Shipped
By] = Employee.Value, [IKR Items].[Shipping Number] = Now() " & _
"WHERE [IKR Items].[Item #] = Item_ID "

What is happening is there is a combo box that has a field (Item_ID) that is
scanned via a bar code scanner - it fires an AfterUpdate event (above) and
updates the fields Shipped, Shipped By, and Shipping Number. What I would
like it to do is to update if all 3 fields are null, but to pop up a message
warning and not do any updates if there are values in these fields.
Please help, thanks!
-gary

 

[Bob Hairgrove]

Hi all,
I am using an SQL command to update a record and can get it to do so
perfectly. However, I would like to put in an error check that will check to
see if a value already exists so that nothing will be overwritten. My SQL is
as follows:

DoCmd.FindRecord "Item_ID", , True, , True
DoCmd.RunSQL "UPDATE [IKR Items] " & _
"SET [IKR Items].Shipped = Date(), [IKR Items].[Shipped
By] = Employee.Value, [IKR Items].[Shipping Number] = Now() " & _
"WHERE [IKR Items].[Item #] = Item_ID "

The last line above should probably look like this:
"WHERE [IKR Items].[Item #] = " & Item_ID

If [Item #] is a text field (barcodes usually are), then you will need
embedded quotes:
"WHERE [IKR Items].[Item #] = " & """" & Item_ID & """"

This applies to the criteria in the DCount statements below just as
well...

What is happening is there is a combo box that has a field (Item_ID) that is
scanned via a bar code scanner - it fires an AfterUpdate event (above) and
updates the fields Shipped, Shipped By, and Shipping Number. What I would
like it to do is to update if all 3 fields are null, but to pop up a message
warning and not do any updates if there are values in these fields.
Please help, thanks!
-gary

I would suggest something like this;

If (DCount("*","[IKR Items]","[Item #]=" & Item_ID _
& " And [Shipped] Is Not Null") > 0) _
Or (DCount("*","[IKR Items]","[Item #]=" & Item_ID _
& " And [Shipped By] Is Not Null") > 0) _
Or (DCount("*","[IKR Items]","[Item #]=" & Item_ID _
& " And [Shipping Number] Is Not Null") > 0) Then
' show your warning
Exit Sub
Else
' do the updates
End If

Alternatively, you could just add the same criteria to your SQL update
statement. However, you wouldn't have the chance to show a message to
the user.

Also, you could use the BeforeUpdate event of the combo box which can
be cancelled.