Access 2000-2007 conversion - VB failing?

[Booleanboy]

I have a relatively simple contact database written with MS Access 2000 and
works fine running under Win XP but in Access 2007 running under Windows 7 I
have some problems with one of the forms.

I use a Combo Box (Combo206) at the top of a form to choose the 'Name' field
from a table so that the record can be displayed in the form body. When run
under Access 2007 the list of 'Name' fields is displayed but the form does
not update with the contents of the related record. I've tried converting the
entire file to the new Access 2007 format but this didn't resolve the problem.

How can I fix this? I suspect the problem is with the VB code. Here's the
code which works under A2000:

Sub Combo206_AfterUpdate()
On Error GoTo Err_Combo206_AfterUpdate
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo206]
Me.Bookmark = Me.RecordsetClone.Bookmark

Exit_Combo206_AfterUpdate:
Exit Sub

Err_Combo206_AfterUpdate:
MsgBox Err.Description
Resume Exit_Combo206_AfterUpdate
End Sub

Additionally the code that used to set focus to the Combo Box now doesn't:

Private Sub Form_Open(Cancel As Integer)
Combo206.SetFocus
End Sub

 

[AccessVandal via AccessMonster.com]

Try something else. You need to check the DAO 3.6 reference in the VBA if
you're using DAO.
Anyway, your code aren't right.

Booleanboy wrote:
Sub Combo206_AfterUpdate()
On Error GoTo Err_Combo206_AfterUpdate
Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
' Find the record that matches the control.
rs.FindFirst "[ID] = " & Me![Combo206] 'Assuming number datatype
if rs.nomatch then
msgbox "No record matching FindFirst"
else
Me.Bookmark = rs.Bookmark
end if
set rs = Nothing 'destroy
Exit_Combo206_AfterUpdate:
Exit Sub

Err_Combo206_AfterUpdate:
MsgBox Err.Description & " - Error No: " & Err.Number 'please include
error number
Resume Exit_Combo206_AfterUpdate
End Sub

Additionally the code that used to set focus to the Combo Box now doesn't:

If it doesn't work, do you have any others events that might trigger it? or
cause the setfocus to another control? The error may cancel the setfocus.

Private Sub Form_Open(Cancel As Integer)
Me.Combo206.SetFocus
End Sub

 

[AccessVandal via AccessMonster.com]

Typo:

Sorry, recordsetclone does not work with nomatch.

if rs.EOF then 'change it to rs.EOF
msgbox "No record matching FindFirst"
else
Me.Bookmark = rs.Bookmark
end if

Booleanboy wrote:
snip..

Or like this.....

Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
' Find the record that matches the control.
With rs
.FindFirst "[ID] = " & Me![Combo206] 'Assuming number datatype
if .EOF then
msgbox "No record matching FindFirst"
else
Me.Bookmark = .Bookmark
end if
End With
set rs = Nothing 'destroy

 

[Booleanboy]

Thanks for assisting me - it is much appreciated.

Forgive me, I really don't have much experience here but if I substitute
your code:

Sub Combo206_AfterUpdate()
Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
' Find the record that matches the control.
With rs
..FindFirst "[ID] = " & Me![Combo206] 'Assuming number datatype
if .EOF then
msgbox "No record matching FindFirst"
else
Me.Bookmark = .Bookmark
end if
End With
set rs = Nothing 'destroy
End Sub

for my original Sub Combo206_AfterUpdate() code it doesn't work here. Am I
doing this correctly?

Just to be clear, the [ID] field is a unique numeric which is the Primary
Key for the table. The value typed into or selected from a list in the
ComboBox is the text content of the [Name] field associated with [ID].

Thanks again.

 

[AccessVandal via AccessMonster.com]

Sorry, was on holiday.

So what did not work. Can you tell us which line?

I'm not certian that ".EOF" work with recordsetclone. Your combobox
"Combo206" must be unbound to the form's recordsource or the table.

You might want to chnage it to

Sub Combo206_AfterUpdate()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = currentdb
set rs = db.openRecordset("YourTableName")
' Find the record that matches the control.
With rs
.FindFirst "[ID] = " & Me![Combo206] 'Assuming number datatype
if .NoMatch then
msgbox "No record matching FindFirst"
else
Me.Bookmark = .Bookmark
end if
End With
rs.close
db.close
set rs = Nothing 'destroy
set db = Nothing
End Sub

 

[Linq Adams via AccessMonster.com]

Does any code on any form run? Code does not run in 2007 unless your database
resides in a folder that has been declared a “trusted†location.

To trust your folder, click:

Office Button (top left)
Access Options (bottom of dialog)
Trust Center (left)
Trust Center Settings (button)
Trusted Locations (left)
Add new location (button)

 

[Booleanboy]

Thanks - this was exactly the problem. In my defence, the default colour
scheme for
Access 2007 is so pale that the notification message wasn't very visible.

I knew a warning was displayed the first time an 'old' file was run, I
didn't appreciate that the file was subsequently locked out without
displaying the message. I've modified the Trust Centre settings now and
all seems to work OK.

Thanks again - I'd been puzzling over this for a couple of weeks!