this one?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?
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
That is the one @moke123. Thank you.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
Code: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.Code: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.
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.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?Thanks for the code. Unfortunately nothing deleted.
yes - and your point is?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.
fromdate = Format(CDate(Me.lst_From_Date), "MM/DD/YYYY")
fromdate = "#" & fromdate & "#"
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
Or Not and just use it as is since it works as designed, and pretty easy to understand.Consider refactoring code as follows...
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