Reduce coding... Part 2 (Dlookup)

[onlylonely]

Hi Guys,



Is me again. Any method to reduce the dlookup coding?
I know is stupid not using bound form. But i've tried many times, it keeps failed and I don't know how to use bound form.
So, no choice, i need to use the stupid method that is "Coding".

The problem i'm facing with bound form is, when i add new data, i'm not sure why the previous data will at the new form.
Besides that, with using bound form, we need to ensure not everything we input will go into table and we need to use "Before update function" to prevent it to be input into the table.
But i have unique number need to input into new form, with the "Before update function" it keeps not showing my unique number. That's the reason i dont use bound form.

Back to my objective, dlookup will basically slow down my program and i aware of this. Any method to reduce this? Thx in advance.

Sub Load_CAPA()

DoCmd.OpenForm "F2CAPAform"


Forms!F2CAPAForm!txtscar = Forms!List!List2.Column(1)


Forms!F2CAPAForm!txtscar.Enabled = False


Forms!F2CAPAForm!txtscar = Forms!List!List2.Column(1)


Forms!F2CAPAForm!txtscar.Enabled = False


Forms!F2CAPAForm!txtCA = DLookup("Containment_Action", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAForm!txtCA.Enabled = False


Forms!F2CAPAForm!txtcad = DLookup("CA_Date", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAForm!txtcad.Enabled = False


Forms!F2CAPAForm!txtrc = DLookup("Root_Cause", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAForm!txtrc.Enabled = False


Forms!F2CAPAForm!txtCAN = DLookup("Corrective_Action", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAForm!txtCAN.Enabled = False


Forms!F2CAPAForm!txtcand = DLookup("CAN_Date", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAForm!txtcand.Enabled = False


Forms!F2CAPAForm!txtPA = DLookup("Preventive_Action", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAForm!txtPA.Enabled = False

[ranman256]

Stop using DLOOKUP.
theres no need for programming.
Connect the form to a query.
This does all the data loading and is 1000% faster.

[orange]

I agree with ranman---suggest you use a bound form. The BeforeUpdate event is the last point before the record is saved. So it is the event where validation of values is performed:
-if validation fails reject the record and provide a message to user
-if validation is successful, save the record.

As for

I don't know how to use bound form

, there are many articles and videos available --google/bing can help you find them. Watch some tutorials, then set up and work through a simple table/query and form.
Steve Bishop has several youtube videoss on MS Access - here is one re building a Form.

[ssanfu]

I also would strongly recommend using a bound form. I once had to create Add, Edit and Delete unbound forms for a Winery (a long ago) and it was a Major PITA!!

If you must use an unbound form, consider using an inline query instead of DLookup().
You didn't post much info about your dB or it's structure, so this might need some work.
.......
** Air code **

Sub Load_CAPA()
    Dim r As DAO.Recordset
    Dim tmpSCAR    '<<--- not sure what data type SCAR is
    Dim sSQL As String

    DoCmd.OpenForm "F2CAPAform"

    tmpSCAR = Forms!List!List2.Column(1)

    'SQL query
    sSQL = "SELECT Containment_Action, CA_Date, Root_Cause, Corrective_Action, CAN_Date, Preventive_Action"
    sSQL = sSQL & " FROM F2CAPAtbl"
    sSQL = sSQL & " WHERE SCAR = " & tmpSCAR
    'Debug.Pring ssql

    'Open query
    Set r = CurrentDb.OpenRecordset(sSQL)
    If Not r.BOF And Not r.EOF Then     '<<-- check for records
        With Forms!F2CAPAForm
            !txtscar = Forms!List!List2.Column(1)
            !txtscar.Enabled = False
            '            !txtscar = Forms!List!List2.Column(1)
            '            !txtscar.Enabled = False

            !txtCA = r("Containment_Action")
            !txtCA.Enabled = False
            !txtcad = r("CA_Date")
            !txtcad.Enabled = False
            !txtrc = r("Root_Cause")
            !txtrc.Enabled = False
            !txtCAN = r("Corrective_Action")
            !txtCAN.Enabled = False
            !txtcand = r("CAN_Date")
            !txtcand.Enabled = False
            !txtPA = r("Preventive_Action")

            !txtPA.Enabled = False
        End With
    Else
        ' No records matching criteria
        MsgBox "No records found for a SCAR of " & tmpSCAR
    End If

    r.Close
    Set r = Nothing

End Sub

Good luck with your project......

[onlylonely]

Hi All,

3 experts also asking me to use bound form.
Yes, i'm learning how to use bound form.
If you have time, please click below thread. I need expert help on this.

https://www.accessforums.net/showthread.php?t=80624