OpenArgs problem

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.
 
J

Jeanette Cunningham

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
 
D

Dirk Goldgar

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...
 
B

Billy B

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...
 
B

Billy B

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.
 
D

Dirk Goldgar

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 ------
 
B

Billy B

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.
 
D

Dirk Goldgar

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.
 

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

Send Objects Freezes Access on Cancel 3
Pass selected items in list box as a string 3
Email help with ListBox 2
Use data from subform (List Box) on Form, via Command button? 3
List box problem 4
why put #?! 8
proper after ujpdate code to populate form 2
multiselect list box code for filter form 11

Top