How can I delete from a List Box BUT only on current record?

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

Guest

I have a combo box on a form (each record holds details of a different
course) for a user to choose a number of codes (these are codes for the names
of modules that each course offers). Pressing Add then puts the code into a
list box on the form. Sometimes two or more records are going to have the
same code attached (for example English & Language share some of the same
modules).

This is fine except, things change & say one of these modules is dropped
from only one of the two courses it has been attached to (e.g. English drops
module A1 but it stays on the Language course) - my problem is that if I
delete (using the Delete button on the form) the module code from the list
box on one record (e.g. English) I finds that it has also disappeared from
the other record (e.g. Language).

This is my first databse & I'm learning as I go along & so I guess that the
problem is that the code for the delete command is essentially saying "delete
that module code from the form" i.e. from every record it appears in, rather
than "delete that module code form the current record". My knowledge of
Visual Basic is very basic so I don't actually know how to do this or if I'm
even on the right track, please help! I've been wracking my brains for most
of the day trying to solve this!

Thanks in advance
Heidi
 
First, is the form in single view or continuous view?

You need a table to hold the selections in the listbox. This table will need
two fields, one for the ID of the current record and one for the ID of the
selected item. This table would be linked to the table the form is based on
using the ID field of the form's records and to the table that the Row
Source of the combo box is based on using the ID field for the selected
item. You would then fill the listbox by filtering its Row Source based on
the current record's ID field. The Row Source of the listbox would be a
query with two tables, the table you just created and the table that the
combo box's Row Source is based on. When you make a selection in the combo
box, it would actually add that selection to the new table and Requery the
listbox so that it will go pick up this new selection. When you delete an
item from the listbox, you will only delete the item with the ID that
matches the current record and the selected item.
 
Hi Wayne

I replied to this last night but my reply hasn't appeared so apologies if
you end up with 2 replies from me!
Anyway, my form is in single view.
I started the database with some of the code already written so I have just
tried to understand it & modify it. Unfortunately, I've now got stuck as I
obviously don't fully understand the code!

However, I'm pretty sure that I have followed your instructions (although
being a beginner I think I've done something incorrectly) & although I can
Add a selection from the combo box to the list box & it then only shows up on
the current record (& appears in the new table) I'm still having problems
deleting. When I delete a selection from the list box on a reocrd it will
still delete the same selection on all of the other records it appears on as
well (this is also reflected in the table) rather than only deleting the
selection from the current record.

I'm really not sure where I'm going wrong although I was a bit uncertain as
to how to do the following: "You would then fill the listbox by filtering its
Row Source based on the current record's ID field." I put the WHERE clause in
the Row Source refering to the current record's ID field (i.e. the name of
this field) but I'm not sure if that's what you meant??

Sorry if I'm making daft mistakes but I'm really new to all of this &
although I (think) can understand what you are telling me to do I don't then
neccessarily understand how to do it!

Thanks
Heidi
 
I'm using a delete command button on the form, so you click on the selection
in the listbox & press delete. I've pasted the code below ('EEMLinkTest' is
the table that holds the selections in the listbox; the listbox holds 'Module
Code/s' chosen from the combo box and the form's ID field is the 'External
Examiner'):

Private Sub Command85_Enter()
Dim MySet As Recordset, varItem As Variant
For Each varItem In List82.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("EEMLinkTest", dbOpenDynaset)
MySet.FindFirst "[External Examiner] = [External Examiner] And [Module
Code] = '" & List82.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem
List82.Requery
End Sub

The Row Source SQL for the listbox is:
SELECT ModsEEsTest.[Module Description_Module Code] FROM ModsEEsTest WHERE
ModsEEsTest.[External Examiner]=[External Examiner] ORDER BY
ModsEEsTest.[Module Description_Module Code];

Note: 'ModsEEsTest' is the query that is made from the 'EEMLinkTest' table
as above & the 'Module Description' table that the combo box's Row Source is
based on.

Thanks for your help & patience!
Heidi
 
Let's fix the FindFirst statement first and see how that goes.
MySet.FindFirst "[External Examiner] = [External Examiner] And [Module
Code] = '" & List82.Column(0) & "'"
Click to expand...

