vba in access 2007

A

agodfried

I am previewing access 2007 and having some difficulty in vba. When I try to
use the on-line help, none of the topics are available so it is difficult to
proceed. Are the help documents completed and if so, how can I fix the help
on my computer? Also, which DAO version is the default for access 2007?
 
A

Allen Browne

In the bottom-right-corner of the Help window, you see an indication of
whether you are using "off-line help" or you are "connected to Office
on-line". Click those words to change you option and get the more up-to-date
version of on-line help.

As with any beta, they don't waste time writing all the help until
everything else has settled down. If you have done any database development
and written documentation or help, you can probably understand that. So
working with a beta generally involves quite a bit of experimentation.

You can discover the DAO file that is in use by opening the Immediate Window
(Ctrl+G) and entering:
? References("DAO").FullPath
If you are using an MDB format, it will point to the same DAO as A2003 (i.e.
3.6.)

If you are using the new ACCDB file format, this will point to AceDAO.DLL.
Under Tools | References, this is labelled:
Microsoft Office 2007 Access database engine Objects.
 
A

agodfried

Thanks for the info. I am using the online version of help so I quess I'll
have to suffer a little while longer. It appears that there are some changes
in AceDAO.DLL that are yet undocumented. For example, there is no
recordset.findfirst and .findnext etc. like there was in DAO3.6.
 
A

Allen Browne

Huh? I'm still using FindFirst on a DAO recordset in an accdb. If you could
not get it to work, check that you opened recordset of type dbOpenDynaset,
not dbOpenTable. Or if you are working with a form, use its RecordsetClone
rather than its Recordset.

The Object Browser (press F2 in the code window) is useful for investigating
what properties and methods exist, what they belong to, and how they compare
to previous versions.

Some of the new things I'm still waiting to see documentation on, such as
the Field2 object. Of course, some may not be there in the final release.

Others you can figure out for yourself, such as the new field type constants
for the complex data types, which I've included here:
http://allenbrowne.com/ser-49.html

HTH.
 
A

agodfried

You're correct. When I declared the recordset explicitly using
dao.recordset, the code compiled propertly and findfirst is valid. I had not
explicitly called out dao. in the declaration. I am getting a strange
compile error however in the subroutine when I refer to control on a form
passed as an argument. In the definition of the subroutine, I pass a
parameter frm as form (sub searchForm frm as Form, ...). Then I refer to a
control on the form as frm!KeyField. The compiler is raising an error, but
this makes no sense since the compiler can't know at this point what form is
being referred to in the subroutine declaration. In ac2003, this same code
does not raise a compile error.
 
A

agodfried

Nope. I tried that and it still raises the error "Method or data member not
found". The actual code is:

Sub SearchForm(frm As Form, strSearchCriteria As String, Optional boolNext
As Boolean = False)
Dim rstClone As DAO.Recordset
Dim varMark As Variant
Dim lngID As Long
Dim varSaveMark As Variant
Dim strSQL As String

On Error GoTo HandleErr

If RTrim(strSearchCriteria) <> "" Then
strSearchCriteria = Left(strSearchCriteria, Len(strSearchCriteria) -
5)
If Not frm.NewRecord Then
varSaveMark = frm.Bookmark
lngID = frm!ID
End If

Set rstClone = frm.RecordsetClone
rstClone.FindFirst strSearchCriteria

If rstClone.EOF Then
MsgBox "Search found no matching records"
Exit Sub
End If

If boolNext Then
rstClone.FindFirst "KeyField = " & frm!KeyField
If Not rstClone.EOF Then
rstClone.FindNext strSearchCriteria
End If
End If

If Not rstClone.NoMatch Then
frm.Bookmark = rstClone.Bookmark
Set rstClone = Nothing
Else
If Not IsNull(varSaveMark) Then
frm.Bookmark = varSaveMark
End If
MsgBox "Search found no matching records"
End If
End If

ExitHere:
Exit Sub

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block
of code.
' Automatic error handler last updated at 02-08-2003 15:13:05
'ErrorHandler:$$D=02-08-2003 'ErrorHandler:$$T=15:13:05
HandleErr:
LogError Err, "UtilityCode.SearchForm"
' End Error handling block.
End Sub
 
A

Allen Browne

Presumably the line that errors is this one:
rstClone.FindFirst "KeyField = " & frm!KeyField

You could try:
Debug.Print frm.Name
Debug.Print frm.KeyField.Name
etc until you are able to identify what's going on.
 
A

agodfried

I find it odd that the compiler is trying to resolve a control name on a
form. This seems like a glitch in the compiler.
 
A

agodfried

I found why I was getting the compile error. the bad line of code was:

rstClone.Find "KeyField = " & frm!KeyField

The compiler was actually complaining about rstClone.Find which doesn't work
for a dao recordset. The compiler however was highlighting the !KeyField
text which really confused the issue.

When I changed the line to:
rstClone.FindFirst "KeyField = " & frm!KeyField

it compiled properly and all is ok. Thanks for your help.
 
A

Allen Browne

Good.

Yes, the compiler seems to work backwards, and often highlights a later item
on the line, not the one that actually has the issue.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top