Solved

Deleting data by month using a form

Posted on 2014-07-23
19
184 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add records to a form to a table 11 41
Access Changing Number to Date with Seperator 5 21
Problem copying record details to a new record 5 9
Modify report 8 8
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

777 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