List box refresh

[grmman]

I have a form with 2 list box on them, when I click on a name in list box 1 I write that infor to a tmp file and I want the name to show up in the 2 list box.

I set the rowsource of the 2 list box to be the tmp file.
The info is being written to the tmp file but it will not show up in the 2 list box.
here is my code.
THANKS

Private Sub cmdswth_Click()

On Error GoTo Form_AfterUpdate_Err
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection
'lstdest.Visible = False
With CurConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "data source= " & CurDB.Name
    .Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "select * from tmp_usr", CurConn

With rst
    .AddNew
    ![app_id] = Me.cboappname.Value
    ![empl_id] = Me.lstempall.Column(0)
    ![fname] = Me.lstempall.Column(1)
    ![lname] = Me.lstempall.Column(2)
    ![status] = 1
    .Update
End With

bytcolcnt = 8
strcolwth = "0;" & "0;" & "0;" & "0;" & "0;" & "0;" & "1;" & "1"
strSrcTyp = "Table/Query"
'lstdest.Visible = True
strlstdest = "Select * from tmp_usr"
 With lstdest
        .ColumnCount = bytcolcnt
        .ColumnWidths = strcolwth
        .RowSource = strlstdest
        .RowSourceType = strSrcTyp
    End With
        
Call lstdest.Requery


rst.Close

Form_AfterUpdate_Exit:
    Exit Sub
Form_AfterUpdate_Err:
    MsgBox Err.Description
    Resume Form_AfterUpdate_Exit

End Sub

 

[KenReay]

try a simply

lstdest.Requery

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SiJP]

From your code you only add in five fields to a record, yet in your lstdest you are trying to add in 8 columns.. I'd advise dumping "Select * from tmp_usr" into a new query, and seeing what is returned.

Also, out of good housekeeping, I generally put the .RowSourceType before the .RowSource

Let us know how you get on..

------------------------
Hit any User to continue

 

[grmman]

I have lstdest.requery already in there and that does not work.
I changed the column count to 2 and that does nothing.
Anymore suggestion please.
THANKS

 

[SiJP]

1) CurDB, bytcolcnt, strcolwth, strlstdest, strSrcTyp are not declared in this module..?


2) change the strcolwth to strcolwth = "0;0;0;0;0;0;1;1"

Give that a go...

------------------------
Hit any User to continue

 

[SiJP]

btw, if you have a column count of two, then your strcolwth should be along the lines of "0;1" or whatever...

------------------------
Hit any User to continue

 

[PHV]

Have you tried to close the recordset BEFORE dealing with the 2nd list box ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grmman]

Thanks All I got it to work.

 

[PHV]

grmman, thanks for sharing.
Can you please explain the members how you solved your issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grmman]

I took out
bytcolcnt = 8
strcolwth = "0;" & "0;" & "0;" & "0;" & "0;" & "0;" & "1;" & "1"
and it worked.