DoCmd.OpenQuery

G

Guest

The code below works perfect - it produces a report and also creates an email
based on choices from 3 list boxes.

My question is: I need to copy this code to another command button and have
it produce results of the Query (DoCmd.OpenQuery "q_AuditDump")

But when I replace this DoCmd the results are not limited to the 3 list boxes.

And I do not get any errors.. As long as I have values picked in the list
boxes...

Note: I did try to add a criteria to the Query
[Forms]![f_AuditDump]![ListProduct] which did not work:

Her is the working code that I need to change to run the query based on the
3 choices.

Dim stDocName As String

Dim stAreaList As String
Dim stProductList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant

stDocName = "r_AuditDump"
stAreaList = ""
stProductList = ""


'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[TR_GBU] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[TR_PRODUCT] " & stProductList & " And "
End If

'removes the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' for debugging
'MsgBox stLinkCriteria
'-------------------------

'open report in preview mode AND send it email
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Exit_cmdViewDump:
Exit Sub

Err_cmdViewDump_Click:
///////////////////// END oF CODE//////////////////
 
G

Guest

You can't as far as I'm aware, the openQuery method won't obviously allow for
a Where argument, and you can't put in a field criteria based on a
multi-select list box, which leaves either opening a form which displays the
query data in datasheet view to *replicate* a query of to construct a new
query entirely in code.

probably not what you wanted to hear, sorry,

TonyT..

Dan @BCBS said:
The code below works perfect - it produces a report and also creates an email
based on choices from 3 list boxes.

My question is: I need to copy this code to another command button and have
it produce results of the Query (DoCmd.OpenQuery "q_AuditDump")

But when I replace this DoCmd the results are not limited to the 3 list boxes.

And I do not get any errors.. As long as I have values picked in the list
boxes...

Note: I did try to add a criteria to the Query
[Forms]![f_AuditDump]![ListProduct] which did not work:

Her is the working code that I need to change to run the query based on the
3 choices.

Dim stDocName As String

Dim stAreaList As String
Dim stProductList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant

stDocName = "r_AuditDump"
stAreaList = ""
stProductList = ""


'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[TR_GBU] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[TR_PRODUCT] " & stProductList & " And "
End If

'removes the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' for debugging
'MsgBox stLinkCriteria
'-------------------------

'open report in preview mode AND send it email
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Exit_cmdViewDump:
Exit Sub

Err_cmdViewDump_Click:
///////////////////// END oF CODE//////////////////
Click to expand...
 
G

Guest

That's what I was thinking, but the problem is that I need to send the data
to an Excel sheet. I cannot do that with a form or report....

Suggestions??


TonyT said:
You can't as far as I'm aware, the openQuery method won't obviously allow for
a Where argument, and you can't put in a field criteria based on a
multi-select list box, which leaves either opening a form which displays the
query data in datasheet view to *replicate* a query of to construct a new
query entirely in code.

probably not what you wanted to hear, sorry,

TonyT..

Dan @BCBS said:
The code below works perfect - it produces a report and also creates an email
based on choices from 3 list boxes.

My question is: I need to copy this code to another command button and have
it produce results of the Query (DoCmd.OpenQuery "q_AuditDump")

But when I replace this DoCmd the results are not limited to the 3 list boxes.

And I do not get any errors.. As long as I have values picked in the list
boxes...

Note: I did try to add a criteria to the Query
[Forms]![f_AuditDump]![ListProduct] which did not work:

Her is the working code that I need to change to run the query based on the
3 choices.

Dim stDocName As String

Dim stAreaList As String
Dim stProductList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant

stDocName = "r_AuditDump"
stAreaList = ""
stProductList = ""


'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[TR_GBU] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[TR_PRODUCT] " & stProductList & " And "
End If

'removes the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' for debugging
'MsgBox stLinkCriteria
'-------------------------

'open report in preview mode AND send it email
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Exit_cmdViewDump:
Exit Sub

Err_cmdViewDump_Click:
///////////////////// END oF CODE//////////////////
Click to expand...
Click to expand...
 
G

Guest

NEVERMIND!!!

I just realized I can do this:
DoCmd.OutputTo acOutputQuery, "q_AuditDump", acFormatXLS,
"H:\DataDump\AGSData.xls", True

Thanks



Dan @BCBS said:
That's what I was thinking, but the problem is that I need to send the data
to an Excel sheet. I cannot do that with a form or report....

Suggestions??


TonyT said:
You can't as far as I'm aware, the openQuery method won't obviously allow for
a Where argument, and you can't put in a field criteria based on a
multi-select list box, which leaves either opening a form which displays the
query data in datasheet view to *replicate* a query of to construct a new
query entirely in code.

