retrieving a value from a list box

[Dave Hopper]

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!frmOEOrd er!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.s trNotes = '" & strBody
& "'WHERE tblSMSchedule.c ntID = (Forms!frmOEOrd er!ListSchedule )"
DoCmd.RunSQL (str)

What am I doing wrong ?

Dave Hopper

[Squirrel]

Hi Dave,

You just need to get the selected item.
Assuming your data is integer...

dim val as integer, i as integer
dim ctl as control

set ctl = Forms!frmOEOrde r!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.s trNotes = '" & strBody
& "' WHERE tblSMSchedule.c ntID = " & val
DoCmd.RunSQL (str)

HTH Linda

"Dave Hopper" <da**********@d atelnet.co.uk> wrote in message
news:af******** *************** ***@posting.goo gle.com...

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!frmOEOrd er!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.s trNotes = '" & strBody
& "'WHERE tblSMSchedule.c ntID = (Forms!frmOEOrd er!ListSchedule )"
DoCmd.RunSQL (str)

What am I doing wrong ?

Dave Hopper

[Wayne Morgan]

> str = "UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = '" & strBody

& "'WHERE tblSMSchedule.c ntID = (Forms!frmOEOrd er!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.s trNotes = '" & strBody &
"'WHERE tblSMSchedule.c ntID =" & Forms!frmOEOrde r!ListSchedule

If the value is text instead of a number.

str = "UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = '" & strBody &
"'WHERE tblSMSchedule.c ntID =""" & Forms!frmOEOrde r!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**********@d atelnet.co.uk> wrote in message
news:af******** *************** ***@posting.goo gle.com... 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!frmOEOrd er!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.s trNotes = '" & strBody
& "'WHERE tblSMSchedule.c ntID = (Forms!frmOEOrd er!ListSchedule )"
DoCmd.RunSQL (str)

What am I doing wrong ?

Dave Hopper

[Dave Hopper]

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message news:<qI******* **********@news svr19.news.prod igy.com>...

str = "UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = '" & strBody
& "'WHERE tblSMSchedule.c ntID = (Forms!frmOEOrd er!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.s trNotes = '" & strBody &
"'WHERE tblSMSchedule.c ntID =" & Forms!frmOEOrde r!ListSchedule

If the value is text instead of a number.

str = "UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = '" & strBody &
"'WHERE tblSMSchedule.c ntID =""" & Forms!frmOEOrde r!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**********@d atelnet.co.uk> wrote in message
news:af******** *************** ***@posting.goo gle.com...

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!frmOEOrd er!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.s trNotes = '" & strBody
& "'WHERE tblSMSchedule.c ntID = (Forms!frmOEOrd er!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 ImportAppointme ntsTest()

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenR ecordset("tblSM Schedule")

Dim Prop As Outlook.UserPro perty

Dim objOL As New Outlook.Applica tion
Dim objNS As Outlook.NameSpa ce
Dim strFind As String
Dim objCalFolder As Outlook.mapiFol der
Dim AllPublicFolder s As Outlook.mapiFol der
Dim MyPublicFolder As Outlook.mapiFol der
Dim colCalendar As Outlook.Items
Dim objAppt As Outlook.Appoint mentItem

Dim db As Database
Dim rsAppointmentsR ecords 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 strRequiredAtte ndees
Dim strCategories
Dim strBillingInfor mation
Dim strShow
Dim strUniqueID

Dim val As Integer, i As Integer
Dim ctl As Control
Const olAppointmentIt em = 1

strPublicFolder = ("Office")

If Len(strPublicFo lder) > 0 Then

Set objOL = CreateObject("O utlook.Applicat ion")
Set mNameSpace = objOL.GetNamesp ace("MAPI")
Set objCalFolder = mNameSpace.Fold ers("Public Folders")
Set AllPublicFolder s = objCalFolder.Fo lders("All Public Folders")
Set MyPublicFolder = AllPublicFolder s.Folders("Offi ce")
Set colCalendar = MyPublicFolder. Items

strFind = "[Billinginformat ion] = " &
Forms!frmOEOrde r!txtOrderNumbe r & ""
strShow = "" & Forms!frmOEOrde r!txtOrderNumbe r & ""
Set objAppt = colCalendar.Fin d(strFind)
If objAppt Is Nothing Then

strSQL = "DELETE tblSMSchedule.s trOrderNumber FROM
tblSMSchedule WHERE tblSMSchedule.s trOrderNumber = '" & strShow & "'"
DoCmd.RunSQL strSQL

Else

Set rst = CurrentDb.OpenR ecordset("tblSM Schedule")

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!frmOEOrde r!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.s trNotes = '" & strBody
& "' WHERE tblSMSchedule.c ntID = " & 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

[Wayne Morgan]

Dave,

