MS Access open record in a form based on current field value

Question

I have the following New Departure form in MS Access:

I want to be able to enter an Item ID (we can be sure that the entered value is valid and such a record already exists), and based on the entered Item ID open the record from the 1 Inventory table in this form (this should be the effect of pressing the Open button, or alternatively, pressing Enter).

I tried attaching the following Macro to the OnClick event of the Open button:

When I enter a value for Item ID, and press Open, always the last record in 1 Inventory is opened, no matter the entered value.

The property sheet of the Item ID field is the following: property sheet 1 property sheet 2

Please help me solve this problem!

Answer 1

1- unbound the form's controls Control Source and unbound form's Record Source

2- Set Item ID Before Update Event In VBA Editor, To Check If Item Already Exist.

    Dim rs     As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [1 Inventory] " & _
        "WHERE [Item ID] = '" & Me.[Item ID] & "'")
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        With rs
            Me.[item name] = ![item name]
        End With
    Else
        DoCmd.CancelEvent
        MsgBox "This Item is Not Exist"
    End If
    rs.Close
    Set rs = Nothing

3-and there is no need for open button delete it dynamically item name Will be entered.

4- use following code to save data to your table new departure when you click save

 CurrentDb.Execute "Insert Into [new departure] ([item ID], [Departure Date], [Departure Personel], [Truck Licence Plate], [Departure Comment]) " & _
                      "Values ('" & Me.[item ID] & "', " & Me.[Departure Date] & ", '" & Me.[Departure Personel] & "', '" & Me.[Truck Licence Plate] & "', '" & Me.[Departure Comment] & "')"