Access 2000-2007 conversion - VB failing?

  • Thread starter Thread starter Booleanboy
  • Start date Start date
B

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
 
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.
 
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!
 
Back
Top