probably not what you wanted to hear, sorry,

TonyT..

Dan @BCBS said:
The code below works perfect - it produces a report and also creates an email
based on choices from 3 list boxes.

My question is: I need to copy this code to another command button and have
it produce results of the Query (DoCmd.OpenQuery "q_AuditDump")

But when I replace this DoCmd the results are not limited to the 3 list boxes.

And I do not get any errors.. As long as I have values picked in the list
boxes...

Note: I did try to add a criteria to the Query
[Forms]![f_AuditDump]![ListProduct] which did not work:

Her is the working code that I need to change to run the query based on the
3 choices.

Dim stDocName As String

Dim stAreaList As String
Dim stProductList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant

stDocName = "r_AuditDump"
stAreaList = ""
stProductList = ""


'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[TR_GBU] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[TR_PRODUCT] " & stProductList & " And "
End If

'removes the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' for debugging
'MsgBox stLinkCriteria
'-------------------------

'open report in preview mode AND send it email
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Exit_cmdViewDump:
Exit Sub

Err_cmdViewDump_Click:
///////////////////// END oF CODE//////////////////
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi again Dan,

surely that just outputs the entire un-filtered query values into your excel
spreadsheet, not the filtered version going to your report?
There is the OutputTo acOutputForm that would allow you to open a datasheet
view form using the same stLinkCriteria (hidden if prefered) and then
outputting it's 'filtered' results.

TonyT..


Dan @BCBS said:
NEVERMIND!!!

I just realized I can do this:
DoCmd.OutputTo acOutputQuery, "q_AuditDump", acFormatXLS,
"H:\DataDump\AGSData.xls", True

Thanks



Dan @BCBS said:
That's what I was thinking, but the problem is that I need to send the data
to an Excel sheet. I cannot do that with a form or report....

Suggestions??


TonyT said:
You can't as far as I'm aware, the openQuery method won't obviously allow for
a Where argument, and you can't put in a field criteria based on a
multi-select list box, which leaves either opening a form which displays the
query data in datasheet view to *replicate* a query of to construct a new
query entirely in code.

probably not what you wanted to hear, sorry,

TonyT..

:

The code below works perfect - it produces a report and also creates an email
based on choices from 3 list boxes.

My question is: I need to copy this code to another command button and have
it produce results of the Query (DoCmd.OpenQuery "q_AuditDump")

But when I replace this DoCmd the results are not limited to the 3 list boxes.

And I do not get any errors.. As long as I have values picked in the list
boxes...

Note: I did try to add a criteria to the Query
[Forms]![f_AuditDump]![ListProduct] which did not work:

Her is the working code that I need to change to run the query based on the
3 choices.

Dim stDocName As String

Dim stAreaList As String
Dim stProductList As String
Dim stLinkCriteria As String

'first time thru loop?
Dim FirstTime As Boolean

Dim stArea As Variant
Dim stProduct As Variant

stDocName = "r_AuditDump"
stAreaList = ""
stProductList = ""


'dates
If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
End If

'get areas selected in ListArea
FirstTime = True
For Each stArea In ListArea.ItemsSelected
If FirstTime Then
stAreaList = "In('" & ListArea.ItemData(stArea) & "'"
FirstTime = False
Else
stAreaList = stAreaList & ",'" & ListArea.ItemData(stArea) & "'"
End If
Next stArea
If Not FirstTime Then
stAreaList = stAreaList & ")"
End If

'get products in ListProduct
FirstTime = True
For Each stProduct In ListProduct.ItemsSelected
If FirstTime Then
stProductList = "In('" & ListProduct.ItemData(stProduct) & "'"
FirstTime = False
Else
stProductList = stProductList & ",'" & ListProduct.ItemData(stProduct) & "'"
End If
Next stProduct
If Not FirstTime Then
stProductList = stProductList & ")"
End If


'create criteria string
'stAreaList
If Len(Trim(Nz(stAreaList, ""))) > 0 Then
stLinkCriteria = "[TR_GBU] " & stAreaList & " And "
End If

'stProductList
If Len(Trim(Nz(stProductList, ""))) > 0 Then
stLinkCriteria = stLinkCriteria & "[TR_PRODUCT] " & stProductList & " And "
End If

'removes the last 'And' and spaces
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 5)

'-------------------------
' for debugging
'MsgBox stLinkCriteria
'-------------------------

'open report in preview mode AND send it email
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
'DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Exit_cmdViewDump:
Exit Sub

Err_cmdViewDump_Click:
///////////////////// END oF CODE//////////////////
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type Mismatch 5
DoCmd.SendObject 2
3 list boxes 1 answer 9
3 choices 3
Combine 3 List box Choices 1
Combo Box 15
Multi options 25
records per list 4

Top