Report value not shown according to listbox value

[Ben Saeed]

Hi guys,

I have a listbox inside name of workers which is coming from table (Workersdetail) on the otherside I have report from the same table which still showing all records of worker, this report execute through the form and by selection of listbox values. Please look into my following codes.


Dim strWhereworker As String
strWhereworker = "workername = Forms![workermenu]!workerslist"<--(Workerlist is the name of listbox)

Select Case Me!reporttoview
Case 3
'DoCmd.OpenReport "workerindividual1", acViewReport, , strWhereworker

Problem is, in the report all workers with related values showing while I want to get only the worker name I select from the listbox, I could not understand where I made mistake. Appreciate if someone help me.

Thanks in advance
Ben

 

[CJ_London]

There are a couple of possibilities

Assuming the value in your lis box is text then you need to include single quotation marks

strWhereworker = "workername = '" & Forms![workermenu]!workerslist & "'"

On the other hand, if your list box has a rowsource along the lines of:

SELECT ID, Workername from workers

and the bound column is 1 then the actual listbox value is numeric (the ID). You can do a number of things to correct:

1. change the bound column of the listbox to 2 (or whichever column the workername is in)

2. change the where sql to

strWhereworker = "workerID = " & Forms![workermenu]!workerslist ' Note: no single quotations because this is a number

3. change the where sql to look at the correct column

strWhereworker = "workerID = '" & Forms![workermenu]!workerslist.column(1) & "'"
Note: despite the first column in listbox being referred to as 1 for the bound column, in the sql the first column is 0

 

[Ben Saeed]

Perfect!!!
Made a mistake with bound columns

Thanks alot with best wishes