"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<qI*****************@newssvr19.news.prodigy.c om>...
str = "UPDATE tblSMSchedule SET tblSMSchedule.strNotes = '" & strBody
& "'WHERE tblSMSchedule.cntID = (Forms!frmOEOrder!ListSchedule)"
DoCmd.RunSQL (str)
First, a couple of questions. Is the form frmOEOrder open when you try to do
this and is ListSchedule a multiselect listbox?
Assuming yes and no respectively above, then try to concatenate the value
in.
str = "UPDATE tblSMSchedule SET tblSMSchedule.strNotes = '" & strBody &
"'WHERE tblSMSchedule.cntID =" & Forms!frmOEOrder!ListSchedule
If the value is text instead of a number.
str = "UPDATE tblSMSchedule SET tblSMSchedule.strNotes = '" & strBody &
"'WHERE tblSMSchedule.cntID =""" & Forms!frmOEOrder!ListSchedule & """"
What you had would work well if you assigned it to the SQL property of a
stored query, then ran that.
--
Wayne Morgan
Microsoft Access MVP
"Dave Hopper" <da**********@datelnet.co.uk> wrote in message
news:af**************************@posting.google.c om... Hi
I am using the following SQL to retrieve a value in a list box using a
unique ID held in the list box call cntID. The list box is used on an
order form to list appointments that have been made for service staff.
My problem is the SQL is not retrieving the value of the listbox. I
have tested the SQL by entering a fixed value of a valid entry in the
listbox and it works fine, but as soon as I replace the fixed value
with (Forms!frmOEOrder!ListSchedule) it fails to pick up anything. I
have a feeling that I need to address the row in the list box and then
loop through all the entries, but I don't know the syntax
This is the SQL
str = "UPDATE tblSMSchedule SET tblSMSchedule.strNotes = '" & strBody
& "'WHERE tblSMSchedule.cntID = (Forms!frmOEOrder!ListSchedule)"
DoCmd.RunSQL (str)
What am I doing wrong ?
Dave Hopper
Thanks to both of you. Linda, your code really helped me, but! ...
it won't loop through each entry in the lsit box(or indeed return any
entry) unless you highlight it. I am now finding the correct number
of appointments in the tblSMSchedule though, so thats a bonus!
I thought it maybe useful to bullet point what I am trying to do.
I am running this code to update an MS Access table (tblSMSchedule)
with appointments from a Public Folder calendar in an on-open form
event. If an appointment has been deleted in the public folder, the
code deletes it (this works fine), if an appointment is updated the
code finds the appointment associated with the order and updates it
(doesn't work fine!). With the latter, it appears to find the correct
appointment, but because I can't retrieve the assocociated unique ID
(cntID)in the list box, it either doesn't update any entry, or updates
an entry only if it's highlighted.
So, I am pretty sure that the code is finding the correct data and
wants to write it, it just can't find the unique ID (cntID) in the
list box to write it too.
I have posted all my code including your snippet, so you can see whats
going on.
Any ideas' ?
Kind regards
Dave
Option Compare Database
Public Function ImportAppointmentsTest()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblSMSchedule")
Dim Prop As Outlook.UserProperty
Dim objOL As New Outlook.Application
Dim objNS As Outlook.NameSpace
Dim strFind As String
Dim objCalFolder As Outlook.mapiFolder
Dim AllPublicFolders As Outlook.mapiFolder
Dim MyPublicFolder As Outlook.mapiFolder
Dim colCalendar As Outlook.Items
Dim objAppt As Outlook.AppointmentItem
Dim db As Database
Dim rsAppointmentsRecords As Recordset
Dim str As String
Dim TableName As String
Set db = CurrentDb
Dim myOlApp
Dim mNameSpace
Dim MyItem
Dim strMsg
Dim strPublicFolder
Dim strSubject
Dim strStart
Dim strEnd
Dim strBody
Dim strLocation
Dim strRequiredAttendees
Dim strCategories
Dim strBillingInformation
Dim strShow
Dim strUniqueID
Dim val As Integer, i As Integer
Dim ctl As Control
Const olAppointmentItem = 1
strPublicFolder = ("Office")
If Len(strPublicFolder) > 0 Then
Set objOL = CreateObject("Outlook.Application")
Set mNameSpace = objOL.GetNamespace("MAPI")
Set objCalFolder = mNameSpace.Folders("Public Folders")
Set AllPublicFolders = objCalFolder.Folders("All Public Folders")
Set MyPublicFolder = AllPublicFolders.Folders("Office")
Set colCalendar = MyPublicFolder.Items
strFind = "[Billinginformation] = " &
Forms!frmOEOrder!txtOrderNumber & ""
strShow = "" & Forms!frmOEOrder!txtOrderNumber & ""
Set objAppt = colCalendar.Find(strFind)
If objAppt Is Nothing Then
strSQL = "DELETE tblSMSchedule.strOrderNumber FROM
tblSMSchedule WHERE tblSMSchedule.strOrderNumber = '" & strShow & "'"
DoCmd.RunSQL strSQL
Else
Set rst = CurrentDb.OpenRecordset("tblSMSchedule")
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
If rst(19) = strShow Then
With objAppt
strLocation = .Location
strSubject = .Subject
strStart = .Start
strBody = .Body
End With
Set ctl = Forms!frmOEOrder!ListSchedule
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) Then
val = ctl.Column(0, i)
Exit For
End If
Next i
str = "UPDATE tblSMSchedule SET tblSMSchedule.strNotes = '" & strBody
& "' WHERE tblSMSchedule.cntID = " & val
DoCmd.RunSQL (str)
End If
rst.MoveNext
Loop
rst.Close
db.Close
Set objOL = Nothing
Set objNS = Nothing
Set objCalFolder = Nothing
Set colCalendar = Nothing
End If
End If
End Function
