Accessing SQL and Visual Basic

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        .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:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes