Help with a data entry form and entering data.

[RobertSmith101]

Hello friends, I have come into a problem when using Access and I would appreciate your help. I have a Customer Database with the following attributes: CustomerID, (primary key) First Name, Surname, Address etc.
However what I want to know what do is on my Data Entry form when I enter the CustomerID on the Data Entry Form I would like if the CustomerID is already on the database for example 123 is the customerID and its already on the database when it is entered on the database again I want it to automatically enter on the data entry form the other fields such as First Name Sur Name etc. do you know how I would be able to do this?

Thank you very much,
Robert

 

[Corticus]

Hi RobertSmith101,

There are several ways you can do this, depending on your experience with Access/VBA. I'll start with the easiest way.

Open your form in design view, and delete the textbox for customer ID. Then drag a drop combo over from the control toolbox. Use the wizard that pops up to have the form retrieve a record based on what they've entered in the combobox.

Another method, using a textbox, produces the same results as above, but requires a bit of VBA coding. Right click on the textbox where you put the customerID (I'll call it 'txtcustID', in table 'tblcust' referencing field 'custID'). Select Build Event|Code Builder.

Insert code as follows:

Private Sub txtcustID_AfterUpdate()

    Dim rs As Object

    Set rs = Me.Recordset.Clone     

    If IsNull(txtcustid) Then
        Exit Sub
    End If

    rs.FindFirst "[tblcust].[custID] = " & Str(Me![txtcustID])
    Me.Bookmark = rs.Bookmark

End Sub

Both will go to the first instance of the customerID.

HTH,