How to set the Combobox to showup its first value by default.

[Moah Scout]

Hi all!
This forum has been very usefully for the Access Beginners like me. I believe my problem can only be solved in this forum.
I have two tables (tblCustomers and tblOrders)and one form (frmOrders)for data entry, tblCustomers has the following fields:
CoID (Autonumber)
CustomerID (Text,PK)
CustomerFullNam e(Text)

The tblOrders has the following fields:
OrderID (Autonumber, PK)
CustomerID (Lookup Text from tblCustomers.Cu stomerID)
Product (Text)
Quantity (Text)
Price (Currency)

I set a frmOrders to be a combobox of which RowSource=SELEC T [tblCustomers].[CustomerID] FROM tblCustomers WHERE tblCustomers.Co ID=Forms!frmOrd ers!OrderID;
and the AfterUpdate event of [frmOders]![OrderID] set to

Private Sub OrderID_AfterUp date()
CustomerID.SetF ocus
Me.CustomerID.S elected(0) = True
End Sub

The intesion is to keep the first value of Combobox to showup without manually select it. But instead it keep its value waiting for selection.
Where did I do wrong on this?
Please help!

[ADezii]

The problem is that Combo Boxes do not have a Selected Property, only List Boxes. To Select the 1st Item in a Combo Box named CustomerID:

With Me![CustomerID]
  .SetFocus
  .ListIndex = 0    'will Select 1st Item
End With

[Moah Scout]

Thanks for your imediate response.

I have placed the code as you suggested but it gives errors. If you debug and close the opened VBA the field is updated as expected. How to remove this error.
Let me attach the sample db for more clarifications!

[ADezii]

Sorry, not using Access 2007.

[RuralGuy]

The OP is using ac2010 and needs to put the code in the Current event of the form.

[Moah Scout]

I dint know how difficult it is to use ac2003. I have been trying to convert the whole idea to ac2003 but couldnt walk through.
Let me explain what I did at post#15.
1. I set ONCHANGE EVENT at frmOrders!Order ID to REQUERY the whole system so that to refresh and fetch a new records

2. The code placed at AfterUpdate Event On the frmOrders!Dates as follows
Private Sub Dates_AfterUpda te()
With Me![Combo17]
.SetFocus
.ListIndex = 0 'will Select 1st Item
End With
End Sub
Where: Combo17 is just the name of Combobox for CustomerID.
3. ROWSOURCE of Combo17 set to
SELECT [tblCustomers].[CustomerID] FROM tblCustomers WHERE tblCustomers.Co ID=Forms!frmOrd ers!OrderID;
4. the frmOrders set to:
i. Defaultview=Sin gleform
ii. RecordSource=tb lOrders
iii.RecordSourc eType=Dynaset
5. Open the form in formview you will find that there is only one record at the Combobox CustomerID per records as expected (SELECT [tblCustomers].[CustomerID] FROM tblCustomers WHERE tblCustomers.ID =Forms!frmOrder s!ID; )
6. If u hit next to enter new records it open a newform and after data entry at frmOrders!Dates it endup with errors as follows:
#Run-Time Error '2115'
#The macro or function set to BeforeUpdate or Validation Rule Property for this field is preventing MS Access from saving the data in the field.
7. If you hit Debug Button of this MsgBox it highlight the codeline .ListIndex = 0 'will Select 1st Item
8. If you close the VBA it gives a warning and OK it, U will see the form has opened with its new CustomerID automatically!
Questions:
1. How to handle this error?
2. If the Combobox has just one record on its list How to turn this record showup?

[ADezii]

Try:

With Me![Combo17]
  If Not Me.NewRecord Then
    .SetFocus
    .ListIndex = 0 'will Select 1st Item
  End If
End With

[Moah Scout]

This time it doesnt gives error and it neither display the value (it just do nothing)

[RuralGuy]

Try using this code:

Private Sub Dates_AfterUpdate()

   If Me.Combo17.ListCount > 0 Then
      Me.Combo17.ListIndex = 0      'will Select 1st Item
   End If
   Me.Combo17.SetFocus
   
End Sub

[Moah Scout]

The listCount will never be >0 because we have only one record per Orders and Customers for all Customer due to this WHERE Clause set at RowSource of Combo17(WHERE tblCustomers.Co ID=Forms!frmOrd ers!OrdersID;)
That means we have only one [tblCustomers].CoID set for frmOrders!Order sID for selection at COMBO17.
I couldnt find any error in this but if you navigate the records you endup with single RecordShowup in the Combo unless you click on it.
How about to use ListBox intead of ComboBox?

[RuralGuy]

Did you try the code?

[Moah Scout]

Yes I have tried but it gives nothing, I dont know where did I do wrong on this

[RuralGuy]

In guess I must now ask if you are just going to use the one value then why use a ComboBox to display it?

[Moah Scout]

Thanks RuloGuy and Adezii for your Contribution! finaly it works but only if you place the code on EXIT Event of ID or Dates and wont function at AFTERUPDATE Event, I dont know why? (ac2010)
I have decided to use a Combobox because of the choices it has, I couldnt find the way out on TextBox.
For sure only one selection at a time. Give me the way out of TextBox!
The attached is a sample of working DB
Thanks