Help me on coding - make it short and simple.

[onlylonely]

Hi All,

I have write a coding and it makes the program super slow.
Anyone can help me to reduce the coding ?


It basically to retrieve the data from table. But with coding below, it makes it slow, when i click back button, it needs to take about 10seconds.

Private Sub bthback_Click()

DoCmd.OpenForm ("F2CAPAForm")


Forms!F2CAPAform!txtscar = Forms!F3Implementationform!txtscar


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


Forms!F2CAPAform!txtpad = DLookup("PA_Date", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAform!txtpad.Enabled = False


Forms!F2CAPAform!txtresp = DLookup("Responder", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAform!txtresp.Enabled = False


Forms!F2CAPAform!txtresppd.Enabled = False


Forms!F2CAPAform!txtrespdate = DLookup("Responder_date", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAform!txtrespdate.Enabled = False


Forms!F2CAPAform!txtrev = DLookup("Reviewer", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAform!txtrev.Enabled = False


Forms!F2CAPAform!txtrevpd.Enabled = False


Forms!F2CAPAform!txtrevdate = DLookup("Reviewer_Date", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAform!txtrevdate.Enabled = False


Forms!F2CAPAform!txtqm = DLookup("WPPL_QM", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAform!txtqm.Enabled = False


Forms!F2CAPAform!txtqmpd.Enabled = False


Forms!F2CAPAform!txtqmdate = DLookup("QM_Date", "F2CAPAtbl", "SCAR = Forms!F2CAPAForm!txtscar")


Forms!F2CAPAform!txtqmdate.Enabled = False


Forms!F2CAPAform!btnsubmit.Enabled = False


Forms!F2CAPAform!btnrwappr.Enabled = False


Forms!F2CAPAform!btnrevrej.Enabled = False


Forms!F2CAPAform!btnqmapp.Enabled = False


Forms!F2CAPAform!btnqmrej.Enabled = False


DoCmd.Close acForm, "F3Implementationform"




End Sub

Thank You

[ranman256]

Access does not need programming for most everything.
Dlookups are slow.
Connect the form to a query.
the fields populate from that. Fast.

[onlylonely]

Access does not need programming for most everything.
Dlookups are slow.
Connect the form to a query.
the fields populate from that. Fast.

Hi Ranman,

I followed your instruction, instead of using query, i directly use "bound" form.
The idea is , when i click back, the unique number will bound will table and retrieve the data.
But it seems like doesnt work. Can you please teach me how to do it?

Private Sub btnback2_Click()

DoCmd.OpenForm ("Q2CAPAForm")


'Forms!Q2capaform!SCAR = Forms!F3Implementationform!txtscar


Forms!Q2capaform!SCAR.Enabled = False


DoCmd.Close acForm, "F3Implementationform"




End Sub

When i click back...the it appear this.

[accesstos]

Your code try to open a new form with the same data, but in the post, seems that you want to go to the previous record of the current form.
What exactly you try to do?

For the 1st scenario, do follow these steps:
Form "F2CAPAForm" in design mode:

• Bind the form on "F2CAPAtbl".
• Bind each textbox to the desired field of the table above.
• Set the control's property "Enabled" to False.

Save changes and close.

Code for the Back button:

Private Sub btnback2_Click(
    DoCmd.OpenForm "F2CAPAForm",,,"SCAR=" & Me.SCAR
    DoCmd.Close acForm, Me.Name
End Sub

For the 2st scenario:
Code for the Back button:

Private Sub btnback2_Click()
    'Need to catch the error 2046 on first record. 'RecordsGoToPrevious isn't available now'.
    RunCommand acCmdRecordsGoToPrevious
End Sub

PS. For your nickname only: So long lonesome