How to Remove Item from Listbox When Checkbox is Unclicked

[RedWingsFan44]

I'm using Access 2010 and have a form that is basically the last step in generating a report (which will be a printable invoice).

I am facing major problems when it comes to removing line items that the user may have accidentally clicked. I have a checkbox field embedded in the table (tblLineItems) which is set up as binary. I have created a form from tblLineItems, and have in turn embedded that as a subform on the invoice generating form.

When I select line items, I have them populating a two column listbox that shows the user what they have selected before actually opening the report. The code I am using is on the checkbox click event in the subform:

Private Sub InvoiceY_N Click()
Dim InvLineTotal As Currency

If InvoiceY_N.Value = True Then
InvLineTotal = ([PO Line Item Total])

Form_frmInvoiceNew.ListLineitemsold.AddItem ([PO_Line__] & ";" & (InvLineTotal))
Me.Refresh
End If

______________________

If the user unclicks one of these line items I cannot figure out how to remove this item from the listbox!

I have tried the removeitem method but it is not working!

 'If InvoiceY_N.Value = False Then
'InvLineTotal = ([PO Line Item Total])
'Form_frmInvoiceNew.ListLineitemsold.RemoveItem ([PO_Line__] & ";" & (InvLineTotal))
'Me.Refresh
'End If

If someone can steer me in the right direction or has a better way of going about my problem, it would be greatly appreciated.


Thanks!

 

[JHB]

Try:

 Form_frmInvoiceNew.ListLineitemsold.RemoveItem Index:=Form_frmInvoiceNew.ListLineitemsold.ListIndex

 

[spikepl]

You can wind up in deep doodoo if you refer to items on forms using the syntax Form_formname.ControlName, because it can open closed forms invisibly in the background etc etc.

Use the syntax :

Forms!formName!ControlName.someMethodOrParameter

 

[RedWingsFan44]

First off thanks for replying!

JHB: I tryed using this:

 'If InvoiceY_N.Value = False Then
Form_frmInvoiceNew.ListLineitemsold.RemoveItem Index:=Form_frmInvoiceNew.ListLineitemsold.ListIndex

But I am now getting an runtime error 5: Invalid procedure call or argument.

I tryed checking the value in the immediate window for "?frminv.listlineitemsold.ListIndex" and it is giving me a '-1' value.

Spike: I did the above using the syntax you provided and nothing changed.

I couldnt really find a site that clearly explained the differences between using my way vs your syntax vs dimming a form and setting it equal to frmInvoiceNew.

Can someone shed some light on benefits of each?

 

[pr2-eugin]

If the ListIndex value is -1 then nothing is selected.. which means it will not be able to remove something that is not selected.. Show the code that you tried with spike's suggestion..

 

[RedWingsFan44]

I noticed that the listindex method partly works but only when a line is highlighted in the listbox and then i click on the checkbox to uncheck it. The problem is that no matter which line item I uncheck in the subform, the selected line in the listbox gets deleted even if the two dont match.

I have attached a sample workbook for anyone who wants to help.

Thanks in advance.

Edit: It looks like it is not possible to look for specific items. Is there some code where if the value is -1 then it autocorrects it, so that it still runs? Go easy on me, this might not make sense. I am a novice!

 

[RedWingsFan44]

Update: I have think I am headed in the right direction, but still need some help to figure this out...

I am getting a runtime error '7777' with this code, it says that I have used the ListIndex property incorrectly:

