Deleting data by month using a form

I have my list box on a form which displays "Jan-Dec", i have a function in place, and a button called delete. So im thinking a user selects the month from the listbox and then presses the button delete.

My problem here is my delete query is doing nothing!

When i run the delete query manually, i get the inbuilt box "Enter Parameter Value" get_glbMonthToDelete

I think im close but am missing a cruical step/link!!

I have attached a screen shot of my form

Thanks



My delete query
Blady blah
FROM All_Data_Static
WHERE (((All_Data_Static.Date)=get_glbMonthToDelete));

My function

Function get_glbMonthToDelete() As String
get_glbMonthToDelete = glbMonthToDelete
End Function

a module - Option Compare Database

Public glbMonthToDelete As String

My button

Public Sub Command12_Click()
If Not IsNull(Me.List10) Then
       glbMonthToDelete = Me.List10
       DoCmd.OpenQuery "Delete_Month"
End If


End Sub
Doc2.docx
Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Do this as a first step in troubleshooting

Public Sub Command12_Click()
Debug.Print Me.List10     '<-----------
 If Not IsNull(Me.List10) Then
        glbMonthToDelete = Me.List10
        DoCmd.OpenQuery "Delete_Month"
 End If



OM Gang
0
omgangIT ManagerCommented:
...also
The WHERE clause in your query
WHERE (((All_Data_Static.Date)=get_glbMonthToDelete));
is it expecting a DATE value?  The function you have created returns a string value.
OM Gang
0
Seamus2626Author Commented:
I found the below piece of code online but am getting the error message

"Method or data member not found"

Its highlighting .Lst10

That is my listbox name....

--------------

Public Sub Command12_Click()
If IsNull(Me.Lst10) Then
MsgBox "Please select a record from the list to delete", vbCritical
Else

DoCmd.SetWarnings False
If MsgBox("Are you sure you want to remove '" & Me.Lst10.Column(1) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "Delete * from All_Data_Static Where ID=" & Me.LstRecords
Me.LstRecords.Requery
End If
DoCmd.SetWarnings True

End If
End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In general you should refer to the Column value of your Listbox (and use Nz instead of IsNull):

If Nz(Me.lst10.Column(0), 0) = 0 Then
  Msgbox "Please select a record"
Else
  '/ delete here
End If
0
omgangIT ManagerCommented:
Where is it highlighting .Lst10?  If it's in this statement
If MsgBox("Are you sure you want to remove '" & Me.Lst10.Column(1) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
I'm betting your list is only a single column?  Me.Lst10.Column(1) is actually referring to the second column in the list; Access List and ComboBox controls are zero based meaning the first column is indexed at 0, the second column at 1, etc.  The error message
Method or data member not found
is indicating the second column cannot be found.

If my assumption is correct try changing to
If MsgBox("Are you sure you want to remove '" & Me.Lst10.Column(0) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then

OM Gang
0
Helen FeddemaCommented:
Your code will be a lot more readable if you give your controls meaningful names, using the standard prefixes, such as cboSelectMonth, lstMonths, etc.  Also, for debugging purposes, assign values picked up from controls to variables of the appropriate data type, and display them in the Immediate Window with Debug.Print statements.
0
Seamus2626Author Commented:
I changed the code to the below, but am getting the same error message.

I attached a screensot of my form, it is a single room

Thanks


 
Public Sub Command12_Click()
If IsNull(List10) Then
MsgBox "Please select a record from the list to delete", vbCritical
Else

DoCmd.SetWarnings False
If MsgBox("Are you sure you want to remove '" & Me.Lst10.Column(0) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then


DoCmd.RunSQL "Delete * from All_Data_Static Where ID=" & Me.LstRecords
Me.LstRecords.Requery
End If
DoCmd.SetWarnings True

End If
End Sub

Open in new window

Doc1.docx
0
Seamus2626Author Commented:
Single column form, not a single room!
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Which line is throwing the error?

As indicated above, you should reference the .Column property to insure that the code gets the correct value:

DoCmd.RunSQL "Delete * from All_Data_Static Where ID=" & Me.LstRecords.Column(0)
0
Seamus2626Author Commented:
Sorry, my mistake, error is being thrown out at

LstRecords from the line


DoCmd.RunSQL "Delete * from All_Data_Static Where ID=" & Me.LstRecords

So, the column issue is fixed

Now it is saying member or data method not found on "LstRecords"

Thanks
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you absolutely sure that "lstRecords" is the correct spelling for the Listbox name, and that you're running this code on the Form that contains that Listbox? Object Not Found basically means VBA cannot find it on the form.
0
Seamus2626Author Commented:
I attached my DB to make it easier, so i cannot cuurently select the months from the drop down which i think is the problem, but im not sure why i cant select?
Pilot-DB.zip
0
omgangIT ManagerCommented:
The Listbox name on your form is List10 (not Lst10 nor LstRecords).

So, in the button click event you need to change

'If MsgBox("Are you sure you want to remove '" & List10.Column(1) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
'DoCmd.RunSQL "Delete * from All_Data_Static Where ID=" & Me.List10

If MsgBox("Are you sure you want to remove '" & List10.Column(0) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "Delete * from All_Data_Static Where ID=" & Me.List10.Column(0)

OM Gang
0
omgangIT ManagerCommented:
Also, you have the listbox List10 bound to the Date column/field from your table.  Two things:
1) You can't assign a Text value such as Jan, Feb, Mar, etc. to a Date value field
2) Date is a reserved word in Access & VBA so you really shouldn't use it as a field/column name

OM Gang
0
omgangIT ManagerCommented:
The reason you couldn't select a value from the list is because you had the Form property
Allow Edits = No
Form PropertiesOM Gang
0
omgangIT ManagerCommented:
You SQL statement says to delete records where the value of the ID column equals the value selected in the list.  There is no ID column in the table.
OM Gang
0
Seamus2626Author Commented:
Thanks for your pointers OMG,

Maybe it will help if f i outline what im trying to do.

I want the user to be able to select Jan-Dec in the dropdown list, then if they press delete Jan, the SQL recognises Jan as =  01/01/2014 and deletes all records in table " All_Data_Static" that have a date 01/01/2014

Many thanks
0
omgangIT ManagerCommented:
I've attached your db with some changes.
1) I renamed the Date field in the table to MyDate
2) I modified the Listbox on the form so it is unbound, contains two columns of data (second is hidden)
3) I modified the button click event procedure so that it works with the above changes

Records are now successfully deleted from the table as you desire.
I recommend further changes to the LIstbox so that it's not limited to the manually entered date values, e.g. only 2014.  

OM Gang
Pilot-DB.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
OM Gang , THANK YOU!!!

All sorted
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.