My Selected print in the list box does not work

F

Frank Situmorang

Hello,

I appreciate for any help . My select print from the list box does not work,
here is my Module and VBA in the Command button.

The error says when click the command button is unable to call the
module"BuildWhereCondition" , it saya " Expect the valiable not the module.

while all the module and VBA in the command button, I learned from this
forum and I made like it.

This my module and VBA:
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control

Set ctl = Forms!frmSelectPrint!(strControl) 'replace with your form name
that has the listbox

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhereLst = strWhereLst & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select

This is my command button VBA:
Private Sub SelectedPrint_Click()
Dim strWhere As String
Dim stDocName As String
stDocName = "Update Data Keanggotaan"
strWhere = BuildWhereCondition("Hsehold_listbox") 'this calls the function
and if no items are selected,
' there is no where condition, so all items print, else selected items print
'next line follows the function call:
strWhere = "[Hsehold_listbox] " & strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub
Thanks in advance

Frank
 
F

Frank Situmorang

hello, MVP I have followed your instruction, but the problem saying that it
expected variable not a module.

This is my complete module and VBA in the on click of command seleted print:
Option Compare Database
Option Explicit
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control

Set ctl = Forms!frmSelectPrint!(strControl) 'replace with your form name
that has the listbox

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhereLst = strWhereLst & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
BuildWhereCondition = strWhereLst
Set ctl = Nothing

End Function
HERE IS THE VBA OF A COMMAND:
Private Sub SelectedPrint_Click()
Dim StrWhere As String
Dim stDocName As String
stDocName = "Update Data Keanggotaan"
StrWhere = BuildWhereCondition("Hsehold_listbox") 'this calls the function
and if no items are selected,
' there is no where condition, so all items print, else selected items print
'next line follows the function call:
StrWhere = "[Hsehold_listbox] " & StrWhere
DoCmd.OpenReport stDocName, acPreview, , StrWhere
End Sub

It stucked in BuildWhereCondition

I appreciate your help.

Frank



Hello,

I appreciate for any help . My select print from the list box does not work,
here is my Module and VBA in the Command button.

The error says when click the command button is unable to call the
module"BuildWhereCondition" , it saya " Expect the valiable not the module.

while all the module and VBA in the command button, I learned from this
forum and I made like it.

This my module and VBA:
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control

Set ctl = Forms!frmSelectPrint!(strControl) 'replace with your form name
that has the listbox

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhereLst = strWhereLst & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select

This is my command button VBA:
Private Sub SelectedPrint_Click()
Dim strWhere As String
Dim stDocName As String
stDocName = "Update Data Keanggotaan"
strWhere = BuildWhereCondition("Hsehold_listbox") 'this calls the function
and if no items are selected,
' there is no where condition, so all items print, else selected items print
'next line follows the function call:
strWhere = "[Hsehold_listbox] " & strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub
Thanks in advance

Frank
Click to expand...

Hi,

Except missing "End Function" bellow these lines:
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
Click to expand...
MISSING: End Function

I don't see any other errors.

Add in each module above any Sub or Function text "Option
Explicit" (without quotes) and click VBA menu Tools > Compile. See if
it reports any errors. For example:

Option Compare Database
Option Explicit

Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report

etc.

Regards,
Branislav Mihaljev
Microsoft Access MVP
Click to expand...
 
M

[MVP]

hello, MVP I have followed your instruction, but the problem saying that it
expected variable not a module.

This is my complete module and VBA in the on click of command seleted print:
Option Compare Database
Option Explicit
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control