Option Compare Database
I recommend you add Option Explicit below this. That will force you to
declare your variables and help catch spelling errors.
Dim myOlApp
Dim mNameSpace

Dim MyItem
Dim strMsg

Dim strPublicFolder
Dim strSubject
Dim strStart
Dim strEnd
Dim strBody
Dim strLocation
Dim strRequiredAtte ndees
Dim strCategories
Dim strBillingInfor mation
Dim strShow
Dim strUniqueID These are all being declared as Variants since a type hasn't been specified.
strPublicFolder = ("Office")

If Len(strPublicFo lder) > 0 Then This will always be true. Since you're setting the value yourself to Office
just before the If statement, the length will be 6.
Dim rsAppointmentsR ecords As Recordset rst was Dim'ed as DAO.Recordset, but not this one.
Else

Set rst = CurrentDb.OpenR ecordset("tblSM Schedule") The recordset was opened prior to the If statement, so opening it again here
shouldn't do anything.
rst.MoveLast
rst.MoveFirst

Do Until rst.EOF You are moving prior to seeing if there is anything to move to. Also, I
don't see where you are doing anything that would require you to "fully
populate" the recordset before you begin the looping, so the 2 move
statements could probably be omitted. If a recordset has no records, then
rst.EOF and rst.BOF are both true. The next statement is the Do Until, if
there are no records, then you will be at EOF and the loop won't run, so the
check is accomplished here.
If rst(19) = strShow Then This is in the ELSE part of the If statement. However the value of strShow
is being set before the ELSE; therefore, strShow = "", since the first part
of the If would not have been executed if you are in the ELSE part.
Set ctl = Forms!frmOEOrde r!ListSchedule
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) Then
val = ctl.Column(0, i)
Exit For
End If
Next i This will set the value of val and abort when the first selected item in the
listbox is found. I don't see where you are selecting/deselecting items, so
if you go through this again in the Do loop, it should set val to the same
value again.
rst.Close

db.Close Should be
rst.Close
Set rst = Nothing
Set db = Nothing

Also, you use CurrentDb to set rst, you set db to CurrentDb later then never
use it.
--
Wayne Morgan
Microsoft Access MVP
"Dave Hopper" <da**********@d atelnet.co.uk> wrote in message
news:af******** *************** ***@posting.goo gle.com...
Option Compare Database

Public Function ImportAppointme ntsTest()

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenR ecordset("tblSM Schedule")

Dim Prop As Outlook.UserPro perty

Dim objOL As New Outlook.Applica tion
Dim objNS As Outlook.NameSpa ce
Dim strFind As String
Dim objCalFolder As Outlook.mapiFol der
Dim AllPublicFolder s As Outlook.mapiFol der
Dim MyPublicFolder As Outlook.mapiFol der
Dim colCalendar As Outlook.Items
Dim objAppt As Outlook.Appoint mentItem

Dim db As Database
Dim rsAppointmentsR ecords 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 strRequiredAtte ndees
Dim strCategories
Dim strBillingInfor mation
Dim strShow
Dim strUniqueID

Dim val As Integer, i As Integer
Dim ctl As Control
Const olAppointmentIt em = 1

strPublicFolder = ("Office")

If Len(strPublicFo lder) > 0 Then

Set objOL = CreateObject("O utlook.Applicat ion")
Set mNameSpace = objOL.GetNamesp ace("MAPI")
Set objCalFolder = mNameSpace.Fold ers("Public Folders")
Set AllPublicFolder s = objCalFolder.Fo lders("All Public Folders")
Set MyPublicFolder = AllPublicFolder s.Folders("Offi ce")
Set colCalendar = MyPublicFolder. Items

strFind = "[Billinginformat ion] = " &
Forms!frmOEOrde r!txtOrderNumbe r & ""
strShow = "" & Forms!frmOEOrde r!txtOrderNumbe r & ""
Set objAppt = colCalendar.Fin d(strFind)
If objAppt Is Nothing Then

strSQL = "DELETE tblSMSchedule.s trOrderNumber FROM
tblSMSchedule WHERE tblSMSchedule.s trOrderNumber = '" & strShow & "'"
DoCmd.RunSQL strSQL

Else

Set rst = CurrentDb.OpenR ecordset("tblSM Schedule")

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!frmOEOrde r!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.s trNotes = '" & strBody
& "' WHERE tblSMSchedule.c ntID = " & 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

[Dave Hopper]

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message news:<JN******* ********@newssv r16.news.prodig y.com>...

Dave,

Option Compare Database

I recommend you add Option Explicit below this. That will force you to
declare your variables and help catch spelling errors.

Dim myOlApp
Dim mNameSpace

Dim MyItem
Dim strMsg

