Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: My Form Is Really Slow - Please Help : )

    Hi all,

    I have a Team Change System that utilizes the dlookup function to populate a form whereby users can update an employees record when necessary. The form works perfectly and the only issue is that it runs slowly. The user would select an employee name and then the code pulls out results from a select query, this can take up to 30 seconds to populate. Whilst i am happy with what the form is doing i would be over ther moon if it would just speed up a bit. Being reasonably intermediate in my knowledge of VBA and beginner in SQL please bear this in mind when posting any replies (otherwise they will be useless to me ).

    As always i would be eternally grateful of any help or advice.

    Regards
    EddiesVoicebox

    CODE POSTED BELOW

    Code:
    Private Sub CSANAME_AfterUpdate()
    Me.Refresh
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QRY_CLEAR_UPDATED_INFORMATION_TABLE"
    DoCmd.OpenQuery "QRY_APPEND_RECORD_TO_BE_UPDATED"
    DoCmd.SetWarnings True
    If IsNull(Me!CSANAME) Then
    Exit Sub
    Else
    Me!Payroll = DLookup("[Payroll Number]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!Surname = DLookup("[Surname]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!FirstName = DLookup("[FirstName]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!Grade = DLookup("[Grade/JF]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!FTPT = DLookup("[FT/PT]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!Hours = DLookup("[Contract Hours]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!ShiftType = DLookup("[Type Of Shift]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!CODISLogon = DLookup("[CODIS Logon]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!PhoneLogon = DLookup("[Phone Logon]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!Notes = DLookup("[Notes]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!Sex = DLookup("[sex]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!QMaxID = DLookup("[Q-Max ID]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!Location = DLookup("[Location]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!Area = DLookup("[Area]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!Dept = DLookup("[Dept]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!SubDept = DLookup("[Sub Dept]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!ContractType = DLookup("[Contract Type]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    Me!IncentiveScheme = DLookup("[Incentive Scheme type]", "QRY_SELECT_DETAILS_FOR_FORM(UPDATE)")
    End If
    
    If (Me!SubDept) = "Sales" Or (Me!SubDept) = "Retainers" Then
    Me.DATEFORCHANGE = DLookup("[Sales]", "QRY_DATES")
    Else
    Me.DATEFORCHANGE = DLookup("[Servicing]", "QRY_DATES")
    End If
    
    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry, there is no easy way round this - don't use dlookup. its so incredibly slooooooooow.

    Usually you can design out dlookup / domain functions with a bit of careful query design.

    Looking, breifly, at your code it seems you have a query which retrieves the info you require, yet you do a dlookup multiple times on seemingly the same criteria.

    you could also do a simple query on your data in VBA and then transfer the values to your controls.

    DLookups can be a useful tool, but like many useful tools its best when used appropriately, and often sparingly.

    perhaps you could give a simple expalnation of what you are trying to do in english.

  3. #3
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    Hi Healdem

    Thanks for the quick reply on this.

    Basically the situation is as follows

    I have a Staff Table holding information on employees working for the company. This feeds all the forms in my database.

    What i want is for the user to select an employee name and for all that employees details to pop up in the fields on screen. Im sure this is probably a fairly simple routine thing to do but i only know the dlookup method.

    Hope this sheds some light for you.

    thanks again

    EV

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Try using a recordset to pull the data desired ... Ex:
    Code:
    Dim MyRecSet As ADODB.Recordset
    
        Set MyRecSet = New ADODB.Recordset
        
        MyRecSet.CursorType = adOpenForwardOnly
        MyRecSet.LockType = adLockOptimistic
        MyRecSet.CursorLocation = adUseClient
    
        SQLString = "SELECT * FROM [QRY_SELECT_DETAILS_FOR_FORM(UPDATE)];"
        MyRecSet.Open SQLString, currentproject.connection
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            ' Payroll
            Payroll.Value = MyRecSet.Fields(XXX).Value
            ....
        End If
        MyRecSet.Close
    
        Set Myrecset=Nothing
    Mind: This is ADO code ... You'll have to assign your values using the Ordinal position of the column in your table/query (ordinals ALWAYS start a 0). You'll also need to handle potential Null assignments to (easy: just tack on a ' & "" ' to the end of any string assignment)

    Enjoy!
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The simplest way is to use a combobox. After you drag it onto your form in design view it goes into the CB Wizard and asks how you want to use it. The first (and default) choice is something like "I want the combobox to look up the values in a table." Select this and finish the Wizard's run for the combobox. This should do the trick.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The 'smartest' solution as Missingling suggests is a combobox. Place some code in the forms "on change" event, that looks for that employee in the staff table.

    populate your combobox with employeeID ( employee names. Bear in mind your users probably want to refer to employees by name. eg they would want to type in "missingling" instead of an employee id. It means ypou may have to redesign your combo box to be non bound and extract the employeeid using the combos' items selected collection.

    eg select * from DT_Staff where Emp_ID=cmbSelectEmployee.value
    'then populate the controls as required (assuming the employee record has been found

    for optimum performance create a recordset in the forms "on open" event and close in the forms "on close" event.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •