running multiple queries with parameters from table

[wynstonh]

Hi

New to the forum with good access knowledge but only limited VBA.

My problem:

I have a list of account numbers stored in a table and I need to use this list as the parameter to run a single query multiple times.

So if the table has 10 entries I want 10 outputs wth each one being the query result relating to each unique entry in the parameter table.

Any help would be much appreciated.

 

[sneuberg]

Have you tried joining the table with the account numbers with the query?

 

[wynstonh]

Thanks for replying.

I can join them but not sure if it will give me what I want. I can get all the data I need in one query result but ideally I want x number of outputs (tabs/query results) where x is the number of account numbers in my parameter table.

 

[sneuberg]

I can join them but not sure if it will give me what I want..

Why not try? It would only take a minute. I don't see how changing the parameter x times, let's say in VBA code, is going to produce an output any different from what you would get with a join.

 

[wynstonh]

Let me try and clarify the situation & what I want. At the moment I have a front end form with a parameter query "Please enter account number" upon which a single query result is returned with 4 fields of data relating to that single account number. Ideally I want the user to be able to enter multiple account numbers (via an xls import/form entry or direct table entry), then receive a separate output for each account number. Joining the account number table to the query would still only give me a single output. Granted, I can then export that to excel & write a macro to autofilter and paste to a new sheet but I would hope I can do all this in access instead.

 

[Minty]

Assuming your account numbers are already in your system, why not present them in a multi select listbox (queried to only list approprite accounts), and let the user select as many of those for the report as they want.
Use that as a source for some code to loop through those account numbers a produce the spreadsheet / report you need per account.

 

[wynstonh]

Sounds good. I've no problem with the multi select listbox but the code bit I'm a little stuck on. Any help would be much appreciated.

 

[Minty]

This thread http://www.access-programmers.co.uk/forums/showthread.php?t=179928 has an example of creating a spreadsheet based on a multiselect list box. If you get stuck shout back here.

The other route which you users may find more elegant, create a second list box and "move" the selected accounts into it. This gives a more visual clue about what you are about to do, it may not be applicable in your case.
I use one for bulk adding training records, there are a lot of employees so it makes what has been selected easier to see and manage ;

 

[wynstonh]

I've attempted to use a variation of the code in the thread you linked but am getting the error "method or data member not found".

The code is:

Private Sub Command7_DblClick(Cancel As Integer)
If file_list.ListIndex = -1 Then
'If ListIndex is -1, nothing selected
MsgBox "Nothing was selected!"
Else
'If ListIndex not -1 run transfer spreadsheet
Dim varItem As Variant
Dim FilePath As String
Dim FileSelection As String
For Each varItem In Me.file_list.ItemsSelected
FileSelection = file_list.ItemData(varItem)
DoCmd.OpenQuery "qry_live_by_broker"
Next varItem
End If
End Sub

The multi select box is in a sub form as is Command7, but I get the same error if Command7 is in the main form.

 

[Minty]

I don't think you can run it from double clicking the list box as that would select or deselect a record. Create another control to process the event.

The code is referring to a listbox call file_list. I would strongly suggest changing the name of your listbox to something meaningful and update the code to reflect that. Make sure at the top of every code module you have Option Explicit as that would highlight the fact it doesn't know what file_list was.

 

[wynstonh]

Please excuse my stupidity. I'd not changed file_list from the original code to the name of my listbox.

Last question (I hope). How would I amend my query "qry_live_by_broker" so that the values from the multi select box are passed as parameters?

 

[wynstonh]

The code is now:

Private Sub Command7_DblClick(Cancel As Integer)
If List2.ListIndex = -1 Then
'If ListIndex is -1, nothing selected
MsgBox "Nothing was selected!"
Else
Dim varItem As Variant
Dim FilePath As String
Dim FileSelection As String
For Each varItem In Me.List2.ItemsSelected
FileSelection = List2.ItemData(varItem)
DoCmd.OpenQuery "qry_live_by_broker"
Next varItem
End If
End Sub

 

[Minty]

That's a little more tricky, you'll need to make a dynamic querydef which is a bit messy if you've not done it before.
It might be better to ask - What will you ultimately do with the query results? I'm not sure I understand why you want multiple query windows opened all at once.

 

