MS Access search for record by textbox instead of dropdown

Question

Hi. I'm pretty new to MS Access. I'm trying to create a simple form that will basically search for a particular record using a textbox, rather than a drop down box. Essentially a user would be able to enter an ID number and retrieve some other related Info. However, I do not want the user to be able to add any new records to the database. I've been able to get the forms to look the way I want them, but I'm not sure where to place the code (do I create a macro, insert the code into the properties of the button?) Any help is greatly appreciated!

Answer 1

1. Set the form property Data/'Allow Additions' to No.
2. Either in the AfterUpdate event of the textbox, or in the Click event of a button, you can write code or assign a macro to look up and display the record you want.

Answer 2

I assume that you have bound your form to a table or a query and that you want to be able to enter the ID manually in a textbox, then press ENTER and load that record's data or display an error message if there is no such record.

As dsteele said, make sure that the form's Data property Allow Addtions is set to No to disallow users from adding records.

Then, from the AfterUpdate event of the textbox, add the following code (assuming that your textbox is named txtGoTo):

Private Sub txtGoTo_AfterUpdate()
    If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.RecordSet
    Set rs = Me.RecordsetClone
    rs.FindFirst "[ID]=" & txtGoTo
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.RecordSet.Bookmark = rs.Bookmark
    End If
    rs.Close
    txtGoTo = Null
End Sub

Note that you will have to change the line rs.FindFirst "[ID]=" & txtGoTo to something that is adequate for your data:

• "[ID]=" should be replaced by the field you want to search (it could be "[POReference]=" or something else.

• if you are searching by a numeric ID, for instance because the field is an autonumber column, then the code is fine.
  Otherwise, if the field you are searching on is a string (say PN12-G) then you have to change the code to:

  rs.FindFirst "[ID]=""" & txtGoTo & """"
  

Failing to use the proper quoting (or quoting where not necessary) will result in errors of the kind Data type mismatch....

As a new user, I would recommend that you have a look at the sample NorthWind project database that is either shiped with older versions of Access or available as a template for download from Access 2007.
There a lots of techniques to learn from as a new Access developer, including other ways to implement record navigation.