• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

What's faster than DLookup in access 2003 form?

Hi all.

I am using a DLookup to insert a field in a table, but it has proven to be a bit slow, is there a faster way to accomplish this?

The code in question is:

!Print_Media_ID = DLookup("[PrintMediaID]", "Print Media IDs and GPO Entity Codes", "[CompanyNumber] = '" & rs("Company Number") & "'  And [GPOName] = '" & Me.cmbGPO_Name & "'")
Dim stDocName, stLinkCriteria As String
 
stDocName = "PleaseWait"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset
 
Set rs = CurrentDb.OpenRecordset("Select [Company Number],[Account Name],[Address1],[Address2],[Address3],[City],[State],[Zip Code],[Phone],[Fax],[GLN Number],[Top Parent Number],[Top Parent Name] from dbo_All_Rosters where [Top Parent Number]='" & DLookup("[Top Parent Number]", "Entity Codes", "[Company Number] = '" & Me.Assignment_Entity & "' And [GPO Name] = '" & Me.cmbGPO_Name & "'") & "'")
Set rs1 = CurrentDb.OpenRecordset("tblContractAssignment_Header", dbOpenDynaset, dbSeeChanges)
Set rs2 = CurrentDb.OpenRecordset("tblContractAssignment_Detail", dbOpenDynaset, dbSeeChanges)
 
Dim j, DistID
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'If the Assignment Type was for an Entity Code (1) then insert all of the members of it's family
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
If Me.cmbAssignment_Type.Value = "1" Then
 
    With rs1
    
        .AddNew
        !Contract_Assignment_Number = Me.txtContract_Assignment_Number
        !CAssignment_Type = Me.cmbAssignment_Type.Value
        !CAssignment_Entity = Me.Assignment_Entity
        !Insert_Type = "1"
        !Contract_Type = Me.cmbContract_Type
        !Contract_Subtype = DLookup("[GPO_Contract_Type]", "tblPM_Contracts-All", "[PM_Contract_ID] = '" & Me.cmbContract & "'")
        !GPO_Contract_ID = DLookup("[GPO Contract ID]", "tblPM_Contracts-All", "[PM_Contract_ID] = '" & Me.cmbContract & "'")
        !PM_Contract_ID = Me.cmbContract
        !Contract_Product_Line = Me.cmbGPO_Product_Line
        !Status = "Active"
        !Create_Username = GetUserName()
        !Create_Date = Now()
        !Transfer_Status = "0"
        .Update
    
    End With
    
    rs1.Close
       
    If rs.EOF Then
    
        MsgBox "No Records"
        Exit Sub
    
    End If
    
    rs.MoveFirst
    Do Until rs.EOF
    
    With rs2
        
        .AddNew
        !Contract_Assignment_Number = Me.txtContract_Assignment_Number
        !Facility_ID = rs("Company Number")
        !Name = rs("Account Name")
        !Address1 = rs("Address1")
        !Address2 = rs("Address2")
        !Address3 = rs("Address3")
        !City = rs("City")
        !State = rs("State")
        !Zip_Code = rs("Zip Code")
        !Phone = rs("Phone")
        !Fax = rs("Fax")
        !GLN = rs("GLN Number")
        !Top_Parent_Number = rs("Top Parent Number")
        !Top_Parent_Name = rs("Top Parent Name")
        !Print_Media_ID = DLookup("[PrintMediaID]", "Print Media IDs and GPO Entity Codes", "[CompanyNumber] = '" & rs("Company Number") & "'  And [GPOName] = '" & Me.cmbGPO_Name & "'")
        !Excel_Status = "0"
        !Status = "Active"
        !Create_Username = GetUserName()
        !Create_Date = Now()
        .Update
        
      End With
      
      rs.MoveNext
      
      Loop
      
      rs.Close
      
      rs2.Close

Open in new window

0
printmedia
Asked:
printmedia
  • 4
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can open a Recordset ... for example, since you use the tblPM_Contracts-ALL table twice, you could simple open a Recordset:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT GPO_Contract_Type, GPO_Contract_ID FROM tblPM_Contracts-ALL WHERE PM_Contract_ID='" & Me.cmbContract & "'"

!Contract_Subtype = rst("GPO_Contract_Type")
!GPO_Contract_ID = rst("[GPO Contract ID]")

       
0
 
printmediaAuthor Commented:
Thanks for the reply.

But the data is from a different source (table: "Print Media IDs and GPO Entity Codes") referencing line 70 of the code snippet. How would I include that in the script instead of line 70?

Do I have to create a new Dim rs4?
0
 
printmediaAuthor Commented:
I replaced line 70 with the code snippet below, but it still takes just as long as using DLookup.



 !Print_Media_ID = CurrentDb.OpenRecordset("Select [PrintMediaID] from [Print Media IDs and GPO Entity Codes] where [CompanyNumber]='" & rs("Company Number") & "' And [GPOName] = '" & Me.cmbGPO_Name & "'")

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
printmediaAuthor Commented:
Actually it didn't work....sorry
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<But the data is from a different source>

Then you'd use syntax similar to what I showed, expect you'd use the Table and Field names you need. Check online help for basic information on opening recordsets.

Also, you can't use a recordset in the manner you showed in your last post. You must open the REcordset, then refer to it as I showed earlier.

And, if all you're doing is getting that single value, then nothing you do will be much faster ... if this is taking an extremely long time, then I'd be looking into networking issues (assuming this is a split database), or poor database structure.
0
 
printmediaAuthor Commented:
If I open the recordset outside of the loop how will it know to apply the criteria for each individual record as it walks through the loop.

How does it know to apply the CompanyNumber currently in rs, to other recordset (rs4)?
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now