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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 <--
 
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
 
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
 
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
 
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.
 
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...
 
Hi Sam,
Putting the semicolon at the end of the query string doesn't solve the
problem. Sorry!
 
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.
 
Back
Top