OpenArgs problem

  • Thread starter Thread starter Billy B
  • Start date Start date
B

Billy B

I have an unbound form with a listbox with the following settings:
Row Source Type = Table/Query
Row Source = SELECT DISTINCT Code FROM [TEST Master] WHERE Code>=0 ORDER BY
Code;
Bound column 1
Multiselect is Simple
The field value for code in the table is number.

The user selects the codes they want from the list box and then I want to
open the report rptTesting displaying only the data for the code numbers
selected in the list box.


Private Sub cmdListboxOfQueries_Click()
Dim intCount As Integer
Dim varItem As
Dim intLength As Integer 'To determine length of final string
'then delete the trailing " OR "

Dim strTblField As String
Dim strOR As String
Dim strFinalString As Variant

strTblField = "
Code: 
 = """
strConcChar = " & "
strOR = "' Or '"

intCount = 0
'Using the ItemsSelected Property
With lstCodeNumbers
If .MultiSelect = 0 Then
txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strTblField & " " & .Column(0, varItem) & strOR
txtSelected = txtSelected & strList
strList = ""
intCount = intCount + 1
Next varItem
End If
intLength = Len(txtSelected)                    'length of string
txtSelected = Left(txtSelected, intLength - 4)  'delete the right 4
characters

'**************
'For testing show string in the textbox of the form
Me.txtFinalLblString.SetFocus
Me.txtFinalLblString.Text = txtSelected

'**************

strFinalString = txtSelected
End With


DoCmd.OpenReport "rptTesting", acViewPreview, , , , strFinalString

‘Open Event code for report
‘Get syntax error
Private Sub Report_Open(Cancel As Integer)
'Me.OpenArgs
MsgBox Me.OpenArgs
'DoCmd.OpenForm "frmSortCodes", , , , , acDialog
Me.Filter = OpenArgs
'Me.FilterOn
End Sub

I get a syntax error. I have been trying to get the opening args to work now
for three days. Any help would be greatly appreciated.

Thank you.
 
Instead of trying to filter the report, use strFinalString as the where
condition to open the report, similar to the way we do it with forms.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Billy B said:
I have an unbound form with a listbox with the following settings:
Row Source Type = Table/Query
Row Source = SELECT DISTINCT Code FROM [TEST Master] WHERE Code>=0 ORDER
BY
Code;
Bound column 1
Multiselect is Simple
The field value for code in the table is number.

The user selects the codes they want from the list box and then I want to
open the report rptTesting displaying only the data for the code numbers
selected in the list box.


Private Sub cmdListboxOfQueries_Click()
Dim intCount As Integer
Dim varItem As
Dim intLength As Integer 'To determine length of final string
'then delete the trailing " OR "

Dim strTblField As String
Dim strOR As String
Dim strFinalString As Variant

strTblField = "
Code: 
 = """
strConcChar = " & "
strOR = "' Or '"

intCount = 0
'Using the ItemsSelected Property
With lstCodeNumbers
If .MultiSelect = 0 Then
txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strTblField & " " & .Column(0, varItem) & strOR
txtSelected = txtSelected & strList
strList = ""
intCount = intCount + 1
Next varItem
End If
intLength = Len(txtSelected)                    'length of string
txtSelected = Left(txtSelected, intLength - 4)  'delete the right 4
characters

'**************
'For testing show string in the textbox of the form
Me.txtFinalLblString.SetFocus
Me.txtFinalLblString.Text = txtSelected

'**************

strFinalString = txtSelected
End With


DoCmd.OpenReport "rptTesting", acViewPreview, , , , strFinalString

‘Open Event code for report
‘Get syntax error
Private Sub Report_Open(Cancel As Integer)
'Me.OpenArgs
MsgBox Me.OpenArgs
'DoCmd.OpenForm "frmSortCodes", , , , , acDialog
Me.Filter = OpenArgs
'Me.FilterOn
End Sub

I get a syntax error. I have been trying to get the opening args to work
now
for three days. Any help would be greatly appreciated.[/QUOTE]

1. Why pass OpenArgs to filter the report, when you could use the same
filter in the WhereCondition argument to DoCmd.OpenReport.

2. Is the [Code] field numeric or text?  Your rowsource SQL implies that it
is numeric, but your code treats it as if it were text, wrapping each value
in quotes.

3. This line:
[QUOTE]
strTblField = "[Code] = """[/QUOTE]

starts the first code value off with a leading double-quote ("), but later
lines clloses the string with a single-quote (').
Click to expand...
 
In answer to your question, Yes, the data in the table is numeric.

Dirk Goldgar said:
Billy B said:
I have an unbound form with a listbox with the following settings:
Row Source Type = Table/Query
Row Source = SELECT DISTINCT Code FROM [TEST Master] WHERE Code>=0 ORDER
BY
Code;
Bound column 1
Multiselect is Simple
The field value for code in the table is number.

The user selects the codes they want from the list box and then I want to
open the report rptTesting displaying only the data for the code numbers
selected in the list box.


Private Sub cmdListboxOfQueries_Click()
Dim intCount As Integer
Dim varItem As
Dim intLength As Integer 'To determine length of final string
'then delete the trailing " OR "

Dim strTblField As String
Dim strOR As String
Dim strFinalString As Variant

strTblField = "
Code: 
 = """
strConcChar = " & "
strOR = "' Or '"

intCount = 0
'Using the ItemsSelected Property
With lstCodeNumbers
If .MultiSelect = 0 Then
txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strTblField & " " & .Column(0, varItem) & strOR
txtSelected = txtSelected & strList
strList = ""
intCount = intCount + 1
Next varItem
End If
intLength = Len(txtSelected)                    'length of string
txtSelected = Left(txtSelected, intLength - 4)  'delete the right 4
characters

'**************
'For testing show string in the textbox of the form
Me.txtFinalLblString.SetFocus
Me.txtFinalLblString.Text = txtSelected

'**************

strFinalString = txtSelected
End With


DoCmd.OpenReport "rptTesting", acViewPreview, , , , strFinalString

‘Open Event code for report
‘Get syntax error
Private Sub Report_Open(Cancel As Integer)
'Me.OpenArgs
MsgBox Me.OpenArgs
'DoCmd.OpenForm "frmSortCodes", , , , , acDialog
Me.Filter = OpenArgs
'Me.FilterOn
End Sub

I get a syntax error. I have been trying to get the opening args to work
now
for three days. Any help would be greatly appreciated.[/QUOTE]

1. Why pass OpenArgs to filter the report, when you could use the same
filter in the WhereCondition argument to DoCmd.OpenReport.

2. Is the [Code] field numeric or text?  Your rowsource SQL implies that it
is numeric, but your code treats it as if it were text, wrapping each value
in quotes.

3. This line:
[QUOTE]
strTblField = "[Code] = """[/QUOTE]

starts the first code value off with a leading double-quote ("), but later
lines clloses the string with a single-quote (').

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
[/QUOTE]
Click to expand...
Click to expand...
 
I sure do. I would really appreciate it. I have exhausted my knowledge trying
to get it to work so any help would be great. Thanks a bunch.
 
Try this revised version of the calling code, and drop the code in the
report's Open event that is trying to apply a filter.

'------ start of revised code (untested) ------
Private Sub cmdListboxOfQueries_Click()

Dim strFilter As String
Dim varItem As Variant

With lstCodeNumbers
If .MultiSelect = 0 Then
strFilter = "
Code: 
 = " & .Value
Else
For Each varItem In .ItemsSelected
strFilter = strFilter & "," & .ItemData(varItem)
Next varItem

If Len(strFilter) > 0 Then
If .ItemsSelected.Count = 1 Then
strFilter = "[Code] = " & Mid$(strFilter, 2)
Else
strFilter = "[Code] In(" & Mid$(strFilter, 2) & ")"
End If
End If

End With

DoCmd.OpenReport "rptTesting", acViewPreview, _
WhereCondition:=strFilter

End Sub
'------ end of code ------
 
Thank you. My thumbnail with the program just died so I am going to have to
rebuild it to try the code. Hope you don't mind if it is a day or so before I
let you know if it works.

Thank you.
 
Billy B said:
Thank you. My thumbnail with the program just died so I am going to have
to
rebuild it to try the code. Hope you don't mind if it is a day or so
before I
let you know if it works.
Click to expand...

No problem.

Here's a suggestion: to avoid corruption, don't run Access databases from a
thumb drive. It's safer to copy it to the hard disk, run it there, and then
copy it back after you close it. If you do run it from the thumb drive,
make sure you use the "Safely Remove Hardware" tool to force the flash drive
to be completelty closed, and all cached writes completed, before you unplug
the drive.
 
Back
Top