[wynstonh]

Ultimately I'll export them to excel. They don't need to be open all at once, just to run in one go with as little user intervention as possible. I intend on using the TransferSpreadsheet code from that other thread but needed to run a query in between.

 

[Minty]

Is the qry doing some form of update? If it is why not just run it once for all accounts before trying to separate out the individual reports?

 

[wynstonh]

No, it's just a simple select query. I want to avoid separating out the individual reports in excel if I can - was hoping for a purely access solution.

 

[Minty]

In that case I think the query stage isn't required but your need to change the query dynamically is. The code below will create a query (Query1) with the SQL query string specified in strSql.

You need to replace /amend your existing query to bring in all records then add in the account number as the criteria in each loop;

[COLOR="Green"]'Put these declarations at the start of your code
[/COLOR]dim db as database
dim qrydef as Querydef
dim strSql as string
set db = currentdb

[COLOR="green"]'This bit below needs to go into your loop[/COLOR]

strSql = "Select * From YourExistingQry WHERE AccountNo = " & YourListBoxLoopCurrentValue  & " ;"
debug.print strSQL    [COLOR="Green"]' Remove this once you have it working [/COLOR]
set qrydef =db.CreateQueryDefs("Query1")
qrydef.SQL = strSql

If you struggle with the syntax, post up the code used and the result ofthe debug.print. I have assumed accountNO is a numeric value, not a string.

 

[wynstonh]

Here's the code:

Private Sub Command7_DblClick(Cancel As Integer)
Dim db As Database
Dim qrydef As QueryDef
Dim strSql As String
Set db = CurrentDb
If List2.ListIndex = -1 Then
'If ListIndex is -1, nothing selected
MsgBox "Nothing was selected!"
Else
Dim varItem As Variant
Dim FilePath As String
Dim FileSelection As String
For Each varItem In Me.List2.ItemsSelected
FileSelection = List2.ItemData(varItem)
strSql = "Select * From qry_live_by_broker WHERE Broker BP = " & List2LoopCurrentValue & " ;"
Debug.Print strSql ' Remove this once you have it working
Set qrydef = db.CreateQueryDefs("Query1")
qrydef.SQL = strSql

Next varItem
End If
End Sub

I am getting an error "variable not defined" on the part in red, List2 being the name of the list box.

 

[Minty]

I've tided up the code - if you use indentation it becomes much clearer what is happening. I've renamed the selected item in the listbox to make it obvious what is being used and where.

Private Sub Command7_DblClick(Cancel As Integer)

	Dim db As Database
	Dim qrydef As QueryDef
	Dim strSql As String
	Dim varItem As Variant
	Dim [COLOR="Red"]iAcctNo [/COLOR]As Long

	Set db = CurrentDb

	If List2.ListIndex = -1 Then
		'If ListIndex is -1, nothing selected
		MsgBox "Nothing was selected!"
	Else

		For Each varItem In Me.List2.ItemsSelected
			[COLOR="red"]iAcctNo  [/COLOR]= List2.ItemData(varItem)
			strSql = "Select * From qry_live_by_broker WHERE [Broker BP] = " & [COLOR="red"]iAcctNo[/COLOR] & " ;"
			Debug.Print strSql [COLOR="Green"]' Remove this once you have it working[/COLOR]
			Set qrydef = db.CreateQueryDefs("Query1")
			qrydef.SQL = strSql
                        msgbox "Account no: " & iAcct & " processed"
		Next varItem
	End If
	
End Sub

I've also put your field name Broker BP into square brackets as the space in the field name will break things without them. This is one reason why spaces are bad news...

I can't test this currently so post back if you have any issues - but check that the error isn't stating something pretty obvious.

 

[wynstonh]

Great, seems to work apart from:

1. I changed < Set qrydef = db.CreateQueryDefs("Query1") > to < Set qrydef = db.CreateQueryDef("Query1")> as otherwise I get an error "method or data member no found"

2. Each time it loops I get another error "Object 'Query1' already exists. Do I need to delete query1 each time and if so, how?

3. The msgbox is good but where do I find the data?