Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-02-2010, 05:43 PM   #1
lmcc007
Newly Registered User
 
Join Date: Nov 2007
Posts: 635
Thanks: 0
Thanked 0 Times in 0 Posts
lmcc007 is on a distinguished road
Error 3070

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!
Attached Files
File Type: zip CoName.zip (27.9 KB, 149 views)

lmcc007 is offline   Reply With Quote
Old 12-02-2010, 08:11 PM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,420 Times in 2,386 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Error 3070

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.
vbaInet is offline   Reply With Quote
Old 12-02-2010, 08:18 PM   #3
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,590
Thanks: 10
Thanked 1,251 Times in 1,190 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
The form frmCompany is based on the table tblCompany, which does not contain a field called CompanyNameTitle. This causes your .FindFirst to fail.
Cheers,

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 12-02-2010, 08:33 PM   #4
lmcc007
Newly Registered User
 
Join Date: Nov 2007
Posts: 635
Thanks: 0
Thanked 0 Times in 0 Posts
lmcc007 is on a distinguished road
Re: Error 3070

Quote:
Originally Posted by lagbolt View Post
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 is offline   Reply With Quote
Old 12-02-2010, 08:35 PM   #5
lmcc007
Newly Registered User
 
Join Date: Nov 2007
Posts: 635
Thanks: 0
Thanked 0 Times in 0 Posts
lmcc007 is on a distinguished road
Re: Error 3070

Quote:
Originally Posted by vbaInet View Post
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.
lmcc007 is offline   Reply With Quote
Old 12-02-2010, 08:44 PM   #6
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,420 Times in 2,386 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Error 3070

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
vbaInet is offline   Reply With Quote
Old 12-02-2010, 09:11 PM   #7
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,807 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Error 3070

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.
Attached Files
File Type: mdb CoName_revBL.mdb (332.0 KB, 164 views)

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-03-2010, 09:06 AM   #8
lmcc007
Newly Registered User
 
Join Date: Nov 2007
Posts: 635
Thanks: 0
Thanked 0 Times in 0 Posts
lmcc007 is on a distinguished road
Re: Error 3070

Quote:
Originally Posted by boblarson View Post
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!
lmcc007 is offline   Reply With Quote
Old 12-03-2010, 10:38 AM   #9
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,807 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Error 3070

Quote:
Originally Posted by lmcc007 View Post
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?

Quote:
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 12-03-2010, 03:31 PM   #10
lmcc007
Newly Registered User
 
Join Date: Nov 2007
Posts: 635
Thanks: 0
Thanked 0 Times in 0 Posts
lmcc007 is on a distinguished road
Re: Error 3070

Quote:
Originally Posted by boblarson View Post
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!
Attached Files
File Type: zip CoName_revBL (4).zip (23.0 KB, 117 views)

lmcc007 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
A question about the error message "Run-time error '3061' Too few parameters, Expecte dariyoosh Modules & VBA 7 03-12-2010 06:26 AM
Error 3070 when using a form to specify criteria accessaspire219 Forms 1 05-13-2009 09:36 AM
Run time error 3075 - syntax error (missing operator) in query expression puskardas Modules & VBA 8 06-30-2008 07:35 PM
RunTime error 3070 divingwelshbird Modules & VBA 4 11-27-2006 01:31 AM
Need help with Error 3070 skilche1 Reports 6 08-19-2003 04:32 PM




All times are GMT -8. The time now is 10:56 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World