Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Deleting data by month using a form

Posted on 2014-07-23
19
185 Views
Last Modified: 2014-07-30
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
0
Comment
Question by:Seamus2626
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 40214682
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
 
LVL 28

Expert Comment

by:omgang
ID: 40214689
...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
 

Author Comment

by:Seamus2626
ID: 40214694
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 84
ID: 40214718
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
 
LVL 28

Expert Comment

by:omgang
ID: 40214722
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40214992
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
 

Author Comment

by:Seamus2626
ID: 40226113
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
 

Author Comment

by:Seamus2626
ID: 40226114
Single column form, not a single room!
0
 
LVL 84
ID: 40226288
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
 

Author Comment

by:Seamus2626
ID: 40228742
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
 
LVL 84
ID: 40228761
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
 

Author Comment

by:Seamus2626
ID: 40228898
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
 
LVL 28

Expert Comment

by:omgang
ID: 40229049
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
 
LVL 28

Expert Comment

by:omgang
ID: 40229061
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
 
LVL 28

Expert Comment

by:omgang
ID: 40229070
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
 
LVL 28

Expert Comment

by:omgang
ID: 40229078
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
 

Author Comment

by:Seamus2626
ID: 40229222
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
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 40229285
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
 

Author Closing Comment

by:Seamus2626
ID: 40229989
OM Gang , THANK YOU!!!

All sorted
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question