Dim strPublicFolder
Dim strSubject
Dim strStart
Dim strEnd
Dim strBody
Dim strLocation
Dim strRequiredAtte ndees
Dim strCategories
Dim strBillingInfor mation
Dim strShow
Dim strUniqueID

These are all being declared as Variants since a type hasn't been specified.

strPublicFolder = ("Office")

If Len(strPublicFo lder) > 0 Then

This will always be true. Since you're setting the value yourself to Office
just before the If statement, the length will be 6.

Dim rsAppointmentsR ecords As Recordset

rst was Dim'ed as DAO.Recordset, but not this one.

Else

Set rst = CurrentDb.OpenR ecordset("tblSM Schedule")

The recordset was opened prior to the If statement, so opening it again here
shouldn't do anything.

rst.MoveLast
rst.MoveFirst

Do Until rst.EOF

You are moving prior to seeing if there is anything to move to. Also, I
don't see where you are doing anything that would require you to "fully
populate" the recordset before you begin the looping, so the 2 move
statements could probably be omitted. If a recordset has no records, then
rst.EOF and rst.BOF are both true. The next statement is the Do Until, if
there are no records, then you will be at EOF and the loop won't run, so the
check is accomplished here.

If rst(19) = strShow Then

This is in the ELSE part of the If statement. However the value of strShow
is being set before the ELSE; therefore, strShow = "", since the first part
of the If would not have been executed if you are in the ELSE part.

Set ctl = Forms!frmOEOrde r!ListSchedule
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) Then
val = ctl.Column(0, i)
Exit For
End If
Next i

This will set the value of val and abort when the first selected item in the
listbox is found. I don't see where you are selecting/deselecting items, so
if you go through this again in the Do loop, it should set val to the same
value again.

rst.Close

db.Close

Should be
rst.Close
Set rst = Nothing
Set db = Nothing

Also, you use CurrentDb to set rst, you set db to CurrentDb later then never
use it.
--
Wayne Morgan
Microsoft Access MVP
"Dave Hopper" <da**********@d atelnet.co.uk> wrote in message
news:af******** *************** ***@posting.goo gle.com...

Option Compare Database

Public Function ImportAppointme ntsTest()

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenR ecordset("tblSM Schedule")

Dim Prop As Outlook.UserPro perty

Dim objOL As New Outlook.Applica tion
Dim objNS As Outlook.NameSpa ce
Dim strFind As String
Dim objCalFolder As Outlook.mapiFol der
Dim AllPublicFolder s As Outlook.mapiFol der
Dim MyPublicFolder As Outlook.mapiFol der
Dim colCalendar As Outlook.Items
Dim objAppt As Outlook.Appoint mentItem

Dim db As Database
Dim rsAppointmentsR ecords 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 strRequiredAtte ndees
Dim strCategories
Dim strBillingInfor mation
Dim strShow
Dim strUniqueID

Dim val As Integer, i As Integer
Dim ctl As Control
Const olAppointmentIt em = 1

strPublicFolder = ("Office")

If Len(strPublicFo lder) > 0 Then

Set objOL = CreateObject("O utlook.Applicat ion")
Set mNameSpace = objOL.GetNamesp ace("MAPI")
Set objCalFolder = mNameSpace.Fold ers("Public Folders")
Set AllPublicFolder s = objCalFolder.Fo lders("All Public Folders")
Set MyPublicFolder = AllPublicFolder s.Folders("Offi ce")
Set colCalendar = MyPublicFolder. Items

strFind = "[Billinginformat ion] = " &
Forms!frmOEOrde r!txtOrderNumbe r & ""
strShow = "" & Forms!frmOEOrde r!txtOrderNumbe r & ""
Set objAppt = colCalendar.Fin d(strFind)
If objAppt Is Nothing Then

strSQL = "DELETE tblSMSchedule.s trOrderNumber FROM
tblSMSchedule WHERE tblSMSchedule.s trOrderNumber = '" & strShow & "'"
DoCmd.RunSQL strSQL

Else

Set rst = CurrentDb.OpenR ecordset("tblSM Schedule")

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!frmOEOrde r!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.s trNotes = '" & strBody
& "' WHERE tblSMSchedule.c ntID = " & 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

thx for your response Wayne

I'm new to this, but I understand where you are coming from.

I'm not sure you answered my question though ...I still can't retrieve
a value from a list box and I need to do this to loop through all the
appointments.

How do I get this value ?

Dave

[Pieter Linden]

> I'm not sure you answered my question though ...I still can't retrieve

a value from a list box and I need to do this to loop through all the
appointments.

How do I get this value ?

Dave

with a multiselect listbox, you have to loop through the ItemsSelected collection.

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSele ctListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelect ed
'---PROCESS EACH SELECTED ITEM HERE...
Call MessWithValue ctl.ItemData(va rItem)
Next varItem