Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Accedss SQL Code

I want to use a message box response to selectively delete records from a table based on two criteria.  the code workd for the first WHERE criteria but not the second.  The variable shows up in the right place and everything looks like it shuld work ..... but it doesn't.  Can anyone point out the flaw?

I thought I sent this question yesterday but apparently it didn't go through.  Sorry if I'm repeating.
Private Sub Delete_Click()
On Error GoTo Err_Delete_Click
 
Dim db As Database
Dim dbqt As String
Dim yesh As Boolean
Dim button As Boolean
Dim buttonclicked As Integer
 
 
 
 
yesh = True
button = True
dbqt = (Chr$(34))
 
Set db = CurrentDb
buttonclicked = MsgBox("To Delete Purchased (Purchasing) Items Click YES" & (Chr$(13)) & (Chr$(13)) & " To Delete Manufactured (Production) Items Click NO", 291, "Delete Manufactured Parts or Purchased Parts")
 
 
 
If buttonclicked = 6 Then
button = True
ElseIf buttonclicked = 7 Then
button = False
End If
 
db.Execute "Delete * FROM [Requisition Pur] WHERE ((([Requisition Pur].Ordered)= " & yesh & ") AND (([Requisition Pur].Purch)= " & button & "));"
 
'Dim MyQuery As String
'DoCmd.SetWarnings False
'MyQuery = "Requisition Ordered-Delete"
'DoCmd.OpenQuery MyQuery
'DoCmd.SetWarnings True
 
Me.Requery
 
Exit_Delete_Click:
    Exit Sub
 
Err_Delete_Click:
    MsgBox Err.Description
    Resume Exit_Delete_Click
End Sub

Open in new window

0
subpik
Asked:
subpik
  • 4
  • 3
1 Solution
 
rockiroadsCommented:
First thing I saw was you wasnt handling cancel on the msgbox. Also better to use predefined constants

Private Sub Delete_Click()
   
    Dim db As dao.Database
    Dim dbqt As String
    Dim yesh As Boolean
    Dim bPurch As Boolean
    Dim iBtnClicked As Integer
    Dim sSql As String
     
     
    On Error GoTo Err_Delete_Click
     
     
     
    yesh = True
    dbqt = (Chr$(34))
     
    Set db = CurrentDb
    iBtnClicked = MsgBox("To Delete Purchased (Purchasing) Items Click YES" & vbCrLf & vbCrLf & "To Delete Manufactured (Production) Items Click NO", vbYesNoCancel + vbQuestion, "Delete Manufactured Parts or Purchased Parts")
     
    'YOU HAVE TO HANDLE CANCEL
    If iBtnClicked = vbCancel Then Exit Sub
   
    If iBtnClicked = vbYes Then
        bPurch = True
    Else
        bPurch = False
    End If
     
    sSql = "Delete * FROM [Requisition Pur] WHERE Ordered = " & yesh & " AND Purch = " & bPurch
    Debug.Print sSql
    db.Execute sSql
     
    'Dim MyQuery As String
    'DoCmd.SetWarnings False
    'MyQuery = "Requisition Ordered-Delete"
    'DoCmd.OpenQuery MyQuery
    'DoCmd.SetWarnings True
     
    Me.Requery
     
Exit_Delete_Click:
        Exit Sub
     
Err_Delete_Click:
        MsgBox err.Description
        Resume Exit_Delete_Click
End Sub


Now in this code, I have dumped the sql to the immediate window so it should produce something like this

Delete * FROM [Requisition Pur] WHERE Ordered = True AND Purch = True

Can you confirm that Purch is in fact defined as a yes/no column in your DB?
0
 
ducky801Commented:
Like RockiRoads mentions, check your data type on Purch to ensure that it is yes/no (boolean).  
Normally, when i work with boolean values with VBA/SQL i'll use -1 for true and 0 for false, because these are the underlying values that Access uses in the table.
Additionally, I noticed that you have a column called 'ORDERED', I wonder if this is confusing the system because of the SQL Reserved word 'ORDER' used in the ORDER BY Clause.  Try this and let me know if it works:
 
Delete * FROM [Requisition Pur] WHERE [Requisition Pur].[Ordered] = -1 AND Purch = -1
 
Hope this helps
 
AR
 
0
 
subpikAuthor Commented:
This line still seems to be the sticking point.

    sSql = "Delete * FROM [Requisition Pur] WHERE Ordered = " & yesh & " AND Purch = " & bPurch

When I have 4 records marked "Ordered" and 2 marked "Purchased",and choose yes, both variables are showing "True" in the mouseover when I step through, but no records are deleted.  When I choose "No",  yesh=True and bPurch=false, all 4 "ordered" records delete.  And yes, Purch is a yes/no field in the table.

Using 0 and -1 does not seem to make a difference.  I had tried using 6 for true and 7 for false because I noticed those were the values returned by buttonclicked".

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rockiroadsCommented:
That doesnt make sense unless data in table is set to the criteria

From what you said, it sounds the opposite

when Purchased, nothing deletes, when not purchased then they delete

Setup your data then confirm the data by running this sql

select * from [Requisition Pur] WHERE Ordered = true and aND Purch = true

and try the variants, to see what data you got.




0
 
rockiroadsCommented:
What Im getting at is the source, how you set that data up originally
0
 
subpikAuthor Commented:
Please see the attached file.  I think this is relative to the question you are asking.  

What I'm trying to achive is, when I click yes, it deletes all records where purch and ordered are true.  When I click no, it deletes all records where ordered is true and purch is false.
Currently I do this sucessfully with a delete parameter query but the user always has to type in "yes" or "no".  I'd like to be more efficient by making it a one click operation.

There is a query between the table and the form but I can manually delete frome either so I don't think it is a locked situation.
Tables.pdf
0
 
rockiroadsCommented:
I cannot see what is wrong, perhaps some db corruption? maybe try a compact/repair on your db
0
 
subpikAuthor Commented:
i got it, finally.  The form's underlying query was getting the "Purch" data from the wrong table.  thanks
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now