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
 
Ad

Advertisements

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

Hi,

Except missing "End Function" bellow these lines:
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
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
 
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

Except missing "End Function" bellow these lines:
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
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

Regards,
Branislav Mihaljev
Microsoft Access MVP

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

Except missing "End Function" bellow these lines:
strWhereLst = Left(strWhereLst, Len(strWhereLst) - 2) & ")"
End Select
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

Regards,
Branislav Mihaljev
Microsoft Access MVP

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
 
Ad

Advertisements

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

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

Top