Combo Box Requery Problem

[LD]

I made a new customer form and code for new customers. When I enter the new
customer, it shows up in the customer list from the combo box, but doesn't
put itself in alphabetical order like the remainder of the customers. It
simply positions itself at the bottom of the list. Any new customer after
that does the same thing and goes at the very bottom. My underlying customer
table puts it in order, but the combo box won't. How do I get it in
alphabetical order? Requerying doesn't seem to work, which I have tried
manually, unless I'm doing it wrong. Here is my code:

Private Sub Customer_NotInList(NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" is not in the list. " & _
"Would you like to add it?"
mbrResponse = MsgBox(strMsg, _
vbYesNo + vbQuestion, "Invalid Customer")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "NewCustomerForm", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData

'Stop Here and wait until the form goes away
If IsLoaded("NewCustomerForm") Then
Response = acDataErrAdded
DoCmd.Save
Me!Customer.Requery
DoCmd.Close acForm, "NewCustomerForm"
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select

End Sub

 

[Arvin Meyer]

You need an Order By clause in your combo's RowSource:

SELECT CustomerID, CustomerName
FROM tblCustomers
ORDER BY CustomerName;

Now when you requery the combo it will put them in the correct alphabetical
order.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[LD]

Thanks--Works perfect

You need an Order By clause in your combo's RowSource:

SELECT CustomerID, CustomerName
FROM tblCustomers
ORDER BY CustomerName;

Now when you requery the combo it will put them in the correct alphabetical
order.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[DianaS]

Hi,

I am new to Access, and am having the same issue as is described in this
post. I would like to use the solution you have described, but where exactly
does one type in the code language?

So far, I have been using combo box "properties" to adjust the settings. It
seems that your solution is more involved.

Thanks,
Diana

 

[DianaS]

Hi,

I am new to Access, and am having the same issue as is described in this
post. I would like to use the solution you have described, but where exactly
does one type in the code language?

So far, I have been using combo box "properties" to adjust the settings. It
seems that your solution is more involved.

Thanks,
Diana