Form Update

[hansaplast]

Hi,

I want to update a record before I move to a next one.
I have an unbound field in my form where u can type a record number. The form then displays this record and a user can make changes. Below is the function I use to move to another record. This works ok. I'm using an ODBC-database on a remote server. When the server reports an error (on an update for example) the error is truncated by Access. I found this strange so I thought that perhaps I'm not updating my records correctly.

Perhaps someone knows a better way to do this. Also let me know, if you know why the errorstring is truncated by Access. I've already posted a message regarding this problem but got no answers ( thread181-217299 ).


Private Sub colnum_info_AfterUpdate()
On Error GoTo Err_colnum_info_AfterUpdate
Dim rs As Object

Set rs = Me.Recordset.Clone
If Not IsNull(Me.colnum_info) Then
rs.FindFirst "[colnum] = " & str(Me.colnum_info)
End If
Me.Bookmark = rs.Bookmark

Exit_colnum_info_AfterUpdate:
Exit Sub

Err_colnum_info_AfterUpdate:
MsgBox Err.Description
Resume Exit_colnum_info_AfterUpdate

End Sub


Thanks

 

[papparuff]

I took your code and pasted it to a form and tried to run it. I got a mismatch error at

rs.FindFirst "[colnum] = " & str(Me.colnum_info)

I changed your code to

rs.FindFirst "[colnum] = '" & Me.colnum_info & "'"

and it worked fine.

Here is another way of doing the find. When you Dim rs as Object you get late binding and though it has miniscule effect here, the program runs slower. Also you do not have the luxury of intellisense with late binding. The sample code below uses early binding. (You need a reference to Microsoft DAO x.x Object Library)

Dim rst As DAO.Recordset

If Not IsNull(colnum_info) Then
Set rst = Me.RecordsetClone

'Find the record that matches the control
With rst
.FindFirst "[colnum] = '" & colnum_info& "'"

If .NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If

.Close
End With
Set rst = Nothing
End If

John Ruff - The Eternal Optimist

 

[hansaplast]

Thanks John,

You are right about the late binding. And I've changed it (Dim rs as DAO.Recordset). But the code you gave is not different form the one I use. You still clone the recordset, after wich you update the with bookmark. Which tells me that what I coded is correct (I guess...).

But.. I still have the error problem. Why does Access truncates messages received from ODBC. And how can I resolve this. I searched like whole the internet and even tried running the same code on Access XP. I searched at microsoft but even they don't seem to know Access is doing this. It seems that I am the only one who has this problem :-( I will stop whining now .

If you like I can send you the whole code and setup my MySQL-Server to the outside world and you can see by youreself. Just e-mail me at hansa.plast@hetnet.nl and I will send you the code.