Solved Delete table entries based on from and to list box selections. (1 Viewer)

[Tom d]

I have a table with blood pressure readings. I load 2 list boxes with the tables entries. One for the delete from list and one for the delete to list.
I want to delete records from the table based on the entries selected. How do I do that?

 

[theDBguy]

Hi. Welcome to AWF!

You should be able to use a DELETE query. Since you won't be selecting multiple dates from each list, you might consider just using a Combobox instead of a Listbox. Just a thought...

By the way, why would you want to delete past readings? You would lose historical data. You can always just use a filter if you don't want to see certain dates in your report.

 

[Gasman]

Someone asked a similar question just recently, but I cannot find it now.
Solution was an IN() clause or a query for each selected item.

My money would be on the IN() clause.

Then again, I agree with theDBguy, why delete them in the first place?

 

[moke123]

Someone asked a similar question just recently, but I cannot find it now.
Solution was an IN() clause or a query for each selected item.

My money would be on the IN() clause.

Then again, I agree with theDBguy, why delete them in the first place?

this one?

Inserting multiple records at once with SQL

I have a vba script that inserts a record chosen from a list box into a table. But I have now made the list box multi-select. So when the user chooses multiple records from the table, I want to be able to insert them all at once. How is this usually done?

www.accessforums.net

 

[MajP]

Private Sub Command23_Click()
  Dim fromdate As String
  Dim todate As String
  Dim strSql As String
  If Not (IsNull(Me.lst_From_Date) Or IsNull(Me.lst_To_Date)) Then
    fromdate = Format(CDate(Me.lst_From_Date), "MM/DD/YYYY")
    fromdate = "#" & fromdate & "#"
    todate = Format(CDate(Me.lst_To_Date), "MM/DD/YYYY")
    todate = "#" & todate & "#"
    Debug.Print fromdate & " " & todate
    If CDate(Me.lst_From_Date) <= CDate(Me.lst_To_Date) Then
      strSql = "Delete * from [tlb Blood Pressures] where [date Entered] >= " & fromdate & " AND [date Entered] <= " & todate
      Debug.Print strSql
      CurrentDb.Execute strSql
    Else
      MsgBox "To date must be greater than from date."
    End If
  Else
    MsgBox "Enter valid to from dates."
  End If
  Me.lst_From_Date.Requery
  Me.lst_To_Date.Requery
End Sub

Your listbox queries are completely jacked. Need to pull the unique dates, no ID.

 

[Gasman]

this one?

Inserting multiple records at once with SQL

I have a vba script that inserts a record chosen from a list box into a table. But I have now made the list box multi-select. So when the user chooses multiple records from the table, I want to be able to insert them all at once. How is this usually done?

www.accessforums.net

That is the one @moke123. Thank you.

 

[Tom d]

Private Sub Command23_Click()
  Dim fromdate As String
  Dim todate As String
  Dim strSql As String
  If Not (IsNull(Me.lst_From_Date) Or IsNull(Me.lst_To_Date)) Then
    fromdate = Format(CDate(Me.lst_From_Date), "MM/DD/YYYY")
    fromdate = "#" & fromdate & "#"
    todate = Format(CDate(Me.lst_To_Date), "MM/DD/YYYY")
    todate = "#" & todate & "#"
    Debug.Print fromdate & " " & todate
    If CDate(Me.lst_From_Date) <= CDate(Me.lst_To_Date) Then
      strSql = "Delete * from [tlb Blood Pressures] where [date Entered] >= " & fromdate & " AND [date Entered] <= " & todate
      Debug.Print strSql
      CurrentDb.Execute strSql
    Else
      MsgBox "To date must be greater than from date."
    End If
  Else
    MsgBox "Enter valid to from dates."
  End If
  Me.lst_From_Date.Requery
  Me.lst_To_Date.Requery
End Sub

Your listbox queries are completely jacked. Need to pull the unique dates, no ID.

Private Sub Command23_Click()
  Dim fromdate As String
  Dim todate As String
  Dim strSql As String
  If Not (IsNull(Me.lst_From_Date) Or IsNull(Me.lst_To_Date)) Then
    fromdate = Format(CDate(Me.lst_From_Date), "MM/DD/YYYY")
    fromdate = "#" & fromdate & "#"
    todate = Format(CDate(Me.lst_To_Date), "MM/DD/YYYY")
    todate = "#" & todate & "#"
    Debug.Print fromdate & " " & todate
    If CDate(Me.lst_From_Date) <= CDate(Me.lst_To_Date) Then
      strSql = "Delete * from [tlb Blood Pressures] where [date Entered] >= " & fromdate & " AND [date Entered] <= " & todate
      Debug.Print strSql
      CurrentDb.Execute strSql
    Else
      MsgBox "To date must be greater than from date."
    End If
  Else
    MsgBox "Enter valid to from dates."
  End If
  Me.lst_From_Date.Requery
  Me.lst_To_Date.Requery
End Sub

Your listbox queries are completely jacked. Need to pull the unique dates, no ID.

Thanks for the code. Unfortunately nothing deleted.

 

[Tom d]

