Delete selected item from list box (1 Viewer)

[HelpMoses76]

Can you please add the following line before the CurrentDb.Execute one and post a screenshot of the message you get? Thanks.

MsgBox strSQL

 

[HelpMoses76]

You need Delete * FROM

https://docs.microsoft.com/en-us/of...ference/delete-statement-microsoft-access-sql

Thank you for replying . I have * in there.

 

[Gasman]

Well I was not sure it was mandatory as you had not used it, but I always have not knowing any better? , so I had to check with that link?

Edit: In fact that link says [table.*] is optional ??

Well I have learnt something new tonight.

 

[theDBguy]

Edit: In fact that link says [table.*] is optional ??

I also think it's optional.

 

[HelpMoses76]

I might say "need" may be a strong word.

 

[Gasman]

I also think it's optional.

No, you know it is

 

[HelpMoses76]

View attachment 85230

I tried it with and without the * . Thank you again !

 

[Gasman]

I tried it with and without the * . Thank you again !

Well from what you have displayed, I cannot see what is wrong with it.?

 

[theDBguy]

View attachment 85230

Hi. Can you please do me one more favor? Go to the Ribbon > Create > Query and go to SQL View and paste the following and then try to Run it. Does it run? If you get an error message, please post a screenshot of it here. Thanks.

DELETE FROM BR-DETAILS WHERE ID=60

 

[HelpMoses76]

Hi. Can you please do me one more favor? Go to the Ribbon > Create > Query and go to SQL View and paste the following and then try to Run it. Does it run? If you get an error message, please post a screenshot of it here. Thanks.

DELETE FROM BR-DETAILS WHERE ID=60

1) It gave me a syntax error first

2) I put a bracket around the table name and ran it from SQL view and it worked...confirmed by opening the table as well
3) However when I put it back on the button on the form and put the bracket there ..no error message comes up anymore but it does not delete from the table either.

Any ideas?

 

[Isaac]

Been away for a little while, but just to clarify and add.

Did you mean the total number of columns in the lisbox ? If yes it is 22

No, what I meant is, the column that contains the ID, which column number is that in the Listbox properties?
For example, if your listbox rowsource is:

Select col1, IDCOLUMN, col2 from table

...then you could say the ID column is the "second one". And (by the way), when referring to it in code, it would have an Index of 1, since listbox columns begin numbering at 0 when it comes to their indexing, so the second column is column(1)
I couldn't necessarily tell if ID was the "first" or "second" column in your listbox, because, it might have had the same rowsource (as I just put above), but, you could have the Listbox's ColumnWidths property set to 0";1";1" (which would make the first column invisible, at runtime display).

The reason that I asked all of this is because, while a lot of people write code like this:

VariableName=Me.ListBoxName

or

strSQL = "select col1 from table where ID=" & Me.ListBoxName

...Both of those two bits of code, when they refer to Me.ListBoxName, rely on the "Default Value" of the Listbox, which will be the value in the Bound Column. (You can choose ANY of the listbox's columns to be the Bound Column, in the properties - 1st, 2nd, 3rd, so on).

That means, when writing code, you always have to remember which column happens to be the "Bound" column, and it also means, as your project grows and sometime you write code that needs to refer to a DIFFERENT column - which is not the Bound column, you can't use that method anyway.

This is why I often steer people away from relying overly on default properties, especially in this particular context, and simply using another technique that will ALWAYS work--whether you're referring to the bound column or not:

Me.ListboxName.Column(0) 'to get the value in the FIRST column
Me.ListboxName.Column(1) 'to get the value in the SECOND column

In order to suggest that you use that method, we would need to know which column (first, second, etc) of the Listbox the ID was in, hence that line of questioning.

Looks like you have plenty of help at this point, I just wanted to clarify where I was going, and more importantly, why.

 

[theDBguy]

1) It gave me a syntax error first

View attachment 85231

2) I put a bracket around the table name and ran it from SQL view and it worked...confirmed by opening the table as well
3) However when I put it back on the button on the form and put the bracket there ..no error message comes up anymore but it does not delete from the table either.

Any ideas?

Hi. Glad to hear you're making good progress. Since we cannot see what you're actually doing, can you please show us (screenshots) the latest changes you've made to the code? Specifically, how did you add the square brackets to the code? If you manually deleted ID=60, I hope you selected a different ID from the Listbox before trying to run the code again, correct?

 

