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.strNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.cntID = 0

The rest of my code looks like this:

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

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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQT9lBoechKqOuFEgEQKdngCfb4Ehh1ZLze7Jcw2B5M4gnn BRO2IAoOsb
65i+rdMoNEwkPZe7FpYDlicK
=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.strNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.cntID = 0

< SNIP >

[Dave Hopper]

MGFoster <[e-mail]> wrote in message news:<ey****************@newsread3.news.pas.earthl ink.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/AwUBQT9lBoechKqOuFEgEQKdngCfb4Ehh1ZLze7Jcw2B5M4gnn BRO2IAoOsb
65i+rdMoNEwkPZe7FpYDlicK
=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.strNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.cntID = 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQUDvboechKqOuFEgEQJ5gwCg/08jYcegBJmtHl/9ce+dVTrdVf4AoKLf
15Fjq6bbj256ZIxqXWwV1gLP
=4N78
-----END PGP SIGNATURE-----
Dave Hopper wrote:

MGFoster <[e-mail]> wrote in message news:<ey****************@newsread3.news.pas.earthl ink.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/AwUBQT9lBoechKqOuFEgEQKdngCfb4Ehh1ZLze7Jcw2B5M4gnn BRO2IAoOsb
65i+rdMoNEwkPZe7FpYDlicK
=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.strNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.cntID = 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*****************@newsread3.news.pas.earth link.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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQUDvboechKqOuFEgEQJ5gwCg/08jYcegBJmtHl/9ce+dVTrdVf4AoKLf
15Fjq6bbj256ZIxqXWwV1gLP
=4N78
-----END PGP SIGNATURE-----
Dave Hopper wrote:

MGFoster <[e-mail]> wrote in message news:<ey****************@newsread3.news.pas.earthl ink.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/AwUBQT9lBoechKqOuFEgEQKdngCfb4Ehh1ZLze7Jcw2B5M4gnn BRO2IAoOsb
65i+rdMoNEwkPZe7FpYDlicK
=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.strNotes = 'Wrthwrthtes mmm'
WHERE tblSMSchedule.cntID = 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.strNotes = '" & strBody
& "' WHERE tblSMSchedule.cntID = " & 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*****************@newsread3.news.pas.earth link.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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQUDvboechKqOuFEgEQJ5gwCg/08jYcegBJmtHl/9ce+dVTrdVf4AoKLf
15Fjq6bbj256ZIxqXWwV1gLP
=4N78
-----END PGP SIGNATURE-----
Dave Hopper wrote:

MGFoster <[e-mail]> wrote in message news:<ey****************@newsread3.news.pas.earthl ink.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/AwUBQT9lBoechKqOuFEgEQKdngCfb4Ehh1ZLze7Jcw2B5M4gnn BRO2IAoOsb
65i+rdMoNEwkPZe7FpYDlicK
=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.strNotes = 'Wrthwrthtes mmm'
>WHERE tblSMSchedule.cntID = 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.strNotes = '" & strBody
& "' WHERE tblSMSchedule.cntID = " & 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.Execute str, dbFailOnError" instead of RunSQL. The
Execute w/ dbFailOnError gives a more exact error report.

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

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

iQA/AwUBQUIZLYechKqOuFEgEQKHdwCeJR8RrK8bNGpHjN4RCFFxHL J6dt0AnRN2
K+2xL1KTmvZJKEZQXKf3ahBX
=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