Help with query and list box values

[Dave Hopper]

Hi

I posted a question recently regarding problems I am having getting a
value from a list box to use in a query. I got a lot of help, for
which I thank you and it's nearly working! But I need a little more
help on one more point.

This is what i've got. I have code that hunts for updated
appointments in a public folder based on the order that happens to be
open (code fires on an on open event) This works fine and updates my
appointments table with no problem if I run the code manually and
select each appointment item manually in my list box on the order
form.

My problem is, if I run the code from an on open event, it doesn't
loop through the values in my list box, it just returns 0 as a value
as follows:

UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.c ntID = 0

The rest of my code looks like this:

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

Any Ideas' ?

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the Form_Load event instead of Form_Open event to read data from a
ListBox.

When the form opens there aren't any items SELECTED from the ListBox, so
what are you getting from the ListBox? Couldn't you just use a
Recordset with the ListBox's RowSource as the query?

An UPDATE doesn't return anything (except error messages, if any), so
what are you expecting?

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQT9lBoechKq OuFEgEQKdngCfb4 Ehh1ZLze7Jcw2B5 M4gnnBRO2IAoOsb
65i+rdMoNEwkPZe 7FpYDlicK
=mbed
-----END PGP SIGNATURE-----
Dave Hopper wrote:

Hi

I posted a question recently regarding problems I am having getting a
value from a list box to use in a query. I got a lot of help, for
which I thank you and it's nearly working! But I need a little more
help on one more point.

This is what i've got. I have code that hunts for updated
appointments in a public folder based on the order that happens to be
open (code fires on an on open event) This works fine and updates my
appointments table with no problem if I run the code manually and
select each appointment item manually in my list box on the order
form.

My problem is, if I run the code from an on open event, it doesn't
loop through the values in my list box, it just returns 0 as a value
as follows:

UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.c ntID = 0

< SNIP >

[Dave Hopper]

MGFoster <[e-mail] > wrote in message news:<ey******* *********@newsr ead3.news.pas.e arthlink.net>.. .

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the Form_Load event instead of Form_Open event to read data from a
ListBox.

When the form opens there aren't any items SELECTED from the ListBox, so
what are you getting from the ListBox? Couldn't you just use a
Recordset with the ListBox's RowSource as the query?

An UPDATE doesn't return anything (except error messages, if any), so
what are you expecting?

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQT9lBoechKq OuFEgEQKdngCfb4 Ehh1ZLze7Jcw2B5 M4gnnBRO2IAoOsb
65i+rdMoNEwkPZe 7FpYDlicK
=mbed
-----END PGP SIGNATURE-----
Dave Hopper wrote:

Hi

I posted a question recently regarding problems I am having getting a
value from a list box to use in a query. I got a lot of help, for
which I thank you and it's nearly working! But I need a little more
help on one more point.

This is what i've got. I have code that hunts for updated
appointments in a public folder based on the order that happens to be
open (code fires on an on open event) This works fine and updates my
appointments table with no problem if I run the code manually and
select each appointment item manually in my list box on the order
form.

My problem is, if I run the code from an on open event, it doesn't
loop through the values in my list box, it just returns 0 as a value
as follows:

UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.c ntID = 0

< SNIP >

Hi, Thanks for the response

The code doesn't work under a button on the form either. However if
you select an item and click the button it functions perfectly for
that entry.

My list box contains appointments that are assigned to the order on
the form. There could be up to ten appointments for an order, so when
the form opens, or when you move to the next record, I need the code
to run and pull in data from the public folder calendar to update the
backend appointments table and refresh the form to show the new data.

As I said, the code seems to find the correct appointments but it
can't find the list box source to complete the update query unless you
highlight an entry.

Any ideas' ?

Dave Hopper

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the Form's OnCurrent event instead of OnOpen or OnLoad
events. The OnCurrent event fires every time a record is accessed. In
that event you could run the code that does whatever you want.

Since the code only works when an item in the ListBox is selected, this
means that the code is probably using the ListBox's ItemsSelected
property. If you want to use ALL the items in a ListBox you would have
to loop thru the items, both selected & unselected. Do you want all the
items?

Here's one way to get all items in a ListBox:

' Get the items in a specific column (1) of the indicated ListBox:
Dim lst As ListBox
Set lst = Me!lstAppts ' Set ref to the ListBox
Dim row As Integer
For row = 0 To lst.ListCount - 1
Debug.Print lst.Column(1, row) ' Get the column we want
Next row

You can modify to your needs.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUDvboechKq OuFEgEQJ5gwCg/08jYcegBJmtHl/9ce+dVTrdVf4AoK Lf
15Fjq6bbj256ZIx qXWwV1gLP
=4N78
-----END PGP SIGNATURE-----
Dave Hopper wrote:

MGFoster <[e-mail] > wrote in message news:<ey******* *********@newsr ead3.news.pas.e arthlink.net>.. .

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the Form_Load event instead of Form_Open event to read data from a
ListBox.

When the form opens there aren't any items SELECTED from the ListBox, so
what are you getting from the ListBox? Couldn't you just use a
Recordset with the ListBox's RowSource as the query?

