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.

 

[Pat Hartman]

After Update event

What after update event? The data is written to the table BEFORE the Form's AfterUpdate event. It is not written for each control.

Also, always compose into a variable. That way you can add a stop to the code and print the string in the debug window to see if it is correct.

 

[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

 

[Pat Hartman]

REMOVE the me.dirty = false from this event. It does not belong here. You NEVER want to force a record to save before all validation is complete. So, you would never have this code in a control level event. You don't want the record to be saved until the form is complete. Most validation code belongs in the FORM's BeforeUpdate event because validation code in the control events does not run unless the control has been changed. So if you had validation code in two control events but you never changed controlA. In your form, the record would be saved in controlB and the validation of the value in controlA would never happen especially if you are not using the control's BefoeUpdate event to do the validation.

The update to the table also does not belong in this event. It belongs in the FORM's AfterUpdate event so it doesn't happen until after the form is completely validated and the record is saved.

Then we get to the second problem which is the important one. Why are you storing this data in two places? Data should NEVER be stored in multiple places. Please do some reading on database normalization so you can understand why this is such a bad practice.

Then we get to the picky issues. It is far easier to manage code if you put all your Dim's at the top of the procedure. Also as MarkK mentioned, declaring a CurrentDB variable eliminates the problem you ran into.

Dim db AS DAO.Database
Set db = CurrentDB

Then use db instead of CurrentDB throughout the procedure. I don't like using With. It is certainly not wrong to use it. I just don't like it because sometimes the .something is so far away from the With that it is easy to loose track of what it belongs to and also, if you have nested recordsets, you can't use the . reference because Access doesn't know which With the object is attached to.