Updating Listbox

  • Thread starter Thread starter JamesJ
  • Start date Start date
J

JamesJ

I'm trying to delete a record from a form that has a Listbox populated by
the same tbl as the form. In the OnClick of a delete button I have the
following code:

Public Function DelRecord()

Set frm = Screen.ActiveForm


Dim msg, style, TITLE, Response

msg = "You are about to delete 1 record," & vbCrLf & "Are you sure you
want to do this?"
style = vbYesNo + vbExclamation + vbDefaultButton2
TITLE = "Delete Record"

Response = MsgBox(msg, style, TITLE)

If Response = vbYes Then

frm.RecordsetClone.Bookmark = frm.Bookmark
frm.RecordsetClone.Delete
frm.Requery
lbox.Requery

Else

End If

End Function

I declare frm and lbox in the Declaration section of the module
I get an error message at the lbox.Requery line:

Object variable or With block variable not set.

Any help will be appreciated

James
 
You may have declared lbox as a Control elsewhere in the module, but you
haven't given it a value:

Set lbox = Screen.ActiveControl

Incidentally, you'd be far better off typing your variables:

Dim msg As String, style As Long, TITLE As String, Response As Long

Your declaration makes your four variables Variants
 
Got it now. But, the listbox still shows the record as #Deleted even though
I
inserted lbox.Requery after the deletion in the code. Any way to update
the listbox?

James
 
You didn't remove the

Set lbox = Screen.ActiveControl

statement, did you?

lbox.RowSource = lbox.RowSource was intended to be used instead of
lbox.Requery.
 
Actually after checking it was removed or wasn't saved for some reason.
I added it and now it saying:
'Object doesn't support this property or method'
at lbox.RowSource = lbox.RowSource

and I removed lbox.Requery
 
Okay, you might have to do it in two steps.

Dim strRowSource As String

strRowSource = lbox.RowSource
lbox.RowSource = vbNullString
lbox.RowSource = strRowSource
 
Can't get it to work.
This form with the listbox is a viewing form. I can put the delete record
button
on my editing form then I won't have to contend with a listbox.
Just an extra step to delete a record.

Thanks,
James
 
Back
Top