asked on

DLookup Error in Access 2003 Form

Hi all.

I have an access form where the user enters an ID and it pulls information for that ID from various sources and inserts it into a table. I get the following error when it gets to the DLookup:

Runtime error 2001: You canceled the previous operation and when I debug it, it highlights the line:

!Print_Media_ID = DLookup("[PrintMediaID]", "Print Media IDs and GPO Entity Codes", "[CompanyNumber] = '" & Me.Assignment_Entity & "' And [GPO Name] = '" & Me.cmbGPO_Name & "'")

There are situations where the DLookup will not find a record, is this why it gives me that error? How do I tell it that if it doesn't find the PrintMediaID, then leave it as null and continue with the code (i.e. !Excel_Status = "0")

Any ideas? Thanks in advance!

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)  
  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] = '" & Me.Assignment_Entity & "'  And [GPO Name] = '" & 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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

Is the Print_Media_ID field in tblContractAssignment_Detail listed as Required. It should not be so it it can be Null.

printmedia

ASKER

Ah, I misspelled one of the words in the criteria field...my mistake thanks mbizup!

mbizup

> I misspelled one of the words in the criteria field

I didn't think of a typo :-)

But I guess this helped out:

<more likely that the DLookup itself is failing.>

> Runtime error 2001: You canceled the previous operation

This error often pops up in Domain Aggregate functions (DLookup, DSum, etc) and also OpenReport /OpenForm statements.

The culprit is almost invariably the criteria.

I'm glad you're sorted.