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

printmediaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.