Full Version: Prevent an update of sql code on a form
UtterAccess Forums > Microsoft® Access > Access Forms
kappler
I would appreciate some help on preventing the following code from running
if the Cust and Code already exists in the table to be updated.
CODE
With Me.sfChangePrice.Form.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do Until .EOF
        
                strNS1 = Nz(.Fields(Me.sfChangePrice.Form.NS1.ControlSource), "")
                If Len(strNS1) > 0 Then
                
                strCust = .Fields(Me.sfChangePrice.Form.Cust.ControlSource)
                strCode = .Fields(Me.sfChangePrice.Form.Code.ControlSource)
                            
                strsql = "Update tblDiscounts " & _
                         "Set tblDiscounts .Disc1= '" & strNS1 & "' " & _
                         "Where tblDiscounts .Customer= '" & strCust & "' " & _
                         "AND tblDiscounts .ItemCode= '" & strCode & "';"
                rdoConn.Execute strsql
                End If
                 .MoveNext
            Loop
        End If
End With

I would like to know where in the above to place the code
Thanks in advance
LPurvis
Prevent it from running at all? Or just making it have no net effect?
Oassume that it would currently have an effect - as the [Disc1] field being set isn't already of the value strNS1?)
kappler
Hi Purvis
Yes, just prevent it from updating at all. At the moment
it simply adds what it finds if the criteria matches. So that
price on that customer's list is shown twice.
Hope I've clarified
LPurvis
Adds?
How does it add an entry from an Update table? (I presume tblDiscounts is a table yes? :-)

You *could* just have

CODE
strsql = "Update tblDiscounts " & _
nbsp;                        "Set Disc1= '" & strNS1 & "' " & _
                         "Where Customer= '" & strCust & "' " & _
                         "AND ItemCode= '" & strCode & "' " & _
                         "AND Not Exists (SELECT Null FROM tblDiscounts WHERE Customer= '" & strCust & "' " & _
                                          "AND ItemCode= '" & strCode & "')"


To make no updates if they already exist.
You could also perform a lookup first (by one means or another) to check for existence.
kappler
Hi Purvis
nserted your code but it still updates the table even though
Can entry that corresponds already exists. Any more ideas ?
LPurvis
I just sort of offered the code in concept without really pondering on your overall goal.
What you're saying is that your statement
pdate tblDiscounts
Set Disc1= 'XXX'
Where Customer= 'Cust1' AND ItemCode= '123'
is to update a field Disc1 for a specific Customer and ItemCode.
But you're asking for this code not to run... when the Customer and ItemCode already exists???
Umm... but for them to be updated at all - those records must already exist.
If you don't have the specified Customer and ItemCode - then no records will be updated.
If you prevent the Update from running when they *do* exist - then the statement will *never* do anything.
So I come back to my original question.
How can records be being created? I think you need to provide more detail - and perhaps an example MDB in action.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.