Loop thru listbox code...

M

Matt K.

Why does this code only work on the last item selected in the listbox? I have
a feeling it has to do with opening and closing the recordset????

I am using this on a form that allows the user to update a field (that they
chose from a combobox) for multiple records (they select in the listbox).

For Each itm In Me.lstSurveysForUpdate.ItemsSelected
Set db = CurrentDb
Set rstDestTable = db.OpenRecordset("SELECT " &
Me.cboFieldToUpdate.Column(4) & ".SURVEYid FROM " &
Me.cboFieldToUpdate.Column(4) & _
" WHERE " & Me.cboFieldToUpdate.Column(4) & ".SURVEYid=" &
Me.lstSurveysForUpdate.Column(0), dbOpenDynaset)
If rstDestTable.RecordCount = 0 Then
strAddRecord = "INSERT INTO " & Me.cboFieldToUpdate.Column(4) & "
(SURVEYid) VALUES (" & Me.lstSurveysForUpdate.Column(0) & ")"
db.Execute strAddRecord, dbFailOnError
rstDestTable.Close
Set db = Nothing
Set rstDestTable = Nothing
Else
End If
Next itm

This looks in the "child" table of a one to one relationship (PK=SURVEYid)
to see if there is a related record to even update. If not it INSERTs one to
be updated... the updating code is not pasted here.

I hope this makes sense to someone.
 
M

Matt K.

I got it to work changing
Me.lstSurveysForUpdate.Column(0) to Me.lstSurveysForUpdate.ItemData(itm)

I am still developing this database but I have seen posts that indicate
after splitting a database, the "db.Execute" Method does not work. Would it
be better to use the "DoCmd.RunSQL" and suppress warnings???
Or am I out in left field? I've been accused of such before!

Anyways here is what the code that is now working looks like...

For Each itm In Me.lstSurveysForUpdate.ItemsSelected
Set db = CurrentDb
Set rstDestTable = db.OpenRecordset("SELECT " &
Me.cboFieldToUpdate.Column(4) & ".SURVEYid FROM " &
Me.cboFieldToUpdate.Column(4) & _
" WHERE " & Me.cboFieldToUpdate.Column(4) & ".SURVEYid=" &
Me.lstSurveysForUpdate.ItemData(itm), dbOpenDynaset)
If rstDestTable.RecordCount = 0 Then
strAddRecord = "INSERT INTO " & Me.cboFieldToUpdate.Column(4) & "
(SURVEYid) VALUES (" & Me.lstSurveysForUpdate.ItemData(itm) & ")"
db.Execute strAddRecord, dbFailOnError
rstDestTable.Close
db.Close
Set db = Nothing
Set rstDestTable = Nothing
Else
End If
Next itm
 
G

Graham Mandeno

Hi Matt

Just to clarify:

..ItemData(itm) will give you the value from the *bound column* of each row.

..Column(n, itm) will give you the value from the nth column (starting from
0), which is not necessarily the bound column.

Note also than the BoundColumn starts from 1, so if BoundColumn is 1 it is
the equivalent of .Column(0)
 
M

Matt K.

Graham,
Thanks for the explanation. That little problem drove me nuts!
Any comment on this part of my previous post...


Graham Mandeno said:
Hi Matt

Just to clarify:

..ItemData(itm) will give you the value from the *bound column* of each row.

..Column(n, itm) will give you the value from the nth column (starting from
0), which is not necessarily the bound column.

Note also than the BoundColumn starts from 1, so if BoundColumn is 1 it is
the equivalent of .Column(0)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Matt K. said:
I got it to work changing
Me.lstSurveysForUpdate.Column(0) to Me.lstSurveysForUpdate.ItemData(itm)

I am still developing this database but I have seen posts that indicate
after splitting a database, the "db.Execute" Method does not work. Would
it
be better to use the "DoCmd.RunSQL" and suppress warnings???
Or am I out in left field? I've been accused of such before!

Anyways here is what the code that is now working looks like...

