How to update "changed date" field on bound form?

[bulrush]

A2003 on Win XP.

I have a bound form. On the table bound to the form, I have 2 fields: ChangedDate which stores the date the record was changed, and ChangeUser which stores the network username of the person who changed the record.

In my Form_BeforeUpdate event I put this code:

On Error GoTo MyError

'DoCmd.RunCommand acCmdSaveRecord
txtUpdateDate.Enabled = True
txtUpdateUser.Enabled = True

txtUpdateDate = Now()
txtUpdateDate.Enabled = False

txtUpdateUser = gUsername
txtUpdateUser.Enabled = False

Exit Sub

MyError:
If (Err.Number <> 2046) And (Err.Number > 0) Then
    Call DispError("Form_Dirty")
    Stop
    Resume Next
    'Exit Sub
End If

But I'm getting an Unknown Error each time I try to disable the field (which has the focus.)

Am I doing things right? Is the code in the correct event? How do I keep the 2 fields disabled after I update their bound contents?

Thanks.

 

[SOS]

Get rid of the .SetFocus part. You don't need to set focus on a control to set the value (you only need to do it if you use the .TEXT instead of .Value, which .Value is the default).

 

[bulrush]

I think my problem is a combination of things.

I have a list box called lstFilter. I have a text box called txtSearch. When the user types characters in txtSearch, the lstFilter is filled with the name of products which match the text in txtSearch. When the user clicks on lstFilter the record is displayed on the bound form.

Here is my code in lstFilter.

 Private Sub lstFilter_AfterUpdate()
' Do stuff when user types in txtSearchString.
Dim rs As Object
Dim crit As String

On Error GoTo MyError

'DoCmd.RunCommand acCmdSaveRecord
If gUsername = "chuck" Then Stop
Set rs = Me.Recordset.Clone
crit = "[GID]=" & Str(Nz(Me!lstFilter, 0)) ' Get first column?
rs.FindFirst crit
If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
Exit Sub

MyError:
If Err.Number > 0 Then
    Call DispError("lstFilter_AfterUpdate")
    'Stop
    Resume Next
    Exit Sub
End If

End Sub

I think in the line "Me.Bookmark = rs.bookmark" the Form_BeforeUpdate is run, and the txtUpdateUser and txtUpdateDate are trying to be updated, but they can't. I get an error "-2147352567 Update or CancelUpdate without Addnew or Edit" in the "Form_BeforeUpdate" code above.

Any ideas what is going on? I got my code for the list box search from this site.

 

[vbaInet]

Would this help (in place of your code above)?

Me.Filter = "[GID]=" & Str(Nz(Me!lstFilter, 0))
Me.FilterOn = True

 

[JANR]

But I'm getting an Unknown Error each time I try to disable the field (which has the focus.)

You can't disable a control that HAS focus, you must move the focus away from the control in question before you disable the control.

JR

 

[bulrush]

I fixed the problem. I added "Me.Requery" at the beginning of lstFilter_AfterUpdate. This bug is also found when searching for records using a single combo box. New code is:

Private Sub lstFilter_AfterUpdate()
' Do stuff when user types in txtSearchString.
Dim rs As Object
Dim crit As String

On Error GoTo MyError

Me.Requery ' ****** Fix bug in Access 
'DoCmd.RunCommand acCmdSaveRecord
If gUsername = "chuck" Then Stop
Set rs = Me.Recordset.Clone
crit = "[GID]=" & Str(Nz(Me!lstFilter, 0)) ' Get first column?
rs.FindFirst crit
If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
Exit Sub

MyError:
If Err.Number > 0 Then
    Call DispError("lstFilter_AfterUpdate")
    'Stop
    Resume Next
    Exit Sub
End If


End Sub

 

[vbaInet]

Fabulous! Glad you managed to source it out.