Thanks for the code. Unfortunately nothing deleted.

Access stores input into a Date/Time field as a floating point number; that is, a number with an integer part and a decimal part.


The integer part of a number stored in a Date/Time field relates to Access's built-in calendar that runs from January 1st 100 CE to December 31st 9999 CE. Access stores January 1st 100 CE as the number -657434; December 31st 9999 CE, as 2958465.

 

[MajP]

Thanks for the code. Unfortunately nothing deleted.

Of course the code works fine. You think i just pulled that off the top of my head without testing it?

 

[CJ_London]

Access stores input into a Date/Time field as a floating point number; that is, a number with an integer part and a decimal part.

yes - and your point is?

Listboxes columns are text and need to be converted to a date suitable for inclusion in a sql statement in a format it understands - which is either the US standard of mm/dd/yyyy or the sql standard of yyyy-mm-dd. And as you probably know, you need the # delimiters to tell the sql engine that the text between them should be treated as a date.

so your #02/12/2021# and #03/12/2021# you believe to be 2nd and 3rd December is being treated as 12th February and 12th March

sql is clever enough to know that #13/12/2021# is the 13th December since there are only 12 months in the year. If you had used that as your 'to' date, you would have deleted all your records

 

[MajP]

I still do not get it. The demo clearly works regardless of regional settings. I tested both US and British and it deletes all records between the two dates. This is immediately reflected in the comboboxes.
The key is that the the listbox converts all values to text so you first have to convert it to a date using cdate, Then you turn it back to a properly delimited string using the Format function in #mm/dd/yyyy# format since that is what is needed by sql (or another non ambiguous format).

    fromdate = Format(CDate(Me.lst_From_Date), "MM/DD/YYYY")
    fromdate = "#" & fromdate & "#"

 

[MarkK]

Consider refactoring code as follows...

Private Sub Command23_Click()
'   user interface code
    If PerformDeleteQuery Then
        Me.lst_From_Date.Requery
        Me.lst_To_Date.Requery
    End If
End Sub

Private Function Validate() As Boolean
'   validation code
    If IsDate(Me.lst_From_Date) And IsDate(Me.lst_To_Date) Then
        If Me.lst_From_Date <= Me.lst_To_Date Then
            Validate = True
        Else
            MsgBox "From date must preceed To date"
        End If
    Else
        MsgBox "Enter valid dates"
    End If
End Function

Private Function PerformDeleteQuery() As Boolean
'   execute the query specified by SQL
    Private Const SQL As String = _
        "DELETE * FROM [tlb Blood Pressures] " & _
        "WHERE [date Entered] >= p0 AND [date Entered] <= p1 "
    
    If Validate Then
        With CurrentDb.CreateQueryDef("", SQL)
            .Parameters("p0") = Me.lst_From_Date
            .Parameters("p1") = Me.lst_To_Date
            .Execute dbFailOnError
            PerformDeleteQuery = .RecordsAffected
        End With
    End If
End Function

- Separating interaction from validation from process can make your code easier to understand and maintain.
- Using a temp QueryDef to execute parameter queries, particularly for dates, means all delimiters and formats are handled automatically.

 

[MajP]

Consider refactoring code as follows...

Or Not and just use it as is since it works as designed, and pretty easy to understand.

 

[Pat Hartman]

I wouldn't delete the old data. I would use criteria to control what the report shows. I have a database to do the same tracking but it has more features. I'm not sure I ever shared it here before. I built it a few years ago to help my husband monitor his diabetes and added other types of tracking later. I added your report to it but the report uses criteria to select the data.

The database supports multiple people as well as multiple types of tracking. Add yourself and import your old data. Don't forget to run the first option to set up the default location for exports.

 

[moke123]

I was tinkering with this the other day when first posted.
When it came to validating the "from" and "to" dates I figured why even give the user the choice to choose a "to" date prior to the "from" date.
Set the "to" list row source in the after update of the "from" list. Only show dates equal to or greater than the date selected in the "from" list.

Private Sub lstFromDate_AfterUpdate()
    Dim strsql As String

    Me.lstToDate = Null

    strsql = "SELECT DISTINCT  DateEntered  FROM tblBloodPressure Where  DateEntered  >= " & SQLDate(Me.lstFromDate) & " ORDER BY  DateEntered ;"

    Me.lstToDate.RowSource = strsql

End Sub

You should also get rid of all the spaces in your table ,field and object names.

 

[Pat Hartman]

The reason for having a to date is so you can compare the stats during different periods. Is the medication working or is it not?

 

[The_Doc_Man]

@Tom d - just a suggestion. You have gotten all sorts of ideas where you have a DELETE * FROM table WHERE .... but things don't seem to work. As a simple test, make a SELECT query to select records using the same FROM and WHERE clauses. When you see a list of records and they clearly are ones you wanted to delete, then you can change the SELECT field-list to a DELETE * with some confidence that it works. In other words, do a divide-and-conquer approach. First select the records to be delete - and verify that they are in fact right - then delete them using a verified method of selection. When you do this, what you gain is the knowledge that the WHERE clause is correct.