Combo box default

I have an unbound combo box which selects its values from an SQL
statement. I would like this control to default to the first row of
the combo box. BUT, it absolutely refuses to do so. I've tried
[comboname].ItemData(0) in the default property and every other
combination I can think of and nothing! Any ideas??

Dave

Hi Dave,
You can make the combo show the first row of data like this.
Me.[comboname] = Me.[comboname].Itemdata(0)

You would put this code on the form's load event.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Dave" wrote in message
...
I have an unbound combo box which selects its values from an SQL
statement. I would like this control to default to the first row of
the combo box. BUT, it absolutely refuses to do so. I've tried
[comboname].ItemData(0) in the default property and every other
combination I can think of and nothing! Any ideas??

Dave

On Aug 18, 8:00*am, "Jeanette Cunningham"
wrote:
Hi Dave,
You can make the combo show the first row of data like this.
Me.[comboname] = Me.[comboname].Itemdata(0)

You would put this code on the form's load event.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"Dave" wrote in message

...



I have an unbound combo box which selects its values from an SQL
statement. I would like this control to default to the first row of
the combo box. BUT, it absolutely refuses to do so. I've tried
[comboname].ItemData(0) in the default property and every other
combination I can think of and nothing! Any ideas??

Dave- Hide quoted text -

- Show quoted text -

I get a #Name? error

Dave

By any chance is the bound column value NULL on the first item in the combo?
If so, then
=[comboname].ItemData(0)
will not work. The Null messes things up since Null never matches anything,
including null.

Also, I assume that you are using [ComboName] as a generic example for the
actual name of the combobox and are using the actual name of the combobox in
the expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dave wrote:
I have an unbound combo box which selects its values from an SQL
statement. I would like this control to default to the first row of
the combo box. BUT, it absolutely refuses to do so. I've tried
[comboname].ItemData(0) in the default property and every other
combination I can think of and nothing! Any ideas??

Dave

On Aug 18, 9:59*am, John Spencer wrote:
By any chance is the bound column value NULL on the first item in the combo?
If so, then
* *=[comboname].ItemData(0)
will not work. *The Null messes things up since Null never matches anything,
including null.

Also, I assume that you are using [ComboName] as a generic example for the
actual name of the combobox and are using the actual name of the combobox in
the expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County



Dave wrote:
I have an unbound combo box which selects its values from an SQL
statement. I would like this control to default to the first row of
the combo box. BUT, it absolutely refuses to do so. I've tried
[comboname].ItemData(0) in the default property and every other
combination I can think of and nothing! Any ideas??

Dave- Hide quoted text -

- Show quoted text -

Actually, I got it to work if I change the form to design view and
back again. It won't requery no matter where I put a requery request.
Arrgh.

Dave

Sorry, I am confused at this point.

Are you trying to requery the combobox and then set its value to the first
row? If so, would you care to show us the VBA code you are trying to use and
where you are trying to use it?


I would expect to see something like the following in your code.

Me.NameOfCombobox.Requery
Me.NameOfCombobox = Me.NameOfCombobox.ItemData(0)

'With this temporary line to check to see what is assigned to the combobox
Debug.Print Nz(Me.NameOfCombobox,"NULL VALUE")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dave wrote:
On Aug 18, 9:59 am, John Spencer wrote:
By any chance is the bound column value NULL on the first item in the combo?
If so, then
=[comboname].ItemData(0)
will not work. The Null messes things up since Null never matches anything,
including null.

Also, I assume that you are using [ComboName] as a generic example for the
actual name of the combobox and are using the actual name of the combobox in
the expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County



Dave wrote:
I have an unbound combo box which selects its values from an SQL
statement. I would like this control to default to the first row of
the combo box. BUT, it absolutely refuses to do so. I've tried
[comboname].ItemData(0) in the default property and every other
combination I can think of and nothing! Any ideas??
Dave- Hide quoted text -
- Show quoted text -

Actually, I got it to work if I change the form to design view and
back again. It won't requery no matter where I put a requery request.
Arrgh.

Dave

On Aug 18, 10:42*am, John Spencer wrote:
Sorry, I am confused at this point.

