Open report criteria cause enter parameter value

[DK22]

I have one form which have two multiselect listboxes. They work, but every time when I choose id_organizacija from listbox popup enter parameter value comes up.

Im already tried to put id_organizacija in [ ] but that didnt solve problem.

Any suggestions?Thanks

 Set ctl = Me.lstEmployees
  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
    Set ctl = Me.lstOrganizacija
    For Each varItem In ctl.ItemsSelected
    strWhere2 = strWhere2 & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  strWhere2 = Left(strWhere2, Len(strWhere2) - 1)
  'open the report, restricted to the selected items
   DoCmd.OpenReport "rptUlaz", acViewPreview, , "broj_konta In(" & strWhere & ") AND id_organizacija In(" & strWhere2 & ")"

 

[Mihail]

...AND id_organizacija In(" & strWhere2 &...

This don't look good for me.
While the ID field is, in most cases an AutoNumber and strWhere2 seems to be a string.

Note please that SQL is not a friend of my so it is possible to be wrong in what I say.

 

[DK22]

In my case

broj_konta is number and
id_organizacija is text

I agree with you that problem is probably in this line, but Im not very good in vba.

Thank you for reply

 

[pr2-eugin]

In my case

broj_konta is number and
id_organizacija is text

So how about..

Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem

Set ctl = Me.lstOrganizacija
For Each varItem In ctl.ItemsSelected
    strWhere2 = strWhere2 & "[COLOR=Red][B]'[/B][/COLOR]" & ctl.ItemData(varItem) & "[COLOR=Red][B]',[/B][/COLOR]"
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 1)
strWhere2 = Left(strWhere2, Len(strWhere2) [COLOR=Red][B]- 2[/B][/COLOR])

DoCmd.OpenReport "rptUlaz", acViewPreview, , "broj_konta In(" & strWhere & ") AND id_organizacija In(" & strWhere2 & ")"

 

[Mihail]

In order to debug:

Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem

Debug.Print "strWhere = " & strWhere

Set ctl = Me.lstOrganizacija
For Each varItem In ctl.ItemsSelected
  strWhere2 = strWhere2 & ctl.ItemData(varItem) & ","
Next varItem

Debug.Print "strWhere2 = " & strWhere2

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)   strWhere2 = Left(strWhere2, Len(strWhere2) - 1)

Debug.Print "FinalstrWhere = " & strWhere
Debug.Print
Stop

'open the report, restricted to the selected items
DoCmd.OpenReport "rptUlaz", acViewPreview, , "broj_konta In(" & strWhere & ") AND id_organizacija In(" & strWhere2 & ")"

 

[Mihail]

When the code halt, press CTRL+G and see how the SQL for your clause is.

 

[DK22]

@eugin thank you, but its not work. Still have enter parameter value and now I even cannot combine two multiselectlistboxes for reporting.

 

[DK22]

When the code halt, press CTRL+G and see how the SQL for your clause is.

After running with your code It bring me back in vba window with marked row Stop
and this message in the bottom:

strWhere = 4320,
strWhere2 = TA3,
FinalstrWhere = 4320

strWhere = 4320,
strWhere2 = TA3,
FinalstrWhere = 4320

 

[pr2-eugin]

@eugin thank you, but its not work. Still have enter parameter value and now I even cannot combine two multiselectlistboxes for reporting.

Hmmm.. The other cause for Enter Parameter Value, popping up would be invalid reference to controls.. Make sure the control that is popping up for value is still part of the report, and spelled correctly..

 

[Mihail]

Oh, I'm very sorry. I've omitted one more test:

'open the report, restricted to the selected items
Debug.Print "ReportClause: " & "broj_konta In(" & strWhere & ") AND id_organizacija In(" & strWhere2 & ")"
Debug.Print

'And the STOP statement goes here
Stop

DoCmd.OpenReport "rptUlaz", acViewPreview, , "broj_konta In(" & strWhere & ") AND id_organizacija In(" & strWhere2 & ")"

This last string cause the asks for parameters.

 

[DK22]

@paul If I use another query for this same table and only put id_organizacija listbox in form, query work just fine.

opis_konta and id_organizacija are two listboxes which is adjusted on the same way. opis_konta work perfectly but id_organizacija wont work without enter parameter value popup.

So I belive that problem is in vba where Im not expertise to find a problem.

 

[DK22]

@Mihail thank you, but I still have a problem with stop in vba. Here is a code which I use right now:

Option Compare Database
Option Explicit

Private Sub cmdOpenReport_Click()

  On Error GoTo Err_cmdOpenReport_Click

  Dim strWhere      As String
  Dim strWhere2      As String
  Dim ctl           As Control
  Dim varItem       As Variant

  'make sure a selection has been made
  If Me.lstEmployees.ItemsSelected.Count = 0 Then
    Set ctl = Me.lstOrganizacija
    For Each varItem In ctl.ItemsSelected
    strWhere2 = strWhere2 & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere2 = Left(strWhere2, Len(strWhere2) - 1)
  'open the report, restricted to the selected items
   DoCmd.OpenReport "rptUlaz", acViewPreview, , "id_organizacija IN(" & strWhere2 & ")"

    Exit Sub
     End If
   
   
If Me.lstOrganizacija.ItemsSelected.Count = 0 Then
    Set ctl = Me.lstEmployees
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  'open the report, restricted to the selected items
   DoCmd.OpenReport "rptUlaz", acViewPreview, , "broj_konta IN(" & strWhere & ")"
   
    Exit Sub
  End If


  'add selected values to string

Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem

Debug.Print "strWhere = " & strWhere

Set ctl = Me.lstOrganizacija
For Each varItem In ctl.ItemsSelected
  strWhere2 = strWhere2 & ctl.ItemData(varItem) & ","
Next varItem

Debug.Print "strWhere2 = " & strWhere2

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
strWhere2 = Left(strWhere2, Len(strWhere2) - 1)

Debug.Print "FinalstrWhere = " & strWhere
Debug.Print

'open the report, restricted to the selected items
Debug.Print "ReportClause: " & "broj_konta In(" & strWhere & ") AND id_organizacija In(" & strWhere2 & ")"
Debug.Print

'And the STOP statement goes here
Stop

DoCmd.OpenReport "rptUlaz", acViewPreview, , "broj_konta In(" & strWhere & ") AND id_organizacija In(" & strWhere2 & ")"

Exit_cmdOpenReport_Click:
  Exit Sub

 

[DK22]

@Mihail @Paul thank you both.

I make this thing work with a little play with code which Paul gave me.

So this is how it looks like:

Set ctl = Me.lstEmployees
  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
    Set ctl = Me.lstOrganizacija
    For Each varItem In ctl.ItemsSelected
    strWhere2 = strWhere2 & "'" & ctl.ItemData(varItem) & "',"
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  strWhere2 = Left(strWhere2, Len(strWhere2) - 1)
  'open the report, restricted to the selected items
   DoCmd.OpenReport "rptUlaz", acViewPreview, , "broj_konta In(" & strWhere & ") AND id_organizacija In(" & strWhere2 & ")"