Default Value

[Guest]

Hi all

I have a combo box which is populated from the results of a different combo
box on a form using the strsql to set rowsource. If the results returned are
only one record can the default value of the 2nd combo box be set to this?

I have tried setting default value to first row in combo box but could not
get this working, also I only want this to be true if there is only the one
row returned. Hope this makes sense!

Thanks in advance for any help.
Sue

 

[Guest]

Sue:

You need a little bit more code in the first combo box's AfterUpdate event
procedure. Lets say the first combo box is of States and the second of
Cities. The second has a RowSource along the lines of:

SELECT CityID, City
FROM Cities
WHERE StateID = Forms!MyForm!cboStates
ORDER BY City;

In cboStates AfterUpdate event procedure the code would be something like:

Dim ctrl As Control

Set ctrl = Me.cboCities

ctrl.Requery

If ctrl.ListCount = 1 Then
ctrl = ctrl.Column(0, 0)
End If

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken, I tried this but it didn't work. Here is my code:

Dim strSQL As String, ctrl As Control


strSQL = "Select CustID, Distributor, CountryCode from [Distributor
Codes (ECS created)]" _
& "where Live = True and CustID = " & Me!cboCustID & " ORDER BY
Distributor"

Me!CountryCode.RowSourceType = "Table/Query"
Me!CountryCode.RowSource = strSQL

Set ctrl = Me.CountryCode
ctrl.Requery

If ctrl.ListCount = 1 Then
ctrl = ctrl.Column(0, 0)
End If

 

[Guest]

Sue:

From its name I imagine that the CountryCode combo box's BoundColumn
property is probably 3 rather than the usual 1. In which case you'd have to
use:

ctrl = ctrl.Column(2, 0)

The column property is zero based, so this references the third column of
the first row.

Ken Sheridan
Stafford, England

Thanks Ken, I tried this but it didn't work. Here is my code:

Dim strSQL As String, ctrl As Control


strSQL = "Select CustID, Distributor, CountryCode from [Distributor
Codes (ECS created)]" _
& "where Live = True and CustID = " & Me!cboCustID & " ORDER BY
Distributor"

Me!CountryCode.RowSourceType = "Table/Query"
Me!CountryCode.RowSource = strSQL

Set ctrl = Me.CountryCode
ctrl.Requery

If ctrl.ListCount = 1 Then
ctrl = ctrl.Column(0, 0)
End If


--
Thanks in advance for any help.
Sue

Sue:

You need a little bit more code in the first combo box's AfterUpdate event
procedure. Lets say the first combo box is of States and the second of
Cities. The second has a RowSource along the lines of:

SELECT CityID, City
FROM Cities
WHERE StateID = Forms!MyForm!cboStates
ORDER BY City;

In cboStates AfterUpdate event procedure the code would be something like:

Dim ctrl As Control

Set ctrl = Me.cboCities

ctrl.Requery

If ctrl.ListCount = 1 Then
ctrl = ctrl.Column(0, 0)
End If

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken! This worked a treat... sorry I should have realised you meant to
reference the bound column.

Sue:

From its name I imagine that the CountryCode combo box's BoundColumn
property is probably 3 rather than the usual 1. In which case you'd have to
use:

ctrl = ctrl.Column(2, 0)

The column property is zero based, so this references the third column of
the first row.

Ken Sheridan
Stafford, England

Thanks Ken, I tried this but it didn't work. Here is my code:

Dim strSQL As String, ctrl As Control


strSQL = "Select CustID, Distributor, CountryCode from [Distributor
Codes (ECS created)]" _
& "where Live = True and CustID = " & Me!cboCustID & " ORDER BY
Distributor"

Me!CountryCode.RowSourceType = "Table/Query"
Me!CountryCode.RowSource = strSQL

Set ctrl = Me.CountryCode
ctrl.Requery

If ctrl.ListCount = 1 Then
ctrl = ctrl.Column(0, 0)
End If


--
Thanks in advance for any help.
Sue

Sue:

You need a little bit more code in the first combo box's AfterUpdate event
procedure. Lets say the first combo box is of States and the second of
Cities. The second has a RowSource along the lines of:

SELECT CityID, City
FROM Cities
WHERE StateID = Forms!MyForm!cboStates
ORDER BY City;

In cboStates AfterUpdate event procedure the code would be something like:

Dim ctrl As Control

Set ctrl = Me.cboCities

ctrl.Requery

If ctrl.ListCount = 1 Then
ctrl = ctrl.Column(0, 0)
End If

Ken Sheridan
Stafford, England

:

Hi all

I have a combo box which is populated from the results of a different combo
box on a form using the strsql to set rowsource. If the results returned are
only one record can the default value of the 2nd combo box be set to this?

I have tried setting default value to first row in combo box but could not
get this working, also I only want this to be true if there is only the one
row returned. Hope this makes sense!

Thanks in advance for any help.
Sue