Set ctl = Forms!frmSelectPrint!(strControl) 'replace with your form name
that has the listbox

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("

    With ctl
        For Each varItem In .ItemsSelected
            strWhereLst = strWhereLst & "'" & .ItemData(varItem) & "', "
            Next varItem
    End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
BuildWhereCondition = strWhereLst
Set ctl = Nothing

End Function
HERE IS THE VBA OF A COMMAND:
Private Sub SelectedPrint_Click()
Dim StrWhere As String
Dim stDocName As String
stDocName = "Update Data Keanggotaan"
StrWhere = BuildWhereCondition("Hsehold_listbox")  'this calls the function
and if no items are selected,
' there is no where condition, so all items print, else selected items print
'next line follows the function call:
StrWhere = "[Hsehold_listbox] " & StrWhere
DoCmd.OpenReport stDocName, acPreview, , StrWhere
End Sub

It stucked in BuildWhereCondition

I appreciate your help.

Frank

Hello,
I appreciate for any help . My select print from the list box does not work,
here is my Module and VBA in the Command button.
The error says when click the command button is unable to call the
module"BuildWhereCondition" , it saya " Expect the valiable not the module.
while all the module and VBA in the command button, I learned from this
forum and I made like it.
This my module and VBA:
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control
Set ctl = Forms!frmSelectPrint!(strControl) 'replace with your formname
that has the listbox
Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("
    With ctl
        For Each varItem In .ItemsSelected
            strWhereLst = strWhereLst & "'" & .ItemData(varItem) & "', "
            Next varItem
    End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
This is my command button VBA:
Private Sub SelectedPrint_Click()
Dim strWhere As String
Dim stDocName As String
stDocName = "Update Data Keanggotaan"
strWhere = BuildWhereCondition("Hsehold_listbox") 'this calls the function
and if no items are selected,
' there is no where condition, so all items print, else selected items print
'next line follows the function call:
strWhere = "[Hsehold_listbox] " & strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub
Thanks in advance
Frank
Click to expand...
Hi,
Click to expand...

Except missing "End Function" bellow these lines:
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
Click to expand...
MISSING: End Function
Click to expand...
I don't see any other errors.
Click to expand...
Add in each module above any Sub or Function text "Option
Explicit" (without quotes) and click VBA menu Tools > Compile. See if
it reports any errors. For example:
Click to expand...
Option Compare Database
Option Explicit
Click to expand...
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report

Regards,
Branislav Mihaljev
Microsoft Access MVP
Click to expand...
Click to expand...

Hi,

Well, it works for me, code appears to be working. I assume this is
not the only code you have in your DB. Did you check the rest? Also
you can call BildWhereCondition with parameter and run code line by
line (if possible). Add breakpoint on the first line of the function
and run it from Immediate window with:

call buildwherecondition("Hsehold_listbox")

Regards,
Branislav Mihaljev
Microsoft Access MVP
 
F

Frank Situmorang

Thanks Branislav, it works now for me. The problem is that the name of the
module should be different with the name of the function, so I changed the
name to be modBuildWhereCondition.

also I made a change the name of the field.

Thanks for your help.

Frank

hello, MVP I have followed your instruction, but the problem saying that it
expected variable not a module.

This is my complete module and VBA in the on click of command seleted print:
Option Compare Database
Option Explicit
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control

Set ctl = Forms!frmSelectPrint!(strControl) 'replace with your form name
that has the listbox

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhereLst = strWhereLst & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
BuildWhereCondition = strWhereLst
Set ctl = Nothing

End Function
HERE IS THE VBA OF A COMMAND:
Private Sub SelectedPrint_Click()
Dim StrWhere As String
Dim stDocName As String
stDocName = "Update Data Keanggotaan"
StrWhere = BuildWhereCondition("Hsehold_listbox") 'this calls the function
and if no items are selected,
' there is no where condition, so all items print, else selected items print
'next line follows the function call:
StrWhere = "[Hsehold_listbox] " & StrWhere
DoCmd.OpenReport stDocName, acPreview, , StrWhere
End Sub

It stucked in BuildWhereCondition

I appreciate your help.

Frank

On Aug 31, 11:35 am, Frank Situmorang
Hello,
Click to expand...
I appreciate for any help . My select print from the list box does not work,
here is my Module and VBA in the Command button.
Click to expand...
The error says when click the command button is unable to call the
module"BuildWhereCondition" , it saya " Expect the valiable not the module.
Click to expand...
while all the module and VBA in the command button, I learned from this
forum and I made like it.
Click to expand...
This my module and VBA:
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report
Dim varItem As Variant
Dim strWhereLst As String
Dim ctl As Control
Click to expand...
Set ctl = Forms!frmSelectPrint!(strControl) 'replace with your form name
that has the listbox
Click to expand...
Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhereLst = ""
Case 1 'Only One Selected
strWhereLst = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhereLst = " IN ("
Click to expand...
With ctl
For Each varItem In .ItemsSelected
strWhereLst = strWhereLst & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
Click to expand...
This is my command button VBA:
Private Sub SelectedPrint_Click()
Dim strWhere As String
Dim stDocName As String
stDocName = "Update Data Keanggotaan"
strWhere = BuildWhereCondition("Hsehold_listbox") 'this calls the function
and if no items are selected,
' there is no where condition, so all items print, else selected items print
'next line follows the function call:
strWhere = "[Hsehold_listbox] " & strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub
Thanks in advance
Frank
Click to expand...
Hi,
Click to expand...

Except missing "End Function" bellow these lines:
Click to expand...
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
MISSING: End Function
Click to expand...
I don't see any other errors.
Click to expand...
Add in each module above any Sub or Function text "Option
Explicit" (without quotes) and click VBA menu Tools > Compile. See if
it reports any errors. For example:
Click to expand...
Option Compare Database
Option Explicit
Click to expand...
Public Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the report

Regards,
Branislav Mihaljev
Microsoft Access MVP
Click to expand...
Click to expand...

Hi,

Well, it works for me, code appears to be working. I assume this is
not the only code you have in your DB. Did you check the rest? Also
you can call BildWhereCondition with parameter and run code line by
line (if possible). Add breakpoint on the first line of the function
and run it from Immediate window with:

call buildwherecondition("Hsehold_listbox")

Regards,
Branislav Mihaljev
Microsoft Access MVP
Click to expand...
 
M

[MVP]

I suspect Steve is right! Access gives all kinds of odd error messages ifthe
function name and the name of the module that holds it are identical. It
confuses the devil out of the Access Gnomes!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200909/1
Click to expand...

Agree. So the one should stick to the naming convention. In this case
I will rename function from "BuildWhereCondition" to
"fBuildWhereCondition" and module name to "modBuildWhereCondition".

Regards,
Branislav Mihaljev
Microsoft Access MVP
 

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

Run select query from VBA code where criteria comes from a multi select ListBox 2
Syntax Error (missing operator) in Query Expresison 5
Multi-Select List Box as Query Parameter 1
Using Variable as criteria 4
List Box Selection 3
how to refer to all rows in a list box 2
List Box Coding Problem 1
how to use list box contents as query criterion 2

Top