Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 12, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Accessing SQL and Visual Basic

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

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
        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
            End If
        End If
    End With

End Sub
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date