ShowAllRecords problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that displays parts. I have a textbox where the user can type
in a part number and rather than only display that part (essentially filter),
I wanted to just go to that record. To do so, I am using the following code:

Private Sub txtPN_AfterUpdate()
DoCmd.ShowAllRecords
DoCmd.GoToControl "PN"
DoCmd.FindRecord Me.txtPN
Me.PN.SetFocus
End Sub

The source for my form is the table Parts.

My problem is that when the user locates a part using the above code, they
cannot edit the record. Actually, they can edit the record but the changes
will not save. If I scroll to the part number, I can edit the record and the
changes will be saved.

Is there something about the ShowAllRecords command that is causing this?
Is there a better way to do this? I've used this same code in other
applications but this is the first time I have encountered this problem.

Thanks in advance!
 
Assuming that txtPN is unbound, there's nothing obviously wrong with the
code. Do you receive an error message when the record does not save?

This example shows how to:
- Find the record in the form without removing any filter if possible.
- Lookup the table to see if it is a valid record before removing the
filter.
- Give a message if the entry is not found at all.
- Avoid concurrency errors by saving before moving record or remoing the
filter.

Private Sub txtPN_AfterUpdate()
Dim strWhere As String
Dim rs As DAO.Recordset

If Not IsNull(Me.txtPN) Then
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

strWhere = "PN = """ & Me.txtPN & """"
Set rs = Me.RecordsetClone
rs.FindFirst strWhere

If rs.NoMatch And Me.FilterOn Then
If Not IsNull(DLookup("PN", "MyTable", strWhere) Then
Set rs = Nothing
Me.FilterOn = False
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
End If
End If

If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End Sub


Note: If PN is a Number type field, drop the extra quotes, i.e.:
strWhere = "PN = " & Me.txtPN
 
Allen,

No, I'm not getting any error message which makes this even more strange.
At least if I was I could work from there. The textbox is unbound and I've
got this same scenario working on several other forms in this app. It's
really bizarre! I will try your code and see if I can get this to work
consistently. Thanks for your help!

Diana
 
Back
Top