I believe that you want the second [External Examiner] to be a value from
the form. As currently written, it is inside the quotes so it is being
passed as part of the string and not as a value. You have two options here.
1) Give the full path to [External Examiner] (Forms!ThisFormName![External
Examiner]) so that FindFirst can interpret it. 2) Concatenate the value in
just as you do for the next parameter.

If you use #2, it would look like:

MySet.FindFirst "[External Examiner] = " & Me.[External Examiner] & " And
[Module Code] = '" & List82.Column(0) & "'"

Assuming that [External Examiner] is a number field.

--
Wayne Morgan
MS Access MVP


Spydii said:
I'm using a delete command button on the form, so you click on the
selection
in the listbox & press delete. I've pasted the code below ('EEMLinkTest'
is
the table that holds the selections in the listbox; the listbox holds
'Module
Code/s' chosen from the combo box and the form's ID field is the 'External
Examiner'):

Private Sub Command85_Enter()
Dim MySet As Recordset, varItem As Variant
For Each varItem In List82.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("EEMLinkTest", dbOpenDynaset)
MySet.FindFirst "[External Examiner] = [External Examiner] And [Module
Code] = '" & List82.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem
List82.Requery
End Sub

The Row Source SQL for the listbox is:
SELECT ModsEEsTest.[Module Description_Module Code] FROM ModsEEsTest WHERE
ModsEEsTest.[External Examiner]=[External Examiner] ORDER BY
ModsEEsTest.[Module Description_Module Code];

Note: 'ModsEEsTest' is the query that is made from the 'EEMLinkTest' table
as above & the 'Module Description' table that the combo box's Row Source
is
based on.

Thanks for your help & patience!
Heidi

Wayne Morgan said:
How are you deleting the item? Also, please post the Row Source SQL.

--
Wayne Morgan
MS Access MVP


news:[email protected]...
Click to expand...
Click to expand...
 
I used the second option & it works! Yay!
Thank you so much for your help!
Heidi

Wayne Morgan said:
Let's fix the FindFirst statement first and see how that goes.
MySet.FindFirst "[External Examiner] = [External Examiner] And [Module
Code] = '" & List82.Column(0) & "'"
Click to expand...

I believe that you want the second [External Examiner] to be a value from
the form. As currently written, it is inside the quotes so it is being
passed as part of the string and not as a value. You have two options here.
1) Give the full path to [External Examiner] (Forms!ThisFormName![External
Examiner]) so that FindFirst can interpret it. 2) Concatenate the value in
just as you do for the next parameter.

If you use #2, it would look like:

MySet.FindFirst "[External Examiner] = " & Me.[External Examiner] & " And
[Module Code] = '" & List82.Column(0) & "'"

Assuming that [External Examiner] is a number field.

--
Wayne Morgan
MS Access MVP


Spydii said:
I'm using a delete command button on the form, so you click on the
selection
in the listbox & press delete. I've pasted the code below ('EEMLinkTest'
is
the table that holds the selections in the listbox; the listbox holds
'Module
Code/s' chosen from the combo box and the form's ID field is the 'External
Examiner'):

Private Sub Command85_Enter()
Dim MySet As Recordset, varItem As Variant
For Each varItem In List82.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("EEMLinkTest", dbOpenDynaset)
MySet.FindFirst "[External Examiner] = [External Examiner] And [Module
Code] = '" & List82.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem
List82.Requery
End Sub

The Row Source SQL for the listbox is:
SELECT ModsEEsTest.[Module Description_Module Code] FROM ModsEEsTest WHERE
ModsEEsTest.[External Examiner]=[External Examiner] ORDER BY
ModsEEsTest.[Module Description_Module Code];

Note: 'ModsEEsTest' is the query that is made from the 'EEMLinkTest' table
as above & the 'Module Description' table that the combo box's Row Source
is
based on.

Thanks for your help & patience!
Heidi

Wayne Morgan said:
How are you deleting the item? Also, please post the Row Source SQL.

--
Wayne Morgan
MS Access MVP


news:[email protected]...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top