For Each itm In Me.lstSurveysForUpdate.ItemsSelected
Set db = CurrentDb
Set rstDestTable = db.OpenRecordset("SELECT " &
Me.cboFieldToUpdate.Column(4) & ".SURVEYid FROM " &
Me.cboFieldToUpdate.Column(4) & _
" WHERE " & Me.cboFieldToUpdate.Column(4) & ".SURVEYid=" &
Me.lstSurveysForUpdate.ItemData(itm), dbOpenDynaset)
If rstDestTable.RecordCount = 0 Then
strAddRecord = "INSERT INTO " & Me.cboFieldToUpdate.Column(4) & "
(SURVEYid) VALUES (" & Me.lstSurveysForUpdate.ItemData(itm) & ")"
db.Execute strAddRecord, dbFailOnError
rstDestTable.Close
db.Close
Set db = Nothing
Set rstDestTable = Nothing
Else
End If
Next itm
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Matt

Yes - sorry - I meant to answer that question also.

I have never heard of this "problem". I *always* use db.Execute.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Matt K. said:
Graham,
Thanks for the explanation. That little problem drove me nuts!
Any comment on this part of my previous post...


Graham Mandeno said:
Hi Matt

Just to clarify:

..ItemData(itm) will give you the value from the *bound column* of each
row.

..Column(n, itm) will give you the value from the nth column (starting
from
0), which is not necessarily the bound column.

Note also than the BoundColumn starts from 1, so if BoundColumn is 1 it
is
the equivalent of .Column(0)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Matt K. said:
I got it to work changing
Me.lstSurveysForUpdate.Column(0) to
Me.lstSurveysForUpdate.ItemData(itm)

I am still developing this database but I have seen posts that indicate
after splitting a database, the "db.Execute" Method does not work.
Would
it
be better to use the "DoCmd.RunSQL" and suppress warnings???
Or am I out in left field? I've been accused of such before!

Anyways here is what the code that is now working looks like...

For Each itm In Me.lstSurveysForUpdate.ItemsSelected
Set db = CurrentDb
Set rstDestTable = db.OpenRecordset("SELECT " &
Me.cboFieldToUpdate.Column(4) & ".SURVEYid FROM " &
Me.cboFieldToUpdate.Column(4) & _
" WHERE " & Me.cboFieldToUpdate.Column(4) & ".SURVEYid=" &
Me.lstSurveysForUpdate.ItemData(itm), dbOpenDynaset)
If rstDestTable.RecordCount = 0 Then
strAddRecord = "INSERT INTO " & Me.cboFieldToUpdate.Column(4) &
"
(SURVEYid) VALUES (" & Me.lstSurveysForUpdate.ItemData(itm) & ")"
db.Execute strAddRecord, dbFailOnError
rstDestTable.Close
db.Close
Set db = Nothing
Set rstDestTable = Nothing
Else
End If
Next itm




:

Why does this code only work on the last item selected in the listbox?
I
have
a feeling it has to do with opening and closing the recordset????

I am using this on a form that allows the user to update a field (that
they
chose from a combobox) for multiple records (they select in the
listbox).

For Each itm In Me.lstSurveysForUpdate.ItemsSelected
Set db = CurrentDb
Set rstDestTable = db.OpenRecordset("SELECT " &
Me.cboFieldToUpdate.Column(4) & ".SURVEYid FROM " &
Me.cboFieldToUpdate.Column(4) & _
" WHERE " & Me.cboFieldToUpdate.Column(4) & ".SURVEYid=" &
Me.lstSurveysForUpdate.Column(0), dbOpenDynaset)
If rstDestTable.RecordCount = 0 Then
strAddRecord = "INSERT INTO " & Me.cboFieldToUpdate.Column(4)
& "
(SURVEYid) VALUES (" & Me.lstSurveysForUpdate.Column(0) & ")"
db.Execute strAddRecord, dbFailOnError
rstDestTable.Close
Set db = Nothing
Set rstDestTable = Nothing
Else
End If
Next itm

This looks in the "child" table of a one to one relationship
(PK=SURVEYid)
to see if there is a related record to even update. If not it INSERTs
one
to
be updated... the updating code is not pasted here.

I hope this makes sense to someone.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

needing help with loop issues 1
populate listbox form database table 0
Select record in a listbox 8
Code Help 2
transfer listbox data 4
no duplicate record 6
Listbox execute SQL error 2
Refining multi select list box code 4

Top