Question Item not found in this collection error? (1 Viewer)

[WildmanUK]

Hi,

I have form with text boxes and a button on one form, the code takes the value of the text boxes and creates a new record in a table from them. I have then replicated the code changing the name of the text boxes etc on another form and i'm getting "Item not found in this collection" error.

Code that works

Dim dbControl As DAO.Database
   Dim rstContact As DAO.Recordset

   Set dbControl = CurrentDb
   Set rstContact = dbControl.OpenRecordset("Customer Contacts")

   rstContact.AddNew
   rstContact("customerCode").Value = Me.Customer_Code.Value
   rstContact("contactName").Value = Me.txtNewName.Value
   rstContact("Email").Value = Me.textNewEmail.Value
   rstContact("telephone").Value = Me.txtNewTel.Value
   rstContact("fax").Value = Me.txtNewFax.Value
   rstContact("locationID").Value = Me.ComboLocation.Value
   rstContact.Update
   rstContact.Close

Code that causes the error

Line that it says causes the error in bold.

Dim searchNumber As Integer
 searchNumber = DFirst("[SearchCode]", "[NewSearchCode]") + 1
   
Dim dbControl As DAO.Database
   Dim rstNewSearch As DAO.Recordset
    
   Set dbControl = CurrentDb
   Set rstNewSearch = dbControl.OpenRecordset("Searches")
   
   

   rstNewSearch.AddNew
  [B] rstNewSearch("CustomerCode").Value = Me.txtCompanyCode.Value[/B]
   rstNewSearch("ContactName").Value = Me.txtContactName.Value
   rstNewSearch("ContactNumber").Value = Me.txtContactNumber.Value
   rstNewSearch("StartDate").Value = Me.txtStartDate.Value
   rstNewSearch("EndDate").Value = Me.txtEndDate.Value
   rstNewSearch("CustomerRef").Value = ""
   rstNewSearch("Closed").Value = ""
   rstNewSearch("Notes").Value = ""
   rstNewSearch("InvoiceDate").Value = ""
   rstNewSearch("SearchCode").Value = CStr(searchNumber)
   rstNewSearch("AddressLine").Value = ""
   rstNewSearch("Coordinates").Value = ""
   rstNewSearch.Update
   rstNewSearch.Close

The thing is before it was working, I just had not worked out out to make it so the SearchCode was one higher, but now even if I only insert just the customerCode it throws the error up..

I know the way I have named the fields in the first example is different so customerCode on one and CustomerCode the 2nd time around but thats just how they are in the table will tidy that up later.

Any ideas?

Thanks.

WildmanUK

 

[spikepl]

In the table/query Searches you do not have a field called customerCode. That's what Access says.

 

[WildmanUK]

Its amazing how you can look at something for ages and not see the problem then its clear once someone says something!

That was the problem the field in Searches is CompanyCode not CustomerCode.

Thanks!

I would have been here for hours and totally not noticed that.. :S

WildmanUK

 

[spikepl]

The thing is that a number of Access messages are nonsensical. But most do have sense, so one only has to relax and read and reread the error message.