Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 8
  • 7
  • 3
  • +1
1 Solution
 
omgangCommented:
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
 
omgangCommented:
...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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 
omgangCommented:
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
 
omgangCommented:
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
 
omgangCommented:
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
 
omgangCommented:
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
 
omgangCommented:
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
 
omgangCommented:
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
 
Seamus2626Author Commented:
OM Gang , THANK YOU!!!

All sorted
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now