[HelpMoses76]

Been away for a little while, but just to clarify and add.

No, what I meant is, the column that contains the ID, which column number is that in the Listbox properties?
For example, if your listbox rowsource is:

Select col1, IDCOLUMN, col2 from table

...then you could say the ID column is the "second one". And (by the way), when referring to it in code, it would have an Index of 1, since listbox columns begin numbering at 0 when it comes to their indexing, so the second column is column(1)
I couldn't necessarily tell if ID was the "first" or "second" column in your listbox, because, it might have had the same rowsource (as I just put above), but, you could have the Listbox's ColumnWidths property set to 0";1";1" (which would make the first column invisible, at runtime display).

The reason that I asked all of this is because, while a lot of people write code like this:

VariableName=Me.ListBoxName

or

strSQL = "select col1 from table where ID=" & Me.ListBoxName

...Both of those two bits of code, when they refer to Me.ListBoxName, rely on the "Default Value" of the Listbox, which will be the value in the Bound Column. (You can choose ANY of the listbox's columns to be the Bound Column, in the properties - 1st, 2nd, 3rd, so on).

That means, when writing code, you always have to remember which column happens to be the "Bound" column, and it also means, as your project grows and sometime you write code that needs to refer to a DIFFERENT column - which is not the Bound column, you can't use that method anyway.

This is why I often steer people away from relying overly on default properties, especially in this particular context, and simply using another technique that will ALWAYS work--whether you're referring to the bound column or not:

Me.ListboxName.Column(0) 'to get the value in the FIRST column
Me.ListboxName.Column(1) 'to get the value in the SECOND column

In order to suggest that you use that method, we would need to know which column (first, second, etc) of the Listbox the ID was in, hence that line of questioning.

Looks like you have plenty of help at this point, I just wanted to clarify where I was going, and more importantly, why.

That was it . The bound column was the issue . I changed it to 22 and that took care of the issue.
Also the bracket looks like it is a must.

Many thanks once again . I do not know how I would learn anything without this forum.

 

[theDBguy]

That was it . The bound column was the issue . I changed it to 22 and that took care of the issue.

Hi. Congratulations! Glad to hear you got it sorted out. Are you saying ID=60 didn't register to you earlier that it was the wrong ID? Just curious...

 

[HelpMoses76]

Hi. Glad to hear you're making good progress. Since we cannot see what you're actually doing, can you please show us (screenshots) the latest changes you've made to the code? Specifically, how did you add the square brackets to the code? If you manually deleted ID=60, I hope you selected a different ID from the Listbox before trying to run the code again, correct?

This is the latest version of the SQL string : strSQL = "DELETE FROM [BR-DETAILS] WHERE ID=" & Me.List248

Thank you once again for your help "theDBGuy" "Isaac" "Gassman"

Much much appreciated.

 

[theDBguy]

This is the latest version of the SQL string : strSQL = "DELETE FROM [BR-DETAILS] WHERE ID=" & Me.List248

Thank you once again for your help "theDBGuy" "Isaac" "Gassman"

Much much appreciated.

Hi. You're welcome. We were all happy to assist. Good luck with your project.

 

[Isaac]

That was it . The bound column was the issue . I changed it to 22 and that took care of the issue.
Also the bracket looks like it is a must.

Many thanks once again . I do not know how I would learn anything without this forum.

Very glad to hear you got it working!

 

[HelpMoses76]

re you saying ID=60 didn't register to you earlier

Hi. Congratulations! Glad to hear you got it sorted out. Are you saying ID=60 didn't register to you earlier that it was the wrong ID? Just curious...

Curiously it was showing the right IDs in the message box no doubt about that . It is after I changed the bound column it worked !
I have a lot to learn !

 

[theDBguy]

Curiously it was showing the right IDs in the message box no doubt about that . It is after I changed the bound column it worked !
I have a lot to learn !

Okay. We'll just chalk it up to experience. Cheers!

 

[Gasman]

Curiously it was showing the right IDs in the message box no doubt about that . It is after I changed the bound column it worked !
I have a lot to learn !

FWIW I *always* make my bound column the first field and hidden, as up to now they have always been autonumber fields.