Stuck on a VBA/ADODB/SQL query

jcl327

New Member
Joined
Apr 10, 2020
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
So... I have to pull information from a database called sales orders. In excel, I need an input box to appear where the user inputs a customer ID, then the customer ID, First Name, Last Name, City, and State appear in Excel. When I run my code, it populates the column headers and nothing else. In other words, if I enter 7 in the input box it should pull this information for customer ID number & in the access database. I'm sure I'm just missing something small. I am an absolute newbie. Thanks in advance.

Here is my code:

Sub Test()
Dim RequestedID As String


RequestedID = Val(InputBox("Please Enter The Customer ID.", "Input"))

Dim cn As New ADODB.Connection
cn.ConnectionString = "Data Source=C:\Users\R01222430\OneDrive - University of Scranton\Documents\Sales Orders Homework\sales.accdb;"
cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.Open




Dim rs As New ADODB.Recordset
Dim SQL As String
SQL = "Select CustomerID, CustFirstName, CustLastName, CustCity, CustState FROM Customers WHERE CustomerID = " & RequestedID & " "

rs.Open SQL, cn

Dim topCell As Range
Set topCell = Range("A1")
Sheet1.Cells.Clear

Dim rowCount As Integer
rowCount = 0


topCell.Offset(0, 0).Value = "Customer ID"
topCell.Offset(0, 1).Value = "First Name"
topCell.Offset(0, 2).Value = "Last Name"
topCell.Offset(0, 3).Value = "City"
topCell.Offset(0, 4).Value = "State"

Do While Not rs.EOF
rowCount = rowCount + 1
If CustomerID = rs.Fields("CustomerID") Then
topCell.Offset(rowCount, 0).Value = rs.Fields("CustomerID")
topCell.Offset(rowCount, 1).Value = rs.Fields("CustFirstName")
topCell.Offset(rowCount, 2).Value = rs.Fields("CustLastName")
topCell.Offset(rowCoaunt, 3).Value = rs.Fields("CustCity")
topCell.Offset(rowCount, 4).Value = rs.Fields("CustState")
Exit Do
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,123,342
Messages
5,601,066
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top