469,270 Members | 1,044 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

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

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)
CustomerFullName(Text)

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

I set a frmOrders to be a combobox of which RowSource=SELECT [tblCustomers].[CustomerID] FROM tblCustomers WHERE tblCustomers.CoID=Forms!frmOrders!OrderID;
and the AfterUpdate event of [frmOders]![OrderID] set to

Private Sub OrderID_AfterUpdate()
CustomerID.SetFocus
Me.CustomerID.Selected(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!
Oct 30 '10 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. With Me![CustomerID]
  2.   .SetFocus
  3.   .ListIndex = 0    'will Select 1st Item
  4. End With

13 37366
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. With Me![CustomerID]
  2.   .SetFocus
  3.   .ListIndex = 0    'will Select 1st Item
  4. End With
Oct 30 '10 #2
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!
Attached Files
File Type: zip Combodb.zip (71.0 KB, 539 views)
Oct 30 '10 #3
ADezii
8,800 Expert 8TB
Sorry, not using Access 2007.
Oct 30 '10 #4
RuralGuy
375 Expert 256MB
The OP is using ac2010 and needs to put the code in the Current event of the form.
Oct 31 '10 #5
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!OrderID 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_AfterUpdate()
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.CoID=Forms!frmOrders!OrderID;
4. the frmOrders set to:
i. Defaultview=Singleform
ii. RecordSource=tblOrders
iii.RecordSourceType=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!frmOrders!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?
Oct 31 '10 #6
ADezii
8,800 Expert 8TB
Try:
Expand|Select|Wrap|Line Numbers
  1. With Me![Combo17]
  2.   If Not Me.NewRecord Then
  3.     .SetFocus
  4.     .ListIndex = 0 'will Select 1st Item
  5.   End If
  6. End With
Oct 31 '10 #7
This time it doesnt gives error and it neither display the value (it just do nothing)
Oct 31 '10 #8
RuralGuy
375 Expert 256MB
Try using this code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Dates_AfterUpdate()
  2.  
  3.    If Me.Combo17.ListCount > 0 Then
  4.       Me.Combo17.ListIndex = 0      'will Select 1st Item
  5.    End If
  6.    Me.Combo17.SetFocus
  7.  
  8. End Sub
Oct 31 '10 #9
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.CoID=Forms!frmOrders!OrdersID;)
That means we have only one [tblCustomers].CoID set for frmOrders!OrdersID 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?
Oct 31 '10 #10
RuralGuy
375 Expert 256MB
Did you try the code?
Oct 31 '10 #11
Yes I have tried but it gives nothing, I dont know where did I do wrong on this
Oct 31 '10 #12
RuralGuy
375 Expert 256MB
In guess I must now ask if you are just going to use the one value then why use a ComboBox to display it?
Oct 31 '10 #13
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
Attached Files
File Type: zip Comboz2007.zip (72.1 KB, 452 views)
Oct 31 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by captain_2010 | last post: by
2 posts views Thread by Nate | last post: by
12 posts views Thread by Brett Romero | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.