Selecting a record from an unbound text box 1

[waldo7474]

Hi.

This was working, I thought. Then I changed some stuff. I thought that I put it back to normal, and it doesn't work. This is what I am hoping to do:

I want a user to type in a PartNumber in an unbound text box called Text76. I want the database to either find the part number and display it, or I want to add that as a new record. This is my code:

Private Sub Text76_AfterUpdate()
    Dim rs As Object
    
    Set rs = Me.Recordset
    rs.FindFirst "[PartNumber] = """ & [Text76] & """"
    DoCmd.GoToControl "Revision"
    Me.Revision.Locked = False
    Me.ECN.Locked = False
    
    If rs.NoMatch Then
        strText76 = Me!Text76
        strPartNumber = Me!PartNumber
        Me!PartNumber = strText76
        DoCmd.GoToControl "Family"
        Me.Family.Locked = False
        Me.MultiplePages.Locked = False
        Me.DrawingSizeSheet1.Locked = False
        Me.DrawingSizeSheet2.Locked = False
        Me.DrawingSizeSheet3.Locked = False
        Me.Description.Locked = False
        Me.Revision.Locked = False
        Me.ECN.Locked = False
        Me.FirstUsedOn.Locked = False
        DoCmd.GoToControl "Family"
        
    End If
    
End Sub

When the user types in a value, if it is found, the focus is placed on the "revision" control, but as a new record (no information is displayed). If it is not found, then I get an error that says I can't go to specified control "Family".

Any suggestions? Any help will be greatly appreciated. Also is there anyway to save VBA code, so I can change stuff and go back to the original if I need to?

Thanks again.
Wally

 

[FancyPrairie]

As I suggested yesterday, you need to use the RecordsetClone. Your code should look something like this:

Private Sub Text76_AfterUpdate()

    Dim rs As DAO.Recordset
    
    Set rs = Me.RecordsetClone

    rs.FindFirst "PartNumber = """ & Text76 & """"

'*******************************************************
'*  If rs.NoMatch is FALSE, then goto record           *
'*  ElseIf rs.NoMatch is True, then goto a new record  *
'*******************************************************

    If (Not rs.NoMatch) Then
        Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark
    Else
        DoCmd.GoToRecord , , acNewRec
         strText76 = Text76
'        blah blah blah
    End If
    
End Sub

A couple of FYI's.

1. You don't need brackets ([]) around field/control names if the name doesn't contain spaces.
2. Instead of Docmd.GoToControl "ControlName". You could type: ControlName.SetFocus

 

[waldo7474]

I appreciate the help Fancy. That seemed to work. I guess that I also had a relationship issue as well, but it is working now.

Could you fill me in as to what the difference between the recordsetclone and recordset is. I have no idea what that means. When I was working with this before it seemed to not pull up the old records if it was set to recordset clone.

Thanks again for the help.

 

[FancyPrairie]

I'm not sure I can explain it fully, but I'll give it a shot. The form's RecordsetClone is just that, a clone of the form's Recordset. However, sometimes you may want to manipulate a set of rows without showing your work on the visible form. The RecordsetClone property of a form creates that copy for you.

In your case you were searching for a given record. By using the form's Recordset to do the search, if the record was not found, the form's current record may change. However, by using the form's RecordsetClone to do the search, if the record is not found, the record position of the form does not change. That is, both the Recordset and the RecordsetClone has its own current row. Once we found the record we wanted (via the RecordsetClone) we set the form's Bookmark equal to the clone's Bookmark. Now they point to the same current row.

Note that you can also create a clone of a recordset. You might want to do this when you want to refer to the same recordset in 2 different ways with two different current row. Also note that not all recordsets are Bookmarkable.

This is not a complete definition of the RecordsetClone, but I hope it helps clearify things.