An UPDATE doesn't return anything (except error messages, if any), so
what are you expecting?

--
MGFoster:::mg f00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQT9lBoechKq OuFEgEQKdngCfb4 Ehh1ZLze7Jcw2B5 M4gnnBRO2IAoOsb
65i+rdMoNEwkP Ze7FpYDlicK
=mbed
-----END PGP SIGNATURE-----
Dave Hopper wrote:

Hi

I posted a question recently regarding problems I am having getting a
value from a list box to use in a query. I got a lot of help, for
which I thank you and it's nearly working! But I need a little more
help on one more point.

This is what i've got. I have code that hunts for updated
appointmen ts in a public folder based on the order that happens to be
open (code fires on an on open event) This works fine and updates my
appointmen ts table with no problem if I run the code manually and
select each appointment item manually in my list box on the order
form.

My problem is, if I run the code from an on open event, it doesn't
loop through the values in my list box, it just returns 0 as a value
as follows:

UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.c ntID = 0

< SNIP >

Hi, Thanks for the response

The code doesn't work under a button on the form either. However if
you select an item and click the button it functions perfectly for
that entry.

My list box contains appointments that are assigned to the order on
the form. There could be up to ten appointments for an order, so when
the form opens, or when you move to the next record, I need the code
to run and pull in data from the public folder calendar to update the
backend appointments table and refresh the form to show the new data.

As I said, the code seems to find the correct appointments but it
can't find the list box source to complete the update query unless you
highlight an entry.

Any ideas' ?

Dave Hopper

[Dave Hopper]

MGFoster <[e-mail] > wrote in message news:<Mb******* **********@news read3.news.pas. earthlink.net>. ..

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the Form's OnCurrent event instead of OnOpen or OnLoad
events. The OnCurrent event fires every time a record is accessed. In
that event you could run the code that does whatever you want.

Since the code only works when an item in the ListBox is selected, this
means that the code is probably using the ListBox's ItemsSelected
property. If you want to use ALL the items in a ListBox you would have
to loop thru the items, both selected & unselected. Do you want all the
items?

Here's one way to get all items in a ListBox:

' Get the items in a specific column (1) of the indicated ListBox:
Dim lst As ListBox
Set lst = Me!lstAppts ' Set ref to the ListBox
Dim row As Integer
For row = 0 To lst.ListCount - 1
Debug.Print lst.Column(1, row) ' Get the column we want
Next row

You can modify to your needs.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUDvboechKq OuFEgEQJ5gwCg/08jYcegBJmtHl/9ce+dVTrdVf4AoK Lf
15Fjq6bbj256ZIx qXWwV1gLP
=4N78
-----END PGP SIGNATURE-----
Dave Hopper wrote:

MGFoster <[e-mail] > wrote in message news:<ey******* *********@newsr ead3.news.pas.e arthlink.net>.. .

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the Form_Load event instead of Form_Open event to read data from a
ListBox.

When the form opens there aren't any items SELECTED from the ListBox, so
what are you getting from the ListBox? Couldn't you just use a
Recordset with the ListBox's RowSource as the query?

An UPDATE doesn't return anything (except error messages, if any), so
what are you expecting?

--
MGFoster:::mg f00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQT9lBoechKq OuFEgEQKdngCfb4 Ehh1ZLze7Jcw2B5 M4gnnBRO2IAoOsb
65i+rdMoNEwkP Ze7FpYDlicK
=mbed
-----END PGP SIGNATURE-----
Dave Hopper wrote:
Hi

I posted a question recently regarding problems I am having getting a
value from a list box to use in a query. I got a lot of help, for
which I thank you and it's nearly working! But I need a little more
help on one more point.

This is what i've got. I have code that hunts for updated
appointmen ts in a public folder based on the order that happens to be
open (code fires on an on open event) This works fine and updates my
appointmen ts table with no problem if I run the code manually and
select each appointment item manually in my list box on the order
form.

My problem is, if I run the code from an on open event, it doesn't
loop through the values in my list box, it just returns 0 as a value
as follows:

UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.c ntID = 0
< SNIP >

Hi, Thanks for the response

The code doesn't work under a button on the form either. However if
you select an item and click the button it functions perfectly for
that entry.

My list box contains appointments that are assigned to the order on
the form. There could be up to ten appointments for an order, so when
the form opens, or when you move to the next record, I need the code
to run and pull in data from the public folder calendar to update the
backend appointments table and refresh the form to show the new data.

As I said, the code seems to find the correct appointments but it
can't find the list box source to complete the update query unless you
highlight an entry.

Any ideas' ?

Dave Hopper

Hi

I have modified the code and can get it to work on it's own, but I
can't seem to get it to work with my sql pasted elsewhere in this
thread. When your code runs on it's own, it produces the desired
result and pulls out all the values correctly. I need to be able to
insert those values into my query by setting a variable. For example
i = lst.Column(1, row). I can then use the value i in my sql as
follows:

str = "UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = '" & strBody
& "' WHERE tblSMSchedule.c ntID = " & i
DoCmd.RunSQL (str)

However, when I enter this, I get an error telling me that it is
expecting a sub ?

