Accessing SQL and Visual Basic

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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a