Can I coordinate 2 combo boxes?

[Guest]

I’d like to get two combo boxes to work jointly. Here’s the situation:

The table has 2 fields: Airport Code (text) and Airport Location (text).
Airport code is the primary key, being a unique 3-letter “code†assigned by
the FFA. Airport location is as it says, the name of the location/city of
the airport.

In my application, the user needs to pick an airport, but I want to give
him/her the option of either (1) entering the airport code, or (2) entering
the airport location, depending upon which they are most familiar. Using a
combo box for both allows them to take advantage of the auto-complete feature.

Am I correct to use the AfterUpdate routine to set the “other†field to the
proper one? So, if the user chooses to enter the airport code (e.g., “ANCâ€),
when he/she leaves that field the location field will be automatically filled
in properly (e.g., set to “Anchorageâ€).. And vice versa.

Or will I end up chasing my tail? Is there a better option I’m not seeing?

Thanks.

Jerry

 

[Guest]

There is another way you can do this. You can actually do it with one combo
box and a command button. Here is the concept. Set up your combo with two
columns, one for the airport code and one for the location. The set it up so
that one of the two is how it works when the form first opens. Let's assume
that will be by code. Then, your command button will have code behind it
that alters the properties of the combo to work by location or by code. Of
course, at this time, the caption of the command button will say Location
when the form opens using the code. Then, when the user clicks the button
that says location, it changes the combo properties to work by location and
changes the button's caption to Code. You can use the current Bound Column
proerty of the combo to tell you what to do when the user clicks it.

Here is a sample of one I did as an experiment:

Private Sub Combo0_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

If Me.Combo0.BoundColumn = 1 Then
strCriteria = "[Activity] = '"
Else
strCriteria = "[DESCRIPTION] = '"
End If
strCriteria = strCriteria & Me.Combo0.Column(0) & "'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

Private Sub Command6_Click()
Dim strAct As String
Dim strDesc As String

strAct = "SELECT CISAttributeTable.ACTIVITY,
CISAttributeTable.DESCRIPTION FROM CISAttributeTable;"
strDesc = "SELECT CISAttributeTable.DESCRIPTION,
CISAttributeTable.ACTIVITY FROM CISAttributeTable;"
If Me.Combo0.BoundColumn = 1 Then
Me.Command6.Caption = "Activity"
Me.Combo0.BoundColumn = 2
Me.Combo0.RowSource = strDesc
Me.Combo0.ColumnWidths = "2.5"";1.1"""
Else
Me.Command6.Caption = "Description"
Me.Combo0.BoundColumn = 1
Me.Combo0.RowSource = strAct
Me.Combo0.ColumnWidths = "1.1"";2.5"""
End If
End Sub

 

[Guest]

Thanks for all your work, Klatuu! I'll have to ruminate on your idea.

Can I muddy the waters a bit? I was planning on using this routine in a
subform set to continuous forms. When I said the user has to pick an airport
I should have said airports, plural. They pick their origination point,
destination point and any waypoints in between. There's a one-to-many
relationship between the FlightID and the Airports.

Would that nulify your idea?

Jerry

There is another way you can do this. You can actually do it with one combo
box and a command button. Here is the concept. Set up your combo with two
columns, one for the airport code and one for the location. The set it up so
that one of the two is how it works when the form first opens. Let's assume
that will be by code. Then, your command button will have code behind it
that alters the properties of the combo to work by location or by code. Of
course, at this time, the caption of the command button will say Location
when the form opens using the code. Then, when the user clicks the button
that says location, it changes the combo properties to work by location and
changes the button's caption to Code. You can use the current Bound Column
proerty of the combo to tell you what to do when the user clicks it.

Here is a sample of one I did as an experiment:

Private Sub Combo0_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

If Me.Combo0.BoundColumn = 1 Then
strCriteria = "[Activity] = '"
Else
strCriteria = "[DESCRIPTION] = '"
End If
strCriteria = strCriteria & Me.Combo0.Column(0) & "'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

Private Sub Command6_Click()
Dim strAct As String
Dim strDesc As String

strAct = "SELECT CISAttributeTable.ACTIVITY,
CISAttributeTable.DESCRIPTION FROM CISAttributeTable;"
strDesc = "SELECT CISAttributeTable.DESCRIPTION,
CISAttributeTable.ACTIVITY FROM CISAttributeTable;"
If Me.Combo0.BoundColumn = 1 Then
Me.Command6.Caption = "Activity"
Me.Combo0.BoundColumn = 2
Me.Combo0.RowSource = strDesc
Me.Combo0.ColumnWidths = "2.5"";1.1"""
Else
Me.Command6.Caption = "Description"
Me.Combo0.BoundColumn = 1
Me.Combo0.RowSource = strAct
Me.Combo0.ColumnWidths = "1.1"";2.5"""
End If
End Sub