I'm sorry to be a pain, but could you show me exactly where I need to
insert this code to get the desired result. I'm normally pretty good
at modifying code for my own needs, but I'm just drawing a mental
blank with this

Many thanks in anticipation for your help.

Dave

[MGFoster]

Dave Hopper wrote:

MGFoster <[e-mail] > wrote in message news:<Mb******* **********@news read3.news.pas. earthlink.net>. ..

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the Form's OnCurrent event instead of OnOpen or OnLoad
events. The OnCurrent event fires every time a record is accessed. In
that event you could run the code that does whatever you want.

Since the code only works when an item in the ListBox is selected, this
means that the code is probably using the ListBox's ItemsSelected
property. If you want to use ALL the items in a ListBox you would have
to loop thru the items, both selected & unselected. Do you want all the
items?

Here's one way to get all items in a ListBox:

' Get the items in a specific column (1) of the indicated ListBox:
Dim lst As ListBox
Set lst = Me!lstAppts ' Set ref to the ListBox
Dim row As Integer
For row = 0 To lst.ListCount - 1
Debug.Print lst.Column(1, row) ' Get the column we want
Next row

You can modify to your needs.
--
MGFoster:::mg f00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUDvboechKq OuFEgEQJ5gwCg/08jYcegBJmtHl/9ce+dVTrdVf4AoK Lf
15Fjq6bbj256Z IxqXWwV1gLP
=4N78
-----END PGP SIGNATURE-----
Dave Hopper wrote:

MGFoster <[e-mail] > wrote in message news:<ey******* *********@newsr ead3.news.pas.e arthlink.net>.. .
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the Form_Load event instead of Form_Open event to read data from a
ListBox.

When the form opens there aren't any items SELECTED from the ListBox, so
what are you getting from the ListBox? Couldn't you just use a
Recordset with the ListBox's RowSource as the query?

An UPDATE doesn't return anything (except error messages, if any), so
what are you expecting?

--
MGFoster::: mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQT9lBoechKq OuFEgEQKdngCfb4 Ehh1ZLze7Jcw2B5 M4gnnBRO2IAoOsb
65i+rdMoNEw kPZe7FpYDlicK
=mbed
-----END PGP SIGNATURE-----
Dave Hopper wrote:

>Hi
>
>I posted a question recently regarding problems I am having getting a
>value from a list box to use in a query. I got a lot of help, for
>which I thank you and it's nearly working! But I need a little more
>help on one more point.
>
>This is what i've got. I have code that hunts for updated
>appointmen ts in a public folder based on the order that happens to be
>open (code fires on an on open event) This works fine and updates my
>appointmen ts table with no problem if I run the code manually and
>select each appointment item manually in my list box on the order
>form.
>
>My problem is, if I run the code from an on open event, it doesn't
>loop through the values in my list box, it just returns 0 as a value
>as follows:
>
>UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = 'Wrthwrthtes mmm'
>WHERE tblSMSchedule.c ntID = 0
>

< SNIP >
Hi, Thanks for the response

The code doesn't work under a button on the form either. However if
you select an item and click the button it functions perfectly for
that entry.

My list box contains appointments that are assigned to the order on
the form. There could be up to ten appointments for an order, so when
the form opens, or when you move to the next record, I need the code
to run and pull in data from the public folder calendar to update the
backend appointments table and refresh the form to show the new data.

As I said, the code seems to find the correct appointments but it
can't find the list box source to complete the update query unless you
highlight an entry.

Any ideas' ?

Dave Hopper

Hi

I have modified the code and can get it to work on it's own, but I
can't seem to get it to work with my sql pasted elsewhere in this
thread. When your code runs on it's own, it produces the desired
result and pulls out all the values correctly. I need to be able to
insert those values into my query by setting a variable. For example
i = lst.Column(1, row). I can then use the value i in my sql as
follows:

str = "UPDATE tblSMSchedule SET tblSMSchedule.s trNotes = '" & strBody
& "' WHERE tblSMSchedule.c ntID = " & i
DoCmd.RunSQL (str)

However, when I enter this, I get an error telling me that it is
expecting a sub ?

I'm sorry to be a pain, but could you show me exactly where I need to
insert this code to get the desired result. I'm normally pretty good
at modifying code for my own needs, but I'm just drawing a mental
blank with this

Many thanks in anticipation for your help.

Dave

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know what that error report is. A "sub"! What's a sub?

Is the SQL string correct. Is the cntID a string or a number? Make
sure it has the proper delimiters if it is a string.

I use "CurrentDB.Exec ute str, dbFailOnError" instead of RunSQL. The
Execute w/ dbFailOnError gives a more exact error report.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUIZLYechKq OuFEgEQKHdwCeJR 8RrK8bNGpHjN4RC FFxHLJ6dt0AnRN2
K+2xL1KTmvZJKEZ QXKf3ahBX
=3Ce7
-----END PGP SIGNATURE-----

[Pieter Linden]

try this instead

DBEngine(0)(0). Execute strSQL, dbFailOnError

[Pieter Linden]

try this instead

DBEngine(0)(0). Execute strSQL, dbFailOnError