MS Access code question?

Question

I am trying to use the FindFirst method in the standard module,referring to two open forms. Can I do that, and how do I reference the controls?

This is what I have:

Dim rs As Object
Set rs = Form_frmCompanyInfo2.Recordset.Clone
rs.FindFirst ("CompanyID=" & Form_frmqryCompanyDBA![CompanyID])
If Not rs.EOF Then Form_frmCompanyInfo2.Bookmark = rs.Bookmark

This is what I get:

Run-time error '3070':

The Microsoft Jet database does not recognize 'CompanyID' as a valid field name or expression.

Yahoo Answers is cutting off some of my lines. Can anyone still make a suggestion based on the code that is showing up?

Answer 1

Try putting a space on either side of the equals sign and encasing the column name in brackets. The brackets shouldn't be necessary since you have no spaces in the column name but they can't hurt and are worth a try

The string you currently have is
"CompanyID=Form_.....
add spaces and brackets so it appears as
"[CompanyID] = Form_.....

Next FindFirst is expecting a string version of a where clause without the WHERE.
Rather than concatenating the Table![Cloumn Name] all in one command line try assigning the column data to a string variable and building your creiteria string before using it within the findfirst method

dim strCriteria as string

'Company ID note I think you will need to provide the value through a parameter
strCriteria = "[CompanyID] = " & "200"

If your ID is not an integer but text you will also have to add single quotes to the parameter
strCriteria = "[CompanyID] = '" & "200" &"'"

rs.FindFirst(strCriteria )


Finally I would rather see you declare rs as a DAO recordset rather than an object

dim rs as DAO.recordset
dim strCriteria as string

Set rs = Form_frmCompanyInfo2.Recordset...
strCriteria = "[CompanyID] = " & "200"

rs.FindFirst(strCriteria)

If Not rs.EOF Then Form_frmCompanyInfo2.Bookmark = rs.Bookmark

Answer 2

Make sure that CompanyID is the same in your code and in your table.

Nelson Bittencourt
See my site:
http://www1.webng.com/nbittencourt/index_e.htm

Answer 3

go to tek-tips.com

it is a free forum. they have all kinds of forums including one for MS Access.

it is an awesome resource!

Answer 4

try to define companyID before that line!