Two comboboxes - Textbox displays value but I can not type a new value in a form.

[Yiannis]

Hi all



Its been almost 3 weeks having this problem and I am still scratching my head.
You guess right … I am new to Access … so

I have two comboboxes,
Cities coming from a Table S/C (Columns: States and Cities)
States coming from Table Info (Columns: Cities, Population, Likes, ...)
Relation one to one – Cities

And textboxes to display other information (Population, Likes ….)

The two comboboxes works great together (filtering data) and I can display information in the textboxes (based on the second combo box)
What I can not do is to change information displayed in a textbox simply by typing a value.
Example: in a textbox Likes instead of a number displayed e.g 12, I want to change and display to 19.

Here is the a simple code I am using.

 
Private Sub ComboCities_Click()
              Population = DLookup("Population", "query2")
              Likes = DLookup("Likes", "query2")
              Handover = DLookup("HandOver", "query2")
             End Sub
 
Private Sub ComboStates_AfterUpdate()
ComboCities.Requery
End Sub

Any help would be appreciated. Cheers

[June7]

The textboxes don't have ControlSource? Is the Locked property set to Yes?

[ssanfu]

I have two comboboxes,
Cities coming from a Table S/C (Columns: States and Cities)
States coming from Table Info (Columns: Cities, Population, Likes, ...)
Relation one to one – Cities

I think you have States and Cities swapped.

Relation one to one – Cities

If so, then you don't need two tables...

One state can have many cities...... True
One City can be in many states.... False

Therefore, you have a one to many relationship between states and cities.



I would have two tables:

table States
Fields
-----
StateID_PK Autonumber
StateName Text

table Cites
Fields
-----
CityID_PK Autonumber
StateID_FK Long
CityName Text
Population Long (or Text)
Likes Text (???)
Handover (???)


The relationship is States.StateID_PK --> Cities.StateID_FK

The combo boxes would be:

ComboStates

------------
Row Source:
SELECT States.StateID_PK, States.StateName
FROM States
ORDER BY States.StateName;

Column count: 2
Bound Column: 1
Column Widths: 0"


ComboCities
------------
Row Source:
SELECT Cities.CityID_PK, Cities.CityName, Cities.Population, Cities.Likes, Cities.Handover
FROM Cities
WHERE (((Cities.StateID_FK)=[forms]![Form8].[comboStates]))
ORDER BY Cities.CityName;

Column Count: 5
Bound Column: 1
Column Widths: 0"


The code for comboStates:

Private Sub ComboStates_AfterUpdate()
   ComboCities.Requery
End Sub

Code for comboCities:

Private Sub ComboCities_Click()
   Me.Population = Me.comboCities.Column(2)
   Me.Likes = Me.comboCities.Column(3)
   Me.Handover = Me.comboCities.Column(4)
End Sub

After selecting a State, then a City, the text boxes are filled in. This is assuming that they are unbound text boxes.


If you are wanting to edit the City name, Population, Likes, or Handover fields, I would suggest using a mainform/subform arragement.

[Yiannis]

My Textboxes do not have a control source. They can be displayed either from option 1 or 2:

1. Me.Population = Me.ComboCities.Column(2)
2. Population = DLookup("Population", "query2")

To acquire date for the mentioned TextBoxes I previously used 2 ComboBoxes thus why I used the above options.

Thanks in advance.

[Yiannis]

Thanks for your input. I did manage to display information I wanted with your proposed method.
For editing a textbox, you suggested to use a subform.
Is there any way that I can do changes on a Form without adding a subform at the bottom of my form?
Thanks in advance.

[Yiannis]

The textboxes don't have ControlSource? Is the Locked property set to Yes?

My Textboxes do not have a control source. They can be displayed either from option 1 or 2:

1. Me.Population = Me.ComboCities.Column(2)
2. Population = DLookup("Population", "query2")

To acquire date for the mentioned TextBoxes I previously used 2 ComboBoxes thus why I used the above options.

Thanks in advance.

[Yiannis]

I think you have States and Cities swapped.


If so, then you don't need two tables...

One state can have many cities...... True
One City can be in many states.... False

Therefore, you have a one to many relationship between states and cities.



I would have two tables:

table States
Fields
-----
StateID_PK Autonumber
StateName Text

table Cites
Fields
-----
CityID_PK Autonumber
StateID_FK Long
CityName Text
Population Long (or Text)
Likes Text (???)
Handover (???)


The relationship is States.StateID_PK --> Cities.StateID_FK

The combo boxes would be:

ComboStates

------------
Row Source:
SELECT States.StateID_PK, States.StateName
FROM States
ORDER BY States.StateName;

Column count: 2
Bound Column: 1
Column Widths: 0"


ComboCities
------------
Row Source:
SELECT Cities.CityID_PK, Cities.CityName, Cities.Population, Cities.Likes, Cities.Handover
FROM Cities
WHERE (((Cities.StateID_FK)=[forms]![Form8].[comboStates]))
ORDER BY Cities.CityName;

Column Count: 5
Bound Column: 1
Column Widths: 0"


The code for comboStates:

Private Sub ComboStates_AfterUpdate()
   ComboCities.Requery
End Sub

Code for comboCities:

Private Sub ComboCities_Click()
   Me.Population = Me.comboCities.Column(2)
   Me.Likes = Me.comboCities.Column(3)
   Me.Handover = Me.comboCities.Column(4)
End Sub

After selecting a State, then a City, the text boxes are filled in. This is assuming that they are unbound text boxes.


If you are wanting to edit the City name, Population, Likes, or Handover fields, I would suggest using a mainform/subform arragement.

Thanks for your input. I did manage to display information I wanted with your proposed method.
For editing a textbox, you suggested to use a subform.
Is there any way that I can do changes on a Form without adding a subform at the bottom of my form?
Thanks in advance.

[ssanfu]

You said the controls Population, Likes and Handover are unbound. You can edit unbound controls, but the changes have no place to be saved. If the controls are bound to a recordset, they could be edited and saved.

You can write code to save the changes to the unbound controls, but it will be a lot of code to find the correct record to save the changes to.

I don't understand what you are doing so it is difficult to be more detailed...