Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    87

    Unanswered: How to make automatic field fill? (Updated)

    I have Cascading lists for my db.There are 2 combobox'es, where in first you select Country, and in Second you select City (for that Country).
    Now i want to create third field (simple text box) where it would automaticaly show population for the city I select in combobox2.

    How could i do that?


    here is the code I use now for those 2 combos:

    1st combo rowsource:

    SELECT DISTINCT tblAll.Country FROM tblAll ORDER BY tblAll.Country;


    For 2nd combo:

    Private Sub cboCountry_AfterUpdate()
    On Error Resume Next
    cboCity.RowSource = "Select tblAll.City " & _
    "FROM tblAll " & _
    "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
    "ORDER BY tblAll.City;"
    End Sub


    tblAll - has country,city and population columns inside.

    Thank you
    Last edited by YZF; 11-11-05 at 08:34. Reason: Editing

  2. #2
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    There are several ways to this, but I think the simplest way would be to update the ComboBox RecordSource to an SQL string including the population field, but keep ColumnCount = 1, and then retrieve the data from the hidden second ComboBox column vis:

    Private Sub cboCountry_AfterUpdate()
    On Error Resume Next
    cboCity.RowSource = "Select tblAll.City, tblAll.CityPopulation " & _
    "FROM tblAll " & _
    "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
    "ORDER BY tblAll.City;"

    'Optional Default Values for City ComboBox and Population TextBox controls
    With cboCity
    .Value = .Column(0, 0)
    txtCityPopulation = .Column(1, 0)
    End With
    End Sub

    Private Sub cboCity_AfterUpdate()
    On Error Resume Next
    With cboCity
    txtCityPopulation = .Column(1, .ListIndex)
    End With
    End Sub

  3. #3
    Join Date
    Oct 2005
    Posts
    87
    Quote Originally Posted by MyNewFlavour
    There are several ways to this, but I think the simplest way would be to update the ComboBox RecordSource to an SQL string including the population field, but keep ColumnCount = 1, and then retrieve the data from the hidden second ComboBox column vis:

    Private Sub cboCountry_AfterUpdate()
    On Error Resume Next
    cboCity.RowSource = "Select tblAll.City, tblAll.CityPopulation " & _
    "FROM tblAll " & _
    "WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
    "ORDER BY tblAll.City;"

    'Optional Default Values for City ComboBox and Population TextBox controls
    With cboCity
    .Value = .Column(0, 0)
    txtCityPopulation = .Column(1, 0)
    End With
    End Sub

    Private Sub cboCity_AfterUpdate()
    On Error Resume Next
    With cboCity
    txtCityPopulation = .Column(1, .ListIndex)
    End With
    End Sub

    Thank you! Now it works

  4. #4
    Join Date
    Oct 2005
    Posts
    87
    Now i have another "problem"...
    Everything works OK when i only select different Cities in combobox "cboCity". But sometimes i need to edit this field ("cboCity") and then (ofcourse) it doesn't show me population...population value changes to empty.

    Is it possible to do so, that at first i select city from "cboCity", then it shows me population for that City, and if i change manualy city name in this "cboCity", to something else, the population value wouldn't change....
    Until i choose another city in "cboCity"...

  5. #5
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    I've assumed in my previous reply that your using the combo boxes as a look up only. Is that correct or is tblAll the RecordSource for the form?

  6. #6
    Join Date
    Oct 2005
    Posts
    87
    Quote Originally Posted by MyNewFlavour
    I've assumed in my previous reply that your using the combo boxes as a look up only. Is that correct or is tblAll the RecordSource for the form?
    In table design view, cboCountry has lookup and rowsource is:

    SELECT DISTINCT [tblAll].[Country] FROM tblAll ORDER BY [tblAll].[Country];

    In table design view, cboCity has lookup, but rowsource is empty.



    If I look into Form properties, then Rowsource for cboCountry is (the same):


    SELECT DISTINCT [tblAll].[Country] FROM tblAll ORDER BY [tblAll].[Country];

    And for cboCity it is:

    SELECT [tblAll].[City], [tblAll].[Population] FROM tblAll WHERE [tblAll].[Country]='' ORDER BY [tblAll].[City];


    As i understand "form properties>rowsource" is the one that is correct...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •