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

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.

mbizupCommented:
printmedia,

<There are situations where the DLookup will not find a record, is this why it gives me that error?>
Does Print_Media_ID allow nulls?  (check the field propertied in the table's design).
 
If it does allow nulls, this should work regardless of whether DLookup finds a matching record.


However, I think it is more likely that the DLookup itself is failing.

Are both Company Number and GPO Name text fields?

If Compny Number is numeric, you need to drop the single quote delimiters:

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

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
GRayLCommented:
Is the Print_Media_ID field in tblContractAssignment_Detail listed as Required.  It should not be so it it can be Null.
0
printmediaAuthor Commented:
Ah, I misspelled one of the words in the criteria field...my mistake thanks mbizup!
0
mbizupCommented:
> 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.

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.