NotInList - NewData split over 3 Fields

[David Mitchell]

I have a combobox which has the following SQL Statement as its Data
Source

SELECT DISTINCTROW tblCustomer.CustomerID,
Trim([tblCustomer]![CustomerTitle] & " " &
[tblCustomer]![CustomerInitials] & " " &
[tblCustomer]![CustomerSurname]) AS Customer
FROM tblCustomer
ORDER BY Trim([tblCustomer]![CustomerTitle] & " " &
[tblCustomer]![CustomerInitials] & " " &
[tblCustomer]![CustomerSurname]);

I then use the NotinList event procedure to allow the user to add a
new customer. FrmCustAdd is opened (modal) and the user has to type
in Title, Initials and Surname into 3 separate fields. When they
close the form the combobox is requeried but does not default to the
newly entered customer. I realise that this is supposed to work on
just one field but is there a workaround?

[HJ]

Save the new customer ID to a global variable, e.g. lngNewCustomerID. Do
this before you update the recordset (assuming that you add the new customer
data by code). When you close the entry form and the customer form regains
focus, set the customer combo box to the new customer ID (Form_GotFocus or
Form_Activate):

Me.ComboBox1.Value = lngNewCustomerID

HJ

"David Mitchell" <da**************@talk21.com> wrote in message
news:c3************************@posting.google.com ...

I have a combobox which has the following SQL Statement as its Data
Source

SELECT DISTINCTROW tblCustomer.CustomerID,
Trim([tblCustomer]![CustomerTitle] & " " &
[tblCustomer]![CustomerInitials] & " " &
[tblCustomer]![CustomerSurname]) AS Customer
FROM tblCustomer
ORDER BY Trim([tblCustomer]![CustomerTitle] & " " &
[tblCustomer]![CustomerInitials] & " " &
[tblCustomer]![CustomerSurname]);

I then use the NotinList event procedure to allow the user to add a
new customer. FrmCustAdd is opened (modal) and the user has to type
in Title, Initials and Surname into 3 separate fields. When they
close the form the combobox is requeried but does not default to the
newly entered customer. I realise that this is supposed to work on
just one field but is there a workaround?