Microsoft Access VBA / SQL issue?
Hello, I have created a form with a ListBox in Access that displays all records from a certain table. The issue I am running into is i created a delete button and added the following event procedure to it.
Private Sub DeleteCust_Click()
If IsNull(Me.Listbox) 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.LstRecords.Column(2) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "Delete * from Customer>Update:
Missed in line:
If MsgBox("Are you sure you want to remove '" & Me.LstRecords.Column(2) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
Changed to:
If MsgBox("Are you sure you want to remove '" & Me.Listbox.Column(2) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
(Still having the same issue)
Comments
I don't have everything here to look at so I'm kind of just guessing here. It looks like you may have a conflict in your If, Then Else setup. I've made a couple of quick notes on your code - it's actually worked for me a couple of times in just relocating the end if.
Private Sub DeleteCust_Click()
If IsNull(Me.Listbox) Then
MsgBox "Please select a record from the list to delete", vbCritical
Else
>>>>END IF HERE<<<<
DoCmd.SetWarnings False
If MsgBox("Are you sure you want to remove '" & Me.LstRecords.Column(2) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "Delete * from CustomerData Where Customer_Last=" & Me.Listbox & ";"
Me.Listbox.Requery
>>> End If remove here<<<
DoCmd.SetWarnings True
End If
End Sub
OR you may want to try something like this:
Private Sub DeleteCust_Click()
If IsNull(Me.Listbox) Then
MsgBox "Please select a record from the list to delete", vbCritical
Else
DoCmd.SetWarnings False
>>>>Else<<<< If MsgBox("Are you sure you want to remove '" & Me.LstRecords.Column(2) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "Delete * from CustomerData Where Customer_Last=" & Me.Listbox & ";"
Me.Listbox.Requery
>>>> End If delete<<<<
DoCmd.SetWarnings True
End If
End Sub
Other than that I imagine the form has only read access rights to the database. I'm a little rusty on my VB so I'm not sure if I'm even being helpful here, but I know it sucks when you're trying to get some help on this kind of stuff and nobody answer!!!
Is there a way to give the button special privleges to the database? I honestly ran into something similar about a thousand years ago and I'm pretty sure it ended up being something goofy like that.
i have run a common question to an get proper of entry to database from Excel 2007 (You did not aspect out what version you're utilising) and it pulls in all 5 decimals. possibly attempt operating a common question rather of a sq. question if that's only a undemanding get proper of entry to DB?
Try this instead of yours.
DoCmd.RunSQL "Delete from CustomerData Where Customer_Last='" & Me.Listbox & "';"