I’d like to get two combo boxes to work jointly. Here’s the situation:

The table has 2 fields: Airport Code (text) and Airport Location (text).
Airport code is the primary key, being a unique 3-letter “code†assigned by
the FFA. Airport location is as it says, the name of the location/city of
the airport.

In my application, the user needs to pick an airport, but I want to give
him/her the option of either (1) entering the airport code, or (2) entering
the airport location, depending upon which they are most familiar. Using a
combo box for both allows them to take advantage of the auto-complete feature.

Am I correct to use the AfterUpdate routine to set the “other†field to the
proper one? So, if the user chooses to enter the airport code (e.g., “ANCâ€),
when he/she leaves that field the location field will be automatically filled
in properly (e.g., set to “Anchorageâ€).. And vice versa.

Or will I end up chasing my tail? Is there a better option I’m not seeing?

Thanks.

Jerry

 

[Guest]

It would not nullify my idea; however, it would change it a bit. First, if
you are going to allow multiple selections, a combo box will not work for
you. Combo boxes do not allow multiple selections. You will need to use a
list box with multi select set to either simple or extended. Then, the
command button would have to have code behind it to use two different row
sources depending on which list you want to present.

Thanks for all your work, Klatuu! I'll have to ruminate on your idea.

Can I muddy the waters a bit? I was planning on using this routine in a
subform set to continuous forms. When I said the user has to pick an airport
I should have said airports, plural. They pick their origination point,
destination point and any waypoints in between. There's a one-to-many
relationship between the FlightID and the Airports.

Would that nulify your idea?

Jerry

There is another way you can do this. You can actually do it with one combo
box and a command button. Here is the concept. Set up your combo with two
columns, one for the airport code and one for the location. The set it up so
that one of the two is how it works when the form first opens. Let's assume
that will be by code. Then, your command button will have code behind it
that alters the properties of the combo to work by location or by code. Of
course, at this time, the caption of the command button will say Location
when the form opens using the code. Then, when the user clicks the button
that says location, it changes the combo properties to work by location and
changes the button's caption to Code. You can use the current Bound Column
proerty of the combo to tell you what to do when the user clicks it.

Here is a sample of one I did as an experiment:

Private Sub Combo0_AfterUpdate()
Dim rst As DAO.Recordset
Dim strCriteria As String

If Me.Combo0.BoundColumn = 1 Then
strCriteria = "[Activity] = '"
Else
strCriteria = "[DESCRIPTION] = '"
End If
strCriteria = strCriteria & Me.Combo0.Column(0) & "'"
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

Private Sub Command6_Click()
Dim strAct As String
Dim strDesc As String

strAct = "SELECT CISAttributeTable.ACTIVITY,
CISAttributeTable.DESCRIPTION FROM CISAttributeTable;"
strDesc = "SELECT CISAttributeTable.DESCRIPTION,
CISAttributeTable.ACTIVITY FROM CISAttributeTable;"
If Me.Combo0.BoundColumn = 1 Then
Me.Command6.Caption = "Activity"
Me.Combo0.BoundColumn = 2
Me.Combo0.RowSource = strDesc
Me.Combo0.ColumnWidths = "2.5"";1.1"""
Else
Me.Command6.Caption = "Description"
Me.Combo0.BoundColumn = 1
Me.Combo0.RowSource = strAct
Me.Combo0.ColumnWidths = "1.1"";2.5"""
End If
End Sub

I’d like to get two combo boxes to work jointly. Here’s the situation:

The table has 2 fields: Airport Code (text) and Airport Location (text).
Airport code is the primary key, being a unique 3-letter “code†assigned by
the FFA. Airport location is as it says, the name of the location/city of
the airport.

In my application, the user needs to pick an airport, but I want to give
him/her the option of either (1) entering the airport code, or (2) entering
the airport location, depending upon which they are most familiar. Using a
combo box for both allows them to take advantage of the auto-complete feature.

Am I correct to use the AfterUpdate routine to set the “other†field to the
proper one? So, if the user chooses to enter the airport code (e.g., “ANCâ€),
when he/she leaves that field the location field will be automatically filled
in properly (e.g., set to “Anchorageâ€).. And vice versa.

Or will I end up chasing my tail? Is there a better option I’m not seeing?

Thanks.

Jerry