Question:
I am developing a database program using Access and Visual Basic. I want to display data in a form where the data comes from two tables. For example, the Friends table has a City field and the City field has just CityID, which is linked to City table.

I want to display CityName in a text box. If the user clicks the Edit button, I want the text box to change to a combo box so the user can choose another city name from the combo box.

This is what I want to know:

• The SQL statement to display the city name in the text box
• How I can add the city name list to the combo box.

I have tried to solve the first item but failed because only some data is displayed, not all.

This is my SQL statement:

SELECT Friends.*, City.CityName FROM Friends, City    WHERE Friends.cityID = City.CityID

Answer:
What I would do in your case is load all the cities in a combo box and just display the city for the current record from the combo box (the combo box is read-only at this point). Then if the user clicks the Edit button, enable the combo box and allow them to select a new city.

You’ll need two queries: one to retrieve the initial information, which it appears you already have done, and another to retrieve all the cities from the City table.

Below is a VB code snippet that retrieves all the cities from a City table in SQL Server and populates a combo box. Notice that the city name is put in the Text property and the ID is put in the ItemData property. When you load up an entry from the Friend table you can use the CityID value to find that city in the combo box:

Private Sub Form_Load()    Dim adors As New ADODB.Recordset        With adors        .ActiveConnection = "Provider=SQLOLEDB;User ID=sa;   Initial Catalog=NorthwindTest;Data Source=SERVER"        .Source = "SELECT * FROM City"        .CursorLocation = adUseClient        .Open                Set .ActiveConnection = Nothing        If .State = adStateOpen Then            If .RecordCount > 0 Then                While (Not .EOF) And (Not .BOF)                    Combo1.AddItem .Fields("City").Value                    Combo1.ItemData(Combo1.NewIndex) = .Fields("ID").Value                    .MoveNext                Wend            End If        End If    End WithEnd Sub