By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

retrieving a value from a list box

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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!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)

HTH Linda

"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

Nov 13 '05 #2

P: n/a
> 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

Nov 13 '05 #3

P: n/a
"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
Nov 13 '05 #4

P: n/a
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 strRequiredAttendees
Dim strCategories
Dim strBillingInformation
Dim strShow
Dim strUniqueID These are all being declared as Variants since a type hasn't been specified.
strPublicFolder = ("Office")

If Len(strPublicFolder) > 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 rsAppointmentsRecords As Recordset rst was Dim'ed as DAO.Recordset, but not this one.
Else

Set rst = CurrentDb.OpenRecordset("tblSMSchedule") 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!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 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**********@datelnet.co.uk> wrote in message
news:af**************************@posting.google.c om...
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

Nov 13 '05 #5

P: n/a
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<JN***************@newssvr16.news.prodigy.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 strRequiredAttendees
Dim strCategories
Dim strBillingInformation
Dim strShow
Dim strUniqueID

These are all being declared as Variants since a type hasn't been specified.
strPublicFolder = ("Office")

If Len(strPublicFolder) > 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 rsAppointmentsRecords As Recordset

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

Set rst = CurrentDb.OpenRecordset("tblSMSchedule")

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!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

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**********@datelnet.co.uk> wrote in message
news:af**************************@posting.google.c om...

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


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
Nov 13 '05 #6

P: n/a
> 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!lbMultiSelectListbox
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.ItemsSelected
'---PROCESS EACH SELECTED ITEM HERE...
Call MessWithValue ctl.ItemData(varItem)
Next varItem
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.