Updating Listbox

[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

 

[Douglas J. Steele]

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

 

[JamesJ]

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

 

[Douglas J. Steele]

See whether

lbox.RowSource = lbox.RowSource

makes a difference.

 

[JamesJ]

Tells me 'Object required'.

See whether

lbox.RowSource = lbox.RowSource

makes a difference.

 

[Douglas J. Steele]

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.

 

[JamesJ]

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

 

[Douglas J. Steele]

Okay, you might have to do it in two steps.

Dim strRowSource As String

strRowSource = lbox.RowSource
lbox.RowSource = vbNullString
lbox.RowSource = strRowSource

 

[JamesJ]

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