Error 3070 (1 Viewer)

lmcc007

Registered User.
Local time
Today, 03:39
Joined
Nov 10, 2007
Messages
635
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!
 

Attachments

  • CoName.zip
    27.9 KB · Views: 241

vbaInet

AWF VIP
Local time
Today, 08:39
Joined
Jan 22, 2010
Messages
26,374
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

bit cruncher
Local time
Today, 01:39
Joined
Mar 17, 2004
Messages
8,178
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

Registered User.
Local time
Today, 03:39
Joined
Nov 10, 2007
Messages
635
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

Registered User.
Local time
Today, 03:39
Joined
Nov 10, 2007
Messages
635
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

AWF VIP
Local time
Today, 08:39
Joined
Jan 22, 2010
Messages
26,374
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

Smeghead
Local time
Today, 01:39
Joined
Jan 12, 2001
Messages
32,059
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.
 

Attachments

  • CoName_revBL.mdb
    332 KB · Views: 271

lmcc007

Registered User.
Local time
Today, 03:39
Joined
Nov 10, 2007
Messages
635
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

Smeghead
Local time
Today, 01:39
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 03:39
Joined
Nov 10, 2007
Messages
635
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!
 

Attachments

  • CoName_revBL (4).zip
    23 KB · Views: 211

Users who are viewing this thread

Top Bottom