Listbox problem

S

SF

Hi

I have a listbox (Me.listSectorID) on form for dataentry purpose. USer can
select one or more on the list and click Save button. I cannot make the
bottun work. Could someone advice!!


dim dbs as dao.database
dim rstSector as dao.database
dim h as interger

set dbs = currentdb

Set rstSector = dbs.OpenRecordset("tmpContactFieldOfWork", dbOpenDynaset)
For H = 0 To Me.listSectorID.ItemsSelected(H) - 1
rstSector.AddNew
Debug.Print Me.listSectorID.ItemData(H) & " - " &
Me.listSectorID.ItemsSelected(H) & " - "
rstSector![So_SectorID] = Me.listSectorID.ItemData(H)
rstSector.Update
rstSector.MoveNext
Next H
Set rstSector = Nothing
DoCmd.OpenTable "tmpContactFieldOfWork", acViewNormal
 
T

TC

Well, I notice that your code has a debug.print statement. That
statement prints two values for each item selected in the list box:

(1) ... Me.listSectorID.ItemData(H)

(2) ... Me.listSectorID.ItemsSelected(H)

Then you add value (1), not value (2), to the table.

So: are the two values printing correctly? You have to tell us that!

Also, be aware that the following statement is not performing any
useful purpose:

rstSector.MoveNext

First, the Update method affects the current record position within the
recordset. So you can't just do a MoveNext immediately after doing an
Update. There's a way to fix that, but, you do not need to do a
MoveNext anyway! You only need to do MoveNext, when your are reading
through existing records from a recordset. You aren't dong that, you
are adding new records to the recordset; you do not need MoveNext for
that.

HTH,
TC (MVP Access)
ttp://tc2.atspace.com
 
G

Guest

Hi SF,

You have declared rstSector incorrectly:
Dim rstSector As DAO.database

It should be:
Dim rstSector As DAO.Recordset (not DAO.database)

You've also misspelled a variable type:
dim h as interger

This should be:
dim h as integer

Do you have those two very important words "Option Explicit" included as the
second line of code in your module? Please see this page for more
information:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Try the following variation of your procedure:

'***********Begin Code***************
Private Function Test()
On Error GoTo ProcError

Dim dbs As DAO.database
Dim rstSector As DAO.Recordset
Dim varCategory As Variant

Set dbs = CurrentDb
Set rstSector = dbs.OpenRecordset("tmpContactFieldOfWork", dbOpenDynaset)

For Each varCategory In Me!listSectorID.ItemsSelected()
rstSector.AddNew
'Debug.Print Me.listSectorID.ItemData(varCategory)
rstSector![So_SectorID] = Me.listSectorID.ItemData(varCategory)
rstSector.Update
Next varCategory


DoCmd.OpenTable "tmpContactFieldOfWork", acViewNormal


ExitProc:
'Cleanup
On Error Resume Next
rstSector.Close: Set rstSector = Nothing
dbs.Close: Set dbs = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Test..."
Resume ExitProc
End Function

'**********End Code*******************



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 

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

Ken Snell's Export to multiple Spreadsheets 9
Invalid Use of Null 3
Runtime Error: 3146 4
help with Code 1
Make table from recordset 2
Querydef Timeout 9
Help with code 5
Excel automation 2

Top