If InvoiceY_N.Value = True Then
InvLineTotal = ([PO Line Item Total])
Form_frmInvoiceNew.ListLineitemsold.AddItem ([textPOLine#] & ";" & (InvLineTotal))
EntryCount = EntryCount + 1
Me.Refresh
End If

If InvoiceY_N.Value = False And Form_frmInvoiceNew.ListLineitemsold.ListCount >= 1 Then
''if no selection, choose last list item
    If Form_frmInvoiceNew.ListLineitemsold.ListIndex = -1 Then
      Form_frmInvoiceNew.ListLineitemsold.ListIndex = Form_frmInvoiceNew.ListLineitemsold.ListCount + 1
    End If
Form_frmInvoiceNew.ListLineitemsold.RemoveItem Index:=Form_frmInvoiceNew.ListLineitemsold.ListIndex
Me.Refresh
End If
End Sub

with..

Private Sub Form_Open(Cancel As Integer)
EntryCount = 0
End Sub

Why is such a simple task so hard to implement? Any help is greatly appreciated

 

[Galaxiom]

ListIndex is always -1 when the listbox is multiselect. In this case it would be necessary to loop through the ItemsSelected Collection to address the selected lines.

A two column list box with an ItemList RowSource would need two values removed for each line.

Using a Item List for the row source of a list box with dynamic contents is not the way to go. Better base it on a query.

Personally I prefer to use fabricated ADO recordsets for listboxes where the list items displayed needs to be changed. This technique supports filtering and reordering without having to requery.

Fabricated ADO recordsets are also ideal for forms where the task involves clicking a checkbox to choose items. They do not require a checkbox field in the table.

Also please note Spike's advice and start referring to the objects using the correct syntax. There are no benefits to using the Form_ syntax.

 

[RedWingsFan44]

Thanks Galaxiom, I will switch syntax throughout the database.

Your post kind of went over my head... I will do some research on fabricated ADO recordsets, but could someone guide me as to what query the listbox would need to be based on? Should it be the same query that the form is based on? I will be experimenting with this but some guidance would be helpful.

Thanks

 

[RedWingsFan44]

If I base the listbox on a query instead of value list doesnt that mean I will not be able to use the additem/removeitem code?

On another note, the only reason I was using the longhand syntax is because it is much easier for me to use the Ctrl+Space to bring up the autofill list.

Sorry I am a noob

 

[Galaxiom]

I have not looked at your database but if I understand correctly the listbox's RowSource query would be based on the same data as the form, tblLineItems

The query would have the condition that the checkbox field is true.

When the checkbox is updated the record would be saved and the listbox requeried.

 

[Galaxiom]

The problem with the checkbox in the line items table is that every user would have the same checkbox field so each user's selections would interfere with each other.

You could copy the table to each user but that is messy. This is where the fabricated ADO recordset does what no other technique can do so effectively.

Fabricated ADO recordets are created by instantiating an ADODB recordset without a Connection property. Initially it has no fields and no data. The fields to be displayed (including the checkbox) are Appended to its Fields Collection and the recordset opened with no data.

Then open a recordset against the LineItems table, loop through the LineItems recordset and write the records to the fabricated recordset. Set the form's Recordset property to the fabricated recordset.

Clone the fabricated recordset and apply a filter based on the checkbox field so that only the checked items appear in the clone. Set the Listbox's Recordset Property to the clone.

 

[RedWingsFan44]

For the purposes of this project, only 1 user will be accessing the application. I put the checkbox in the line items table because I could not get a dynamic number of checkboxes to show up depending on how many line items are generated in the subform. I will look into the ADO recordset method that you mentioned.

Id be really grateful if someone can open up this database and help me out. I have been struggling with this for quite some time now.. I am sure that if someone knows what they are doing it is a 2 minute fix but I cannot find anyone with the necessary knowledge.

 

[JHB]

This will do it, (you can't use Form_frmInvoiceNew.ListLineitemsold.ListIndex because you haven't select anything in the control.):

Private Sub InvoiceY_N_Click()
  Dim ctl As Control, x As Integer
  Dim InvLineTotal As Currency
  
  If InvoiceY_N.Value = True Then
    InvLineTotal = ([PO Line Item Total])
    Me.Parent.ListLineitemsold.AddItem ([PO_Line__] & ";" & (InvLineTotal))
  Else
    Set ctl = Me.Parent.ListLineitemsold
    x = 0
    Do
      If CInt(ctl.ItemData(x)) = [PO_Line__] Then
        Me.Parent.ListLineitemsold.RemoveItem Index:=x
        Exit Do
      End If
      x = x + 1
    Loop Until x = ctl.ListCount
  End If
End Sub