Adding ROW function in VBA that can get data from Access

[Skinny1434]

Hello all, sorry if this topic has been discussed before, this is my first post on the forum. I am very novice with VBA, and currently I am stuck on a certain problem. I have a ton of data, that is currently stored in Access, it used to be stored in Excel but it made the workbook run very slow. I have a table in access that has many different values for the same customer name. i.e.

Customer A 2000
Customer A 5000
Customer A 1000

Before I switched the data over to Access, I used a INDEX>ROW function that gave me all the values related to Customer A. What I am wondering now, is if there is a way to do the same with an access table. I currently have a connection to the Access table, and I am using a DBVlookup, however, I need to be able to return more than one value. Any help would be great!!

 

[Trevor G]

Welcome to the Forum,

I would suggest that you set up a query in Access that will return the data you want and then add a DoCmd.TransferSpreadsheet which will then place the data into a spreadsheet for you.

 

[Skinny1434]

Thanks for the Reply Trevor, that would be the easy solution, unfortunatley, I have a template built into Excel, which has a series of drop downs, I want the user to be able to open up excel, click in the drop down, bring up a customer and then have all their accounts show up. I dont want to have to have a user go into Access and run a query. Therfore I was thinking of just having the VBA do all the work. Do you follow?

 

[Trevor G]

Yes I follow no problems,

The following example I use to select a department from a drop down in Excel it then goes to a database uses a query and brings back all the relevant data. So a standard query has to be made, but the users only use the excel workbook. In the screen below D3 is a validation list which they select from.

Sample of spreadsheet.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		Run Access Query into Excel
2
3			Which Department	Shipping
4			Count	112
5
6	Title	Firstname	Lastname	JobTitle	BirthDate	Gender	Office	Department	Email	Salary
7	Miss	Cordelia	Threipland	Shipping Assistant	22 Oct 1954	F	New York	Shipping	cordelia.threipland@fontstuff.com	25000
8	Miss	Michelle	Heuman	Stock Controller	21 May 1956	F	Indianapolis	Shipping	michelle.heuman@fontstuff.com	26000
9	Ms	Sarah	Hislop	Regional Shipping Manager	18 Oct 1970	F	London	Shipping	sarah.hislop@fontstuff.com	26000
10	Mr	Richard	Brierley	Shipping Assistant	07 Mar 1973	M	London	Shipping	richard.brierley@fontstuff.com	25000
11	Miss	Kate	Butland	Shipping Assistant	25 Mar 1976	F	Brussels	Shipping	kate.butland@fontstuff.com	25000
12	Mrs	Samantha	Hill	Shipping Assistant	23 Mar 1968	F	Cardiff	Shipping	samantha.hill@fontstuff.com	25000
13	Miss	Rebecca	Jackson	Regional Shipping Manager	12 Dec 1971	F	Manchester	Shipping	rebecca.jackson@fontstuff.com	25000
14	Mr	James	Gledhill	Regional Shipping Manager	27 Feb 1949	M	Paris	Shipping	james.gledhill@fontstuff.com	25000
15	Dr	Annette	Rawlence	Secretary	24 Sep 1953	F	Berkeley	Shipping	annette.rawlence@fontstuff.com	26000
16	Miss	Natasha	Hirom	Shipping Assistant	13 Apr 1963	F	Seattle	Shipping	natasha.hirom@fontstuff.com	26000
Sheet1

#VALUE!

Sample Code in the workbook

Sub RunParameterQuery()

'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase("M:\Access Files\Test ME Today.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qryGoToExcel") 'Query name in the database

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Enter Department]") = Range("D3").Value 'From parameter field in access

End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 5: Clear previous contents
Sheets("Sheet1").Select
ActiveSheet.Range("A6:K10000").ClearContents

'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
Cells.EntireColumn.AutoFit

MsgBox "Query has been successful", vbInformation, "Sample"

End Sub

 

[Skinny1434]

Thanks Trevor, I am going to try this out, I will let you know how I make out.

 

[Skinny1434]

Trevor, thank you so much!! You are a genius, this works perfectly!!!

 

[Trevor G]

Pleased to read you have a solution you can work with,

Thanks for the feedback.

 

[Skinny1434]

Question, is there a way to make copies of this for other tabs, and make it possible to select different customers for each tab? The way it is set up now doesnt allow for that to happen. Thanks.

 

[Trevor G]

Set the data to use a Pivot Table, you can then use the Page Option to split the data down to individual sheets and they will have there own Pivot table.

 

[Skinny1434]

Not to sound like a noob, I know what a pivot table is but I have never used one, how would I go about doing that?