requery method in MSaccess 2K doesn't update list box after record

Z

Zadi Friedrich

Hi Everyone,
I have a list box on a form name 'frmPersonelList' whom rowsource property
is dynamically assigned a query string. On this form there is a delete button
that basically calls a vb routine to delete a record from the table. The
routine works just fine. My problem is even after the record is deleted in
the table and I use the instruction to requery the list box, the deleted item
from the table still remains on the list box. I used the refresh method of
the Form object and it doesn't work either. Could you please provide some
help.
 
S

Stefan Hoffmann

hi,

Zadi said:
I have a list box on a form name 'frmPersonelList' whom rowsource property
is dynamically assigned a query string. On this form there is a delete button
that basically calls a vb routine to delete a record from the table.
Click to expand...
If you use DoCmd to delete the record, try a DoCmd.DoEvents or delete
your record with CurrentDb.Execute "DELETE FROM table WHERE id = ..."


mfG
--> stefan <--
 
M

Mark A. Sam

Hello Zadi,

That should be strange, but this is the world of Microsoft. ;)

What is tbe actual method you are using to requery the list box, or better
yet what is the procedure for deleting the record? Are you in a project?
You might want to try reassigning the Rowsource rather than requerying.

God Bless,

Mark A. Sam
 
Z

Zadi Friedrich

Dear Mark,
The answers to your question follow:
Actual method to requery listbox: Forms!frmFormName!lstList
Procedure used to delete the record:
sub DeleteRecord()
dim cnn as new adodb.connection
dim strSQL as string
strSQL="Delete * From tblTableName Where ID =1"
cnn.execute strSQL
Forms!frmName!lstList.RowSource="Select field1,field2 From tblTableName"
Forms!frmName!lstList.Requery
End Sub
The previous procedure is executed when the delete button on form
'frmFormName' is clicked. I hope that will help
 
M

Mark A. Sam

Zadi,

Try this:

sub DeleteRecord()
dim cnn as new adodb.connection
dim strSQL as string
strSQL="Delete * From tblTableName Where ID =1"
cnn.execute strSQL
lstList.RowSource="Select field1,field2 From tblTableName"
lstList.Requery
End Sub

I'm puzzled by this also. ReSetting the Rowsource should automatically
requery the listbox.

Let me know if that helps. Also are you using a project?

God Bless,

Mark
 
M

Mark A. Sam

Hello Zadi,

I noticed something in your SQL Statement:

lstList.RowSource="Select field1,field2 From tblTableName;"

You left out the semicolon at the end of the statement.
 
Z

Zadi Friedrich

And also, yes I am working on a project.

Mark A. Sam said:
Hello Zadi,

I noticed something in your SQL Statement:

lstList.RowSource="Select field1,field2 From tblTableName;"

You left out the semicolon at the end of the statement.
Click to expand...
 
Z

Zadi Friedrich

Hi Sam,
Putting the semicolon at the end of the query string doesn't solve the
problem. Sorry!
 
M

Mark A. Sam

Here is another idea:

sub DeleteRecord()
dim cnn as new adodb.connection
dim strSQL as string
strSQL="Delete * From tblTableName Where ID =1"
cnn.execute strSQL

Docmd.Runcommand acCmdSaveRecord

lstList.RowSource="Select field1,field2 From tblTableName"
lstList.Requery
End Sub


Save the record before Resetting the listbox Rowsource. It may be that the
change to the table wasn't registered. If that fails, you couild always
Requery the form, which would take you to the first record, then Find the
record you were on.
 
Top