Hi Gina
It would REALLY help if you told us in what way it is not working - what
error is being raised (if any) and on what line of code, and if no error is
being raised then what is happening or not happening that is not as you
expect.
However, I can take a guess that the problem is with one or both of these
lines:
.Fields("AssignedTo") = "" 'Clear the person's name from the record
.Fields("DateAssigned").Value = "" 'Clear the Assign date
You are setting both these fields to an empty string. I assume DateAssigned
is a date/time field so you can't set it to a string (which is text).
Further, the AllowZeroLength property of the AssignedTo field is probably No
(the default) so you can't set it to an empty string either.
Usually when a field looks blank, its value is not an empty string, but
Null.
Try replacing both instances of /""/ above with /Null/ (without the slashes
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11361
of course).
By the way, you called this "SQL script" but there is not an ounce of SQL in
it. It is actually VBA code (Visual Basic for Applications).
However, you might find that SQL is a better way to go. In your VBA code
you can construct a SQL command string to perform the update, and then
execute that SQL string.
The first step is to build a criteria string to identify which records to
update, then build a command string that looks like this:
Update Billing (fld1, fld2, fld3) values (val1, val2, val3) where ID in
(id1, id2, ...);
Try this:
Dim sSql as string, sIdList as String, vItem as variant
With Me!lstItems
For Each vItem in .ItemsSelected
sIdList = sIdList & .ItemData(vItem) & ","
Next vItem
End With
' we now have a string like this: "234, 345, 456,"
' remove the last comma
sIdList = Left(sIdList , Len(sIdList )-1)
' now add the rest of the SQL string
sSql = "Update Billing (Assigned, AssignedTo, DateAssigned) " _
& "values (0, Null, Null) where ID in ( " & sIdList & ");"
' now execute the SQL
CurrentDb.Execute sSql, dbFailOnError
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
"Gina"
wrote in message
news:0CD24052-8BF2-45FD-A569-3C1A1E23F0CE@microsoft.com...
> All,
>
> In the following SQL script I am trying to blank out the fields, but so
> far
> it isn't working. Can you point me in the right direction.
>
> Thanks!
>
> 'Setup the table and recordset to be edited
>
> ' strTablename = "Billing"
>
> Set db = CurrentDb
>
> Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)
>
>
>
> For Each i In Me![lstItems].ItemsSelected 'Determine the number of
> items
> selected in the list
>
> Criteria = Me![lstItems].ItemData(i) 'Assign the Customer Number a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11361
> variable
>
>
>
> With rst
>
> .MoveFirst
>
> .FindFirst "[ID] = " & Criteria & "" 'Locate a Customer Number that
> matches the one selected from the list
>
> .Edit
>
> .Fields("Assigned") = 0 'Uncheck the checkbox
>
> .Fields("AssignedTo") = "" 'Clear the person's name from the record
>
> .Fields("DateAssigned").Value = "" 'Clear the Assign date
>
> .Update
>
> End With
>
> rst.Close
>
> Next i 'Loop through the next record
>
> Set db = Nothing 'Unset the Database
>
> Set rst = Nothing 'Unset the recordset
>
>
>
> Me.lstItems.Requery
>
> Exit Sub
>
>
> --
> Gina
Our Donation Goal
: Why Donate? : Donate Today! : Donate Anonymously (ਗੁਪਤ) : Our Family of Supporters 
Linear Mode