Are you trying to requery the combobox and then set its value to the first
row? *If so, would you care to show us the VBA code you are trying to use and
where you are trying to use it?

I would expect to see something like the following in your code.

Me.NameOfCombobox.Requery
Me.NameOfCombobox = Me.NameOfCombobox.ItemData(0)

'With this temporary line to check to see what is assigned to the combobox
Debug.Print Nz(Me.NameOfCombobox,"NULL VALUE")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County



Dave wrote:
On Aug 18, 9:59 am, John Spencer wrote:
By any chance is the bound column value NULL on the first item in the combo?
If so, then
* *=[comboname].ItemData(0)
will not work. *The Null messes things up since Null never matches anything,
including null.

Also, I assume that you are using [ComboName] as a generic example for the
actual name of the combobox and are using the actual name of the combobox in
the expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dave wrote:
I have an unbound combo box which selects its values from an SQL
statement. I would like this control to default to the first row of
the combo box. BUT, it absolutely refuses to do so. I've tried
[comboname].ItemData(0) in the default property and every other
combination I can think of and nothing! Any ideas??
Dave- Hide quoted text -
- Show quoted text -

Actually, I got it to work if I change the form to design view and
back again. It won't requery no matter where I put a requery request.
Arrgh.

Dave- Hide quoted text -

- Show quoted text -

Yes, John that's exactly what I'm trying to do. The combo box in
question is loaded by a select statement using a text box on the form
called txtRecipPK. On the 'change' event of this text box I have the
following code:

Me.cboResultDate.Requery
Me.cboResultDate = Me.cboResultDate.ItemData(0)

If I go to design view and then back again, it works fine.

Thanks for your help,'

Dave

Using the change event means that the text property has changed, but the value
of the control has not yet changed.

So a requery should not change anything in the combobox if it is based on the
control.

If you want to change the combobox as things are typed into txtRecipPK then
you will need to change the Combobox.rowsource property.

As a guess you would need something like the following in the change event of
txtRecipPK

Dim StrSQL
StrSQL = "SELECT Field1, Field2 FROM SomeTable WHERE somefield Like "
strSQL = StrSql & Me.txtRecipPK.Text & "*"
strSQL = StrSQL & " ORDER BY Field1"

Me.CboResultDate.ControlSource = StrSQL
Me.cboResultDate = Me.cboResultDate.ItemData(0)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Dave wrote:
On Aug 18, 10:42 am, John Spencer wrote:
Sorry, I am confused at this point.

Are you trying to requery the combobox and then set its value to the first
row? If so, would you care to show us the VBA code you are trying to use and
where you are trying to use it?

I would expect to see something like the following in your code.

Me.NameOfCombobox.Requery
Me.NameOfCombobox = Me.NameOfCombobox.ItemData(0)

'With this temporary line to check to see what is assigned to the combobox
Debug.Print Nz(Me.NameOfCombobox,"NULL VALUE")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County



Dave wrote:
On Aug 18, 9:59 am, John Spencer wrote:
By any chance is the bound column value NULL on the first item in the combo?
If so, then
=[comboname].ItemData(0)
will not work. The Null messes things up since Null never matches anything,
including null.
Also, I assume that you are using [ComboName] as a generic example for the
actual name of the combobox and are using the actual name of the combobox in
the expression.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Dave wrote:
I have an unbound combo box which selects its values from an SQL
statement. I would like this control to default to the first row of
the combo box. BUT, it absolutely refuses to do so. I've tried
[comboname].ItemData(0) in the default property and every other
combination I can think of and nothing! Any ideas??
Dave- Hide quoted text -
- Show quoted text -
Actually, I got it to work if I change the form to design view and
back again. It won't requery no matter where I put a requery request.
Arrgh.
Dave- Hide quoted text -
- Show quoted text -

Yes, John that's exactly what I'm trying to do. The combo box in
question is loaded by a select statement using a text box on the form
called txtRecipPK. On the 'change' event of this text box I have the
following code:

Me.cboResultDate.Requery
Me.cboResultDate = Me.cboResultDate.ItemData(0)

If I go to design view and then back again, it works fine.

Thanks for your help,'

Dave