Solved

Deleting data by month using a form

Posted on 2014-07-23
19
188 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
[X]
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
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 85
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 85
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 85
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

626 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