How to delete entry in listbox, and prevent textbox re-population?

[Adam Tippelt]

This thread has two questions, however they are linked around the same area and solving one may solve the other:

A) I've created a form for adding different records, complete with a subform for applying requirements to each record.
Within the subform, when you add a data requirement - via a textbox - I've also made a listbox to display what requirements the user has added so far. This also means that should a user make a mistake, they can select the option in the list, and click a delete button to remove the entry.
However, I can't get the delete function to work - code such as the following has not worked:

Dim strSQL As String
 
    strSQL = "DELETE * " & _
        "FROM [DataRequirements] " & _
        "WHERE [DataRequirements].[Data Requirement]=" & Me![List6].Column(0)
    DoCmd.RunSQL strSQL
    Me![List6].Requery
 
End Sub
 

DataRequirements is the name of the Subform.
Data Requirement is a column name within the subform.
List6 is the name of the listbox.

Any idea how I can delete the entry in the listbox - this should in theory delete the entire row from the subform's table.

B) As well as this, one thing I've noticed is if I select an option in the listbox to delete, when I do so this re-populates the entry into the textbox used to add the requirements. This means that if I do this, and then close the form, it ends up duplicating that entry because the text has appeared again in the textbox - is there a way to prevent the listbox selection from re-populating the textbox?

Any help on either of the two questions would be muchly appreciated. I've had to quickly teach myself MS Access having never used it before, so am not an expert yet - my knowledge of Access is intermediate while my knowledge of VBA is slightly worse, so I should be able to understand most of your answer.

Thanks.

[mshmyob]

Looking quickly your SQL statement looks wrong.

You are using a SQl Delete command and therefore you need to indicate the TABLE you are deleting from NOT the subform.

Also the WHERE clause needs to indicate the column name in the table not the subform control name (unless they are both the same).

Change your subform name to your table name and the subform control name to the actual column name in the table.

Then let us know if that works or what error you get.

cheers,

[Adam Tippelt]

Thanks for the reply mshmyob.

I've just realised a mistake I put in my initial post - I wrote the wrong descriptions. What it should have read is:

DataRequirements is the table.
Data Requirement is a column within the table.
List6 is the listbox on the form.

With this in mind, the SQL already holds true to what you were suggesting in the first sentence.

Though I'm still not sure about the WHERE clause. The SQL is supposed to indicate that if the option is clicked/highlighted in the listbox, then that's the row that gets deleted from the table - I don't think the syntax there is right, but I don't know what to use.

Currently when I click the button, nothing happens. It doesn't work, but at the same time doesn't produce any errors. It just simply does nothing.

Any other help would be muchly appreciated.

Thanks.

[ADezii]

1. Is the [Data Requirement] Field a String?
2. Is the Delete Button in the Main or Sub-Form?

[Adam Tippelt]

1. [Data Requirement], now renamed as [Data_Requirement] is a text field, so yes it's a string.

2. Delete button is in the sub-form, as I only want it to delete the data requirement attached, instead of the whole record.
Main form is connected to a separate table, named TestLibrary.

[ADezii]

Try:

"WHERE [DataRequirements].[Data_Requirement]='" & Me![List6].Column(0) & "'"

[Adam Tippelt]

Aha, that's got the functionality working - can you explain why what you did worked?

It's working, however there is an issue:

If a user inputted the same requirement twice, and then realised this and went to delete one, it won't let you delete just the one you've selected, but instead deletes all of them with indentical names. Can I stop it from doing this?
Is it a case of referencing a different column so that it checks the unique ID number in the table matches the listbox one, instead of the data requirement? The listbox only displays the data requirement column, however a unique ID column does exist.

Thanks.

[ADezii]

Aha, that's got the functionality working - can you explain why what you did worked?

Since the [Data_Requirement] Field is TEXT, it needs to be Delimited by "'s" in the WHERE Clause Expression.

"WHERE [DataRequirements].[<UniqueID>]=" & Me![List6].Column(?)"

[mshmyob]

That is correct. All records matching your Where clause will be deleted. If you wish to delete a single record you must use some kind of identifier that is unique to the record - usually the Primary Key. Deleting a specific record by the Primary key is the preferred way to go.

cheers,

[Adam Tippelt]

Yes I would like to change it to delete via the Primary Key. However, the list box only displays the non-primary column, and nothing else, so is it still possible to do? I can't remember entirely, but as the listbox always seems to pull through the Primary Key, the ID column should have been included in the setup, but hidden from view.

As the only displayed column is column 0, how would I reference a hidden column, if that sort of thing is possible? Or would I want a different approach?

Thanks.

[mshmyob]

You are correct that the first column is usually based on the primary key.

Could you post the rowsource for your combo box.

Just click on the rowsource and press Shift F2 to zoom and then copy and paste.

cheers,

[Adam Tippelt]

Might just point out that it's not a combo box, it's a listbox.

The rowsource is a query I built, so zooming on it only produces the name of the query, which isn't much help, so I've pasted in the SQL for the query:

SELECT DataRequirements.Data_Requirement
FROM DataRequirements
WHERE (((DataRequirements.Ref_ID)=[forms]![TestCreator]![Ref_ID]));

As I can see now, it's not pulling through the Primary Key (Script_ID) at all, which is probably why it can't find it. However just adding it into the SELECT row makes the form unusable, because it throws up a message box asking for an input value of Script ID, which is now where I'm stuck at.

Cheers.

[mshmyob]

Could you attach this part of your database (the query, tables related to this form , and the form itself) so I can look at it?

cheers,

[Adam Tippelt]

I've attached the areas of the database concerning this issue.

Please note that I do have two 'delete buttons' that I'm working on, one is delete single and one is delete all - this thread is concerning the delete single option.

Let me know if I need to explain anything.

Thanks.

[mshmyob]

Your Delete button should be like so:

On Error GoTo Err_Command8_Click
Dim strSQL As String
Dim lngItem As Long
 
'Delete Record Button.
'Allows user to delete single data requirements that they want to remove.
lngItem = Me.List6.ListIndex
 
    strSQL = "DELETE * " & _
        "FROM [DataRequirements] " & _
        "WHERE [DataRequirements].[Data_Requirement]= '" & Me.List6.ItemData(lngItem) & "'"
     strSQL = strSQL & " AND [DataRequirements].[REF_ID]= " & Forms!TestCreator.Ref_ID
    DoCmd.RunSQL strSQL
    Me![List6].Requery
 
Exit_Command8_Click:
 
Err_Command8_Click:
        Exit Sub
    MsgBox Err.Description
    Resume Exit_Command8_Click
 

Notice I changed your SQL string only.
1. You need to reference the value selected from your list box using the "listindex and itemdata" methods.
2. You need to further filter your WHERE clause by including the "Ref_ID" since many records could have the same "data requirement".

Hope this helps.

cheers,

[Adam Tippelt]

That half solved the problem...

Ref_ID is a foreign key to the TestLibrary, whereas Data_ID is the primary key. Using that setup means that deleting a requirement doesn't delete identical requirements connected to other tests, however if you've accidentally written the same requirement twice for one test, you still end up deleting both.

What you wrote did provide me with something I was missing though (lngItem), and what you've suggested has helped me find an answer:

I changed the listbox query so that it also pulled through the Data_ID. Then I altered the code to:

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim strSQL As String
Dim lngItem As Long
 
lngItem = Me.List6.ListIndex
 
    strSQL = "DELETE * " & _
        "FROM [DataRequirements] " & _
        "WHERE [DataRequirements].[Data_Requirement]= '" & Me.List6.ItemData(lngItem) & "'"
        strSQL = strSQL & " AND [DataRequirements].[Data_ID]= " & Me.List6.Column(1)
    DoCmd.RunSQL strSQL
    Me![List6].Requery
 
Exit_Command8_Click:
 
Err_Command8_Click:
        Exit Sub
    MsgBox Err.Description
    Resume Exit_Command8_Click
 
End Sub
 

I also changed the listbox so that it pulled through both columns, but with the ID column at width 0cm, so that it remains hidden.

This seems to work how I want it to, so thank you for your help, couldn't have got there without your suggestions.
Now all I'm stuck on is problem B)....

[mshmyob]

Ok I have made some minor changes.

To reflect how things in your app should be I have checked for duplicates BEFORE adding to the database. You should never allow a person to add duplicates if they are not allowed.

I have therfore done the following:

1. I left my listbox code the way I had it originally
2. I changed the code for when you add your data requirements to check for duplicates immediately and not allow them
3. I fixed problem "B" by removing the "Control Source" property for your listbox (since it was bound to DataRequirment.

Code for checking for duplicates - Your Add Requirment button:

On Error GoTo Command43_Click_Err
Dim lngDupCheck
 
    On Error Resume Next
    If IsNull(Me.Ref_ID) Then
    MsgBox "You cannot add data requirements without creating a test.", vbExclamation + vbOKOnly
    Else
    If IsNull(Me.Data_Requirement) Then
    MsgBox "You cannot add a blank data requirement.", vbExclamation + vbOKOnly
    Else
    ' check for duplicate data requirement and reject if so
    If IsNull(DLookup("Data_ID", "DataRequirements", "[DataRequirements].[Data_Requirement]= '" & Me.Data_Requirement & "' AND [DataRequirements].[REF_ID]= " & Forms!TestCreator.Ref_ID)) Then
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.GoToRecord , , acNewRec
        Me.List6.Visible = True
        Me.Label10.Visible = True
        Me.Command8.Visible = True
    Else
        MsgBox ("Duplicate Data requirment already exists")
        Me.Undo
    End If
    End If
    End If
 
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
 
 
Command43_Click_Exit:
    Exit Sub
 
Command43_Click_Err:
    MsgBox Error$
    Resume Command43_Click_Exit
 

I have also reattached the data base for you.

cheers,

[Adam Tippelt]

Hmmm I don't know why I didn't think about that before...It's hard to tell whether a situation might arise where duplicates WOULD be required...however that could easily be solved by changing the reject box to a confirmation box to check if the user wanted to add a duplicate....hmm ideas ideas ideas....

Either way, you've fixed both my problems now. I really must remember to check control sources first before asking questions!

Thank you very much for your help.

[mshmyob]

You're welcome.

cheers,