Error 3070

lmcc007 · Dec 3, 2010

I have an unbound search field on my form that I am using to search for a company name, but I keep getting the following error message:

An error was encountered

Description: The Microsoft Office Access database engine does not recognize 'CompanyNameTitle' as a valid field name or expression.

Error Number: 3070

All I am trying to do is search for a company name and if found go to that record.

I have attached the database.

Thanks for your help!

vbaInet · Dec 3, 2010

Without opening the db I think the error is self explanatory. That field names doesn't exist in your form's record source. Check the spelling.

Or you're calling the field name in the wrong context. Explain your approach.

MarkK · Dec 3, 2010

The form frmCompany is based on the table tblCompany, which does not contain a field called CompanyNameTitle. This causes your .FindFirst to fail.
Cheers,

lmcc007 · Dec 3, 2010

lagbolt said:
The form frmCompany is based on the table tblCompany, which does not contain a field called CompanyNameTitle. This causes your .FindFirst to fail.
Cheers,

Thanks lagbolt.

What should I do different to get the search to work?

lmcc007 · Dec 3, 2010

vbaInet said:
Without opening the db I think the error is self explanatory. That field names doesn't exist in your form's record source. Check the spelling.

Or you're calling the field name in the wrong context. Explain your approach.

Oh, I copied that code and was trying to get it to work here.

I am just trying to search for a company name and if found go the record.

vbaInet · Dec 3, 2010

I haven't looked at your db so I don't know what approach you're using but you can probably just use FindFirst:

Code:

    Dim rs As DAO.Recordset
    
    Set rs = Me.RecordsetClone
    
    rs.FindFirst "[FieldName] Like '*" & Me.txtSearchBox & "*'"
    
    If Not rs.NoMatch Then
        Me.Bookmark = rs.Bookmark
    End If

boblarson · Dec 3, 2010

You need to search on CompanyID and include that in your combo. Because it is a subform which company name is on, the companyID links the two together.

See my revised version - take a look at the row source behind the combo for finding the record as well as the code.

lmcc007 · Dec 3, 2010

boblarson said:
You need to search on CompanyID and include that in your combo. Because it is a subform which company name is on, the companyID links the two together.

See my revised version - take a look at the row source behind the combo for finding the record as well as the code.

Thanks Boblarson,

Got it--works great!!

Now, I am getting an error when I do the following:

1. Click the icon to add a company name.
2. Select a name from the list
3. Then I get the following error: Index or primary key cannot contain a Null value.

What did I miss?

By the way, since this is my first junction table, did I set it up correctly or should I have done it differently?

Thanks for all your help!

boblarson · Dec 3, 2010

lmcc007 said:
Now, I am getting an error when I do the following:

1. Click the icon to add a company name.
2. Select a name from the list
3. Then I get the following error: Index or primary key cannot contain a Null value.

What did I miss?

As you didn't have this in the version I downloaded and subsequently uploaded, I'm not sure how you have the code and stuff set up. Want to upload a latest copy?

By the way, since this is my first junction table, did I set it up correctly or should I have done it differently?

As far as I can see, it looks good.

lmcc007 · Dec 3, 2010

boblarson said:
As you didn't have this in the version I downloaded and subsequently uploaded, I'm not sure how you have the code and stuff set up. Want to upload a latest copy?

Here is the db.

Thanks!

Error 3070

lmcc007

Registered User.

Attachments

vbaInet

AWF VIP

MarkK

bit cruncher

lmcc007

Registered User.

lmcc007

Registered User.

vbaInet

AWF VIP

boblarson

Smeghead

Attachments

lmcc007

Registered User.

boblarson

Smeghead

lmcc007

Registered User.

Attachments

Similar threads

Users who are viewing this thread