VBA code to make a combo box selection (Access)

Question

My situation is quite simple, a user is presented with a drop down menu (combo box) of customers, if the customer needed to fill the form is not present in the database already the user can click an "Add Customer" button which opens up a pop up form to enter the customer details and insert the new entry into the customers table.

I'm nit picking now but, if the user now wants to select the newly entered customer they must re-select the customer from the combo box which will now present a new entry, is there a way of automatically making the combo box default to the newly inserted customer with an "On Close" event on the pop up form?

This is the "On Close" code I already have which refreshes the customer combo box to add the new entry, is there anything I can add which will make it so the box defaults to the new customer entered?

Private Sub Form_Close()

    If CurrentProject.AllForms("edit appointments").IsLoaded Then

        Forms![edit appointments]!customerCombo.Requery

    End If

    If CurrentProject.AllForms("edit purchases").IsLoaded Then

        Forms![edit purchases]!customerCombo.Requery

    End If

End Sub

why do you want to update a control in a form, when the form is being closed. should you not update it when it is still open? — jsotola
@jsotola Hi, I just figured out how to do it. The form that is being closed is the pop-up form so the user can enter a new customer to be used in the "main" form that contains the combo box with all the customers. — Daniel

Daniel Daniel · Accepted Answer · 2017-10-31T01:57:36

I figured it out.

Forms![edit appointments]!customerCombo = DLast("ID", "customer")

This takes the last entry in the "customers" table and sets the value of the combo box to the value contained in the field "ID".

VBA code to make a combo box selection (Access)

1 Answers