Compose failure

[David44Coder]

I'm wanting to build the Where clause for Dlookup and Access continues to shut down as I attempt to compose the line.

The clause I want is
Dac =1446 And aName= "A Story" and (Nz([Location],"")=""

I've Dimmed c$ as chr$(34) and have (which is failing)

x = nz(DLookup("Location", "Table3", "Dac =" & MyID() & " And aName= " & c$ & aName & c$ & " and  (Nz([Location]," & c$ & c$ & ")=" & c$ & c$))

Any idea what's wrong ?

 

[arnelgp]

correct me if i interpret you Expression incorrectly:

you want the Location from Table3 on the Condition:

1. Dac field should be equal to whatever MyID() function will return, And
2. aName field should be equal to whatever string is in aName variable, And
3. Location Is Null.

therefore you are looking for a Location that is Null with the first two criteria above?

 

[David44Coder]

You are right arnelgp but I pasted the wrong thing It should be where Location is Not null, Yes to 1 and 2.

 

[arnelgp]

x = Nz(DLookup("Location", "Table3", "Dac =" & MyID() & " And aName= " & c$ & aName & c$ & " and  IsNull([location]) = False"))

 

[David44Coder]

Many thanks arneglp it's working nicely - and is much better not using Nz and quotes. It was frustrating trying to get it right as Access shut down about 5 or 6 times.

 

[David44Coder]

Another query is failing on one clause but only when run from the After Update event

s = "UPDATE Table3 SET Location = 'France' WHERE (([MyID])=1446) AND (([aName])='A Story');" 'AND nz([Location]) <> 'France';"

That works with the last bit remmed out. If I un-rem it, it tells me 0 records to update.
BUT - if I copy it here

Sub ff()
Dim s
s = "UPDATE Table3 SET Location = 'France' WHERE  (([MyID])=1446) AND (([aName])='A Story') AND nz([Location]) <> 'France';"
DoCmd.RunSQL s
End Sub

And run it, it correctly updates 1 record.
Is there any known reason for the failure in the Form module ? Thanks.

 

[David44Coder]

I figured out what it was. Even though this was in the After Update event the data had not been written yet to the table.
After Me.Dirty- False the query worked as intended. But .RecordsAffected was 0 when it should be 1.

 

[The_Doc_Man]

The question is, from where were you sampling .RecordsAffected - because that value is not a long-term property. It changes frequently.

 

[David44Coder]

It may be clearer if I show the whole routine. I see what Pat Says and can't explains why results seem to be at variance.
But it is updating as desired (only with Me.Dirty= false included)

Private Sub Location_AfterUpdate()
    On Error GoTo err_UU2
    If Nz(aName) > "" Then
        If Nz(Location) > "" Then
            Me.Dirty = False
            Dim c As String: c = Chr$(34)
            Dim sql As String
            Dim db As DAO.Database
            sql = "UPDATE Table3 SET Location = " & c$ & Location & c$ & " WHERE (((Nz([Location]," & c$ & c$ & "))=" & c$ & c$ & ") AND (([MyID])= 1446) AND (([aName])=" & c$ & aName & c$ & "));"
            CurrentDb.Execute sql
            If CurrentDb.RecordsAffected <> 0 Then MsgBox CurrentDb.RecordsAffected & " records updated"
        End If
    End If
    Exit Sub
err_UU2:
   If Err = 2501 Then Exit Sub  'Cancelled update
   MsgBox Error & Err.Description
    Stop
'Resume
End Sub

 

[MarkK]

It is always clearer if you show the whole routine.

CurrentDb is a function that returns a new DAO.Database object every time it is called. As a result, your code here....

    CurrentDb.Execute sql
    If CurrentDb.RecordsAffected <> 0 Then MsgBox CurrentDb.RecordsAffected & " records updated"

... executes sql against one instance, and tests the .RecordsAffected property of a different instance, so the result is always zero.

To fix the code, you could use a With block, like...

    With CurrentDb
        .Execute sql
        If .RecordsAffected <> 0 Then MsgBox .RecordsAffected & " records updated"
    End With

You can also declare a variable, assign CurrentDb to said variable, and just use that single instance throughout your code block.

hth,
Mark

 

[David44Coder]

Great, thank you Mark. Nicely resolved. Changing to it immediately