Update field in specific records 1

[JR2913]

Using a pop-up form I am trying to select specific records (Child Service Users) from a list box (called Childlist) and then update a particular field (Dateended) in the table (tblServiceUse) based on the date selected in a text box(txtDateServiceEnd) on the form.

Using the following code the ServiceUseID (the key field in the tblServiceUse table) does not change so I can't get the specific records to match the ServiceUseID. How do I go through the ServiceUseIDs to find matches and thus update the required field?


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant
Dim val As Integer


Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblServiceuse&quot


For Each varItem In Me!ChildList.ItemsSelected
val = Me!ChildList.Column(0, varItem)
If rst!ServiceUseID = val Then
rst![Dateended] = Me!txtDateServiceEnd
rst.Update
End If
Next varItem

rst.Close
Set dbs = Nothing


I had played with using an Update Query for this task. I could get an update for all users of the service to function correctly but couldn't figure out how to use only the selected items in the list box to achieve the desired outcome.

If anyone can help with this I would be most grateful.

Many thanks

John R

 

[JR2913]

OK - solved it myself.

Amended the For-Next loop to

For Each varItem In Me!ChildList.ItemsSelected
val = Me!ChildList.Column(0, varItem)
CurrentProject.Connection.Execute "UPDATE tblServiceUse SET [Dateended] = #" & Me!txtDateServiceEnd & "# WHERE [ServiceUseID] = " & val
Next varItem

Works like a dream!

John R

 

[JR2913]

But not completely!

I want to add a log of when the item was changed and have changed the loop to:

For Each varItem In Me!AdultList.ItemsSelected
val = Me!AdultList.Column(0, varItem)
CurrentProject.Connection.Execute "UPDATE tblServiceUse SET [Dateended] = #" & Me!txtDateServiceEnd & "# WHERE [ServiceUseID] = " & val
CurrentProject.Connection.Execute "UPDATE tblServiceUse SET [Recupdated] = #" & Now & "# WHERE [ServiceUseID] = " & val
CurrentProject.Connection.Execute "UPDATE tblServiceUse SET [Updateby] = " & atCNames(1) & " WHERE [ServiceUseID] = " & val
Next varItem

When it gets to the last line in the loop it bombs out with an error message saying "No value for one or more parameters". I know the user is JR and that I can produce this on screen in a messagebox.

What is missing from the line to get it to add the user identification into the field?

John R

 

[beetee]

Is it possible [Updateby] has been misspelled?

 

[JR2913]

No - I had already checked that and have just done so again..... It is a text field.....

I just wonder if, in SQL, the function atCNames(1) requires expressing differently, or needs another parameter.

John R

 

[beetee]

Ah! That leads to another thought: if atCNames is returning a string, you should delimit it with single quotes (especially if it has embedded blanks), e.g.

CurrentProject.Connection.Execute "UPDATE tblServiceUse SET [Updateby] = '" & atCNames(1) & "' WHERE [ServiceUseID] = " & val

 

[JR2913]

Tried that code which now generates a different error message:

Syntax error in string in query expression "user'.

Interesting punctuation! I have checked that the apostrophes are in the right place..........

So near,yet so far.........

Any other ideas, beetee?

 

[beetee]

It would be very helpful if you could post the actual SQL string that is producing the error.

 

[JR2913]

You have it in the Execute line - this is the only SQL used in the procedure.

I have now tried splitting the SQL statements as follows:

strSQL1 = "UPDATE tblServiceUse SET [Dateended] = #" & Me!txtDateServiceEnd & "# WHERE [ServiceUseID] = " & val
MsgBox strSQL1
DoCmd.RunSQL strSQL1

strSQL2 = "UPDATE tblServiceUse SET [Recupdated] = #" & Now & "# WHERE [ServiceUseID] = " & val
MsgBox strSQL2
DoCmd.RunSQL strSQL2

strSQL3 = "UPDATE tblServiceUse SET [Updateby] = '" & atCNames(1) & "' WHERE [ServiceUseID] = " & val
MsgBox strSQL3
DoCmd.RunSQL strSQL3


The first two work fine, but the third statement only returns up to the first ' and the username - it requires a parameter if you take the two apostrophes out and, regardless, ignores the second half of the statement starting WHERE.

John R

 

[JR2913]

Solved it!

Using the following code in the loop, substituting Environ("username&quot for atCNames(1):

For Each varItem In Me!ChildList.ItemsSelected
val = Me!ChildList.Column(0, varItem)
strSQL1 = "UPDATE tblServiceUse SET [Dateended] = #" & Me!txtDateServiceEnd & "# WHERE [ServiceUseID] = " & val
strSQL2 = "UPDATE tblServiceUse SET [Recupdated] = #" & Now & "# WHERE [ServiceUseID] = " & val
strSQL3 = "UPDATE tblServiceUse SET [Updateby] = '" & Environ("username&quot & "' WHERE [ServiceUseID] = " & val

DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL3

Next varItem

Works like a dream



Thanks beetee for your time and inspiration!

John R

 

[beetee]

Anytime, my pleasure.

I can only guess there must have been some 'special' characters in CtNames (e.g. O'Malley) that messed up the SQL string.

 

[JR2913]

Not that I am aware of - I tried this on a couple of machines using various flavours of Windows and with different logon names - not much you can do with JR as a username! At least I thought that was the case........!

John R