Mutliselect Listbox Question

[niuginikiwi]

I have looked in the current discussions and the archives but can not get
anything closer to the problem i have.

I have a database that records Plantings of Vegetables (eg Lettuce,
Cabbages, etc) and the Chemcials and Fertilisers that are applied to the
plantings.

My problem is to have an application of spraying eg Spraying Herbicides on
several different plantings using serveral different Spray Chemicals I have
in stock.

My current setup is like this... I have tblApplication which has a one to
many relationship with tblApplicationDetails, the latar being on the many
side. tbl Application has fields ApplicationID PK, ApplicationDate,
OperationID FK, PlantingDetailsID FK.... and tblApplicationDetails has fields
ApplicationID FK PK, and ProductID FK PK.

I have textbox txtApplicationDate (unbound), combo cboOperation (refers to
list of operations from tblOperations), listbox lstPlantings, and listbox
lstProducts (list of Spray Chemicals) all on an unbound form.

What I would like to acheive is to click a comand button to have new record
inserted into tblApplications ie the PlantingDetailsID field on the number
of plantings I select from the mulitselect listbox lstPlantings and have the
Operation (eg Spraying Herbicide) that I select from cboOperations into
tblApplication, and also insert the value I enter into the txtApplicationDate
field to every each reord that have just been created.

Then I would like to have another button that will insert the ApplicationID
FK PK field in tblApplicationDetails with values that have just been created
in tblApplication and for every ApplicationID inserted, I would like to have
several products (spray chemicals) that I select from the multiselect listbox
lstProducts.

I can master on creating subforms for data entry on a record by record basis
but in my case, it would really help to have all these done on a multiselect
level since we have so many plantings that we spray with different mix of
chemicals everytime ... thus this way, i think we will be able to record
oprations and produce spray schedules on the go as needed....


I'm a newbie on VBA and have spent the last week researching for some help
and resources on that but can not be able to find anything.

I would really appreciate if any of you gurus can give em some help me out
on this.

Thanks

 

[Graham Mandeno]

Hi niuginikiwi

You can do this with two nested loops. Something like this:

(WARNING- untested "air-code")

Dim db as DAO.Database
Dim rsApplication as DAO.Recordset
Dim rsApplicationDetails as DAO.Recordset
Dim vPlanting as variant
Dim vProduct as variant
Dim lApplicationID as long
Set db = CurrentDb
Set rsApplication = db.OpenRecordset("tblApplication")
Set rsApplicationDetails = db.OpenRecordset("tblApplicationDetails")
For Each vPlanting in lstPlantings.ItemsSelected
With rsApplications
.AddNew
!ApplicationDate = txtApplicationDate
!OperationID = cboOperation
!PlantingDetailsID = lstPlantings.ItemData(vPlanting)
lApplicationID = !ApplicationID
.Update
End With
For Each vProduct in lstProducts.ItemsSelected
With rsApplicationDetails
.AddNew
!ApplicationID = lApplicationID
!ProductID = lstProducts.ItemData(vProduct)
.Update
End With
Next vProduct
Next vPlanting
rsApplication.Close
rsApplicationDetails.Close

 

[niuginikiwi]

Opps! Posted as a separate Question above. Sorry...

Thanks Graham, apart from me changing some names for controls and objects. It
worked like magic. Below is the code again u gave me.
"A bit of the old Kiwi Ingenuity"

Now I have a query that incoporates planting information, application of
operations information and the products (spary chemicals) used.

I would like to use the same values supplied from txtApplicationDate,
cboOperations, lstPlantings and lstProducts as parameter values to send the
currently viewed selection to a report.
I may need a different / new command button to for this purpose.

On my report design, I have OperationID header to hold operations info and
PlantingDetailsID Header/Footer to hold selected planting details
and would like to display the selected products on the details section.

Here is the the code that Graham gave me which is working to append records
to my tblApplication and tblApplicationDetails.

Private Sub btnAppend_Click()
Dim db As DAO.Database
Dim rsApplication As DAO.Recordset
Dim rsApplicationDetails As DAO.Recordset
Dim vPlanting As Variant
Dim vProduct As Variant
Dim lApplicationID As Long
Set db = CurrentDb
Set rsApplication = db.OpenRecordset("tblApplications")
Set rsApplicationDetails = db.OpenRecordset("tblApplicationDetails")
For Each vPlanting In lstPlantings.ItemsSelected
With rsApplication
.AddNew
!ApplicationDate = txtApplicationDate
!OperationID = cboOperation
!PlantingDetailsID = lstPlantings.ItemData(vPlanting)
lApplicationID = !ApplicationID
.Update
End With
For Each vProduct In lstProducts.ItemsSelected
With rsApplicationDetails
.AddNew
!ApplicationID = lApplicationID
!ProductID = lstProducts.ItemData(vProduct)
.Update
End With
Next vProduct
Next vPlanting
rsApplication.Close
rsApplicationDetails.Close
End Sub

 

[Graham Mandeno]

I think the real question here is "how do I create a WHERE clause from a
multiselect listbox?"

Here is a function I use to do the job. It works by going through the
..ItemsSelected collection of the listbox (as we did in the other code to
create records) and constructing a string of items in the form "in (a, b,
c)". It also looks after the special cases where zero or one items are
selected. Just paste it into a standard module.

Public Function MultiSelectSQL(ctl As Control, _
Optional Delimiter As String) As String
Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1:
sResult = " = " & Delimiter & .ItemData(.ItemsSelected(0)) & Delimiter
Case Else
sResult = " in ("
For Each vItem In .ItemsSelected
sResult = sResult & Delimiter & .ItemData(vItem) & Delimiter & ","
Next vItem
Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function

To use it, you incorporate its result into a filter string. For example:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation

(The Delimiter argument is needed only if the field in not numeric)

 

[niuginikiwi]

Hi Graham,
I have taken the function MultiSelectSQL as you have provided and have it as
a standard module for itself.
However, I can not get past this bit of code here where I get to use the
function:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation
I keep on getting Compile Error: Expected End of Statement message when I
stick the above code on the onClick event othe button that opens the report.
Am I putting that code in the right place. I have a feeling it might not be
the right place. Where do I use that above Filter statement? Can I get some
more clarification and help on that please?

Thanks so much.

niuginikiwi
Nelson, New Zealand

I think the real question here is "how do I create a WHERE clause from a
multiselect listbox?"

Here is a function I use to do the job. It works by going through the
..ItemsSelected collection of the listbox (as we did in the other code to
create records) and constructing a string of items in the form "in (a, b,
c)". It also looks after the special cases where zero or one items are
selected. Just paste it into a standard module.

Public Function MultiSelectSQL(ctl As Control, _
Optional Delimiter As String) As String
Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1:
sResult = " = " & Delimiter & .ItemData(.ItemsSelected(0)) & Delimiter
Case Else
sResult = " in ("
For Each vItem In .ItemsSelected
sResult = sResult & Delimiter & .ItemData(vItem) & Delimiter & ","
Next vItem
Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function

To use it, you incorporate its result into a filter string. For example:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation

(The Delimiter argument is needed only if the field in not numeric)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Opps! Posted as a separate Question above. Sorry...

Thanks Graham, apart from me changing some names for controls and objects.
It
worked like magic. Below is the code again u gave me.
"A bit of the old Kiwi Ingenuity"

Now I have a query that incoporates planting information, application of
operations information and the products (spary chemicals) used.

I would like to use the same values supplied from txtApplicationDate,
cboOperations, lstPlantings and lstProducts as parameter values to send
the
currently viewed selection to a report.
I may need a different / new command button to for this purpose.

On my report design, I have OperationID header to hold operations info and
PlantingDetailsID Header/Footer to hold selected planting details
and would like to display the selected products on the details section.

Here is the the code that Graham gave me which is working to append
records
to my tblApplication and tblApplicationDetails.

Private Sub btnAppend_Click()
Dim db As DAO.Database
Dim rsApplication As DAO.Recordset
Dim rsApplicationDetails As DAO.Recordset
Dim vPlanting As Variant
Dim vProduct As Variant
Dim lApplicationID As Long
Set db = CurrentDb
Set rsApplication = db.OpenRecordset("tblApplications")
Set rsApplicationDetails = db.OpenRecordset("tblApplicationDetails")
For Each vPlanting In lstPlantings.ItemsSelected
With rsApplication
.AddNew
!ApplicationDate = txtApplicationDate
!OperationID = cboOperation
!PlantingDetailsID = lstPlantings.ItemData(vPlanting)
lApplicationID = !ApplicationID
.Update
End With
For Each vProduct In lstProducts.ItemsSelected
With rsApplicationDetails
.AddNew
!ApplicationID = lApplicationID
!ProductID = lstProducts.ItemData(vProduct)
.Update
End With
Next vProduct
Next vPlanting
rsApplication.Close
rsApplicationDetails.Close
End Sub

 

[niuginikiwi]

Graham,
I also forgot to mention that my fields PlantingDetailsID, ProductID, and
OperationID are all number (autoNumber PKs). How do I adjust the ommision of
delimiter that you have mentioned earlier?

Thanks again,

 

[Graham Mandeno]

Sorry - I missed out the string concatenation operators at the start of the
second and third lines:

Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
& " and ProductID" & MultiSelectSQL( lstProducts ) _
& " and OperationID=" & cboOperation

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
I have taken the function MultiSelectSQL as you have provided and have it
as
a standard module for itself.
However, I can not get past this bit of code here where I get to use the
function:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation
I keep on getting Compile Error: Expected End of Statement message when I
stick the above code on the onClick event othe button that opens the
report.
Am I putting that code in the right place. I have a feeling it might not
be
the right place. Where do I use that above Filter statement? Can I get
some
more clarification and help on that please?

Thanks so much.

niuginikiwi
Nelson, New Zealand

I think the real question here is "how do I create a WHERE clause from a
multiselect listbox?"

Here is a function I use to do the job. It works by going through the
..ItemsSelected collection of the listbox (as we did in the other code to
create records) and constructing a string of items in the form "in (a, b,
c)". It also looks after the special cases where zero or one items are
selected. Just paste it into a standard module.

Public Function MultiSelectSQL(ctl As Control, _
Optional Delimiter As String) As String
Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1:
sResult = " = " & Delimiter & .ItemData(.ItemsSelected(0)) &
Delimiter
Case Else
sResult = " in ("
For Each vItem In .ItemsSelected
sResult = sResult & Delimiter & .ItemData(vItem) & Delimiter &
","
Next vItem
Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function

To use it, you incorporate its result into a filter string. For example:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation

(The Delimiter argument is needed only if the field in not numeric)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Opps! Posted as a separate Question above. Sorry...

Thanks Graham, apart from me changing some names for controls and
objects.
It
worked like magic. Below is the code again u gave me.
"A bit of the old Kiwi Ingenuity"

Now I have a query that incoporates planting information, application
of
operations information and the products (spary chemicals) used.

I would like to use the same values supplied from txtApplicationDate,
cboOperations, lstPlantings and lstProducts as parameter values to send
the
currently viewed selection to a report.
I may need a different / new command button to for this purpose.

On my report design, I have OperationID header to hold operations info
and
PlantingDetailsID Header/Footer to hold selected planting details
and would like to display the selected products on the details section.

Here is the the code that Graham gave me which is working to append
records
to my tblApplication and tblApplicationDetails.

Private Sub btnAppend_Click()
Dim db As DAO.Database
Dim rsApplication As DAO.Recordset
Dim rsApplicationDetails As DAO.Recordset
Dim vPlanting As Variant
Dim vProduct As Variant
Dim lApplicationID As Long
Set db = CurrentDb
Set rsApplication = db.OpenRecordset("tblApplications")
Set rsApplicationDetails = db.OpenRecordset("tblApplicationDetails")
For Each vPlanting In lstPlantings.ItemsSelected
With rsApplication
.AddNew
!ApplicationDate = txtApplicationDate
!OperationID = cboOperation
!PlantingDetailsID = lstPlantings.ItemData(vPlanting)
lApplicationID = !ApplicationID
.Update
End With
For Each vProduct In lstProducts.ItemsSelected
With rsApplicationDetails
.AddNew
!ApplicationID = lApplicationID
!ProductID = lstProducts.ItemData(vProduct)
.Update
End With
Next vProduct
Next vPlanting
rsApplication.Close
rsApplicationDetails.Close
End Sub

 

[Graham Mandeno]

I was assuming these were numeric. The optional delimiter is needed only if
the field is non-numeric.

 

[niuginikiwi]

Oh thanks Graham,
This clears that one up...

--
niuginikiwi
Nelson, New Zealand

I was assuming these were numeric. The optional delimiter is needed only if
the field is non-numeric.

 

[niuginikiwi]

Waawww! Quick Reply! Thanks Graham,
I am still having problem figuring out how to apply that filter.
Can you elaborate further with more details on how I may go about applying
this filter using the function. Do i use it thru the command button? Or on
Open Report event of the report itself?

Thanks again

PS: U may also notice that I have started a new thread using AllenBrowne's
example. That was before I discovered that you have answered my question
already.


--
niuginikiwi
Nelson, New Zealand

Sorry - I missed out the string concatenation operators at the start of the
second and third lines:

Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
& " and ProductID" & MultiSelectSQL( lstProducts ) _
& " and OperationID=" & cboOperation

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
I have taken the function MultiSelectSQL as you have provided and have it
as
a standard module for itself.
However, I can not get past this bit of code here where I get to use the
function:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation
I keep on getting Compile Error: Expected End of Statement message when I
stick the above code on the onClick event othe button that opens the
report.
Am I putting that code in the right place. I have a feeling it might not
be
the right place. Where do I use that above Filter statement? Can I get
some
more clarification and help on that please?

Thanks so much.

niuginikiwi
Nelson, New Zealand

I think the real question here is "how do I create a WHERE clause from a
multiselect listbox?"

Here is a function I use to do the job. It works by going through the
..ItemsSelected collection of the listbox (as we did in the other code to
create records) and constructing a string of items in the form "in (a, b,
c)". It also looks after the special cases where zero or one items are
selected. Just paste it into a standard module.

Public Function MultiSelectSQL(ctl As Control, _
Optional Delimiter As String) As String
Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1:
sResult = " = " & Delimiter & .ItemData(.ItemsSelected(0)) &
Delimiter
Case Else
sResult = " in ("
For Each vItem In .ItemsSelected
sResult = sResult & Delimiter & .ItemData(vItem) & Delimiter &
","
Next vItem
Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function

To use it, you incorporate its result into a filter string. For example:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation

(The Delimiter argument is needed only if the field in not numeric)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Opps! Posted as a separate Question above. Sorry...

Thanks Graham, apart from me changing some names for controls and
objects.
It
worked like magic. Below is the code again u gave me.
"A bit of the old Kiwi Ingenuity"

Now I have a query that incoporates planting information, application
of
operations information and the products (spary chemicals) used.

I would like to use the same values supplied from txtApplicationDate,
cboOperations, lstPlantings and lstProducts as parameter values to send
the
currently viewed selection to a report.
I may need a different / new command button to for this purpose.

On my report design, I have OperationID header to hold operations info
and
PlantingDetailsID Header/Footer to hold selected planting details
and would like to display the selected products on the details section.

Here is the the code that Graham gave me which is working to append
records
to my tblApplication and tblApplicationDetails.

Private Sub btnAppend_Click()
Dim db As DAO.Database
Dim rsApplication As DAO.Recordset
Dim rsApplicationDetails As DAO.Recordset
Dim vPlanting As Variant
Dim vProduct As Variant
Dim lApplicationID As Long
Set db = CurrentDb
Set rsApplication = db.OpenRecordset("tblApplications")
Set rsApplicationDetails = db.OpenRecordset("tblApplicationDetails")
For Each vPlanting In lstPlantings.ItemsSelected
With rsApplication
.AddNew
!ApplicationDate = txtApplicationDate
!OperationID = cboOperation
!PlantingDetailsID = lstPlantings.ItemData(vPlanting)
lApplicationID = !ApplicationID
.Update
End With
For Each vProduct In lstProducts.ItemsSelected
With rsApplicationDetails
.AddNew
!ApplicationID = lApplicationID
!ProductID = lstProducts.ItemData(vProduct)
.Update
End With
Next vProduct
Next vPlanting
rsApplication.Close
rsApplicationDetails.Close
End Sub

 

[Graham Mandeno]

You can use the filter string for the WhereCondition (4th argument) for the
OpenForm or OpenReport method:

Dim strFilter as string
strFilter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
& " and ProductID" & MultiSelectSQL( lstProducts ) _
& " and OperationID=" & cboOperation
DoCmd.OpenReport "YourReport", , , strFilter

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Waawww! Quick Reply! Thanks Graham,
I am still having problem figuring out how to apply that filter.
Can you elaborate further with more details on how I may go about applying
this filter using the function. Do i use it thru the command button? Or on
Open Report event of the report itself?

Thanks again

PS: U may also notice that I have started a new thread using AllenBrowne's
example. That was before I discovered that you have answered my question
already.


--
niuginikiwi
Nelson, New Zealand

Sorry - I missed out the string concatenation operators at the start of
the
second and third lines:

Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
& " and ProductID" & MultiSelectSQL( lstProducts ) _
& " and OperationID=" & cboOperation

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
I have taken the function MultiSelectSQL as you have provided and have
it
as
a standard module for itself.
However, I can not get past this bit of code here where I get to use
the
function:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation
I keep on getting Compile Error: Expected End of Statement message when
I
stick the above code on the onClick event othe button that opens the
report.
Am I putting that code in the right place. I have a feeling it might
not
be
the right place. Where do I use that above Filter statement? Can I get
some
more clarification and help on that please?

Thanks so much.

niuginikiwi
Nelson, New Zealand


:

I think the real question here is "how do I create a WHERE clause from
a
multiselect listbox?"

Here is a function I use to do the job. It works by going through the
..ItemsSelected collection of the listbox (as we did in the other code
to
create records) and constructing a string of items in the form "in (a,
b,
c)". It also looks after the special cases where zero or one items
are
selected. Just paste it into a standard module.

Public Function MultiSelectSQL(ctl As Control, _
Optional Delimiter As String) As String
Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1:
sResult = " = " & Delimiter & .ItemData(.ItemsSelected(0)) &
Delimiter
Case Else
sResult = " in ("
For Each vItem In .ItemsSelected
sResult = sResult & Delimiter & .ItemData(vItem) & Delimiter &
","
Next vItem
Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function

To use it, you incorporate its result into a filter string. For
example:
Me.Filter = "PlantingDetailsID" & MultiSelectSQL( lstPlantings ) _
" and ProductID" & MultiSelectSQL( lstProducts ) _
" and OperationID=" & cboOperation

(The Delimiter argument is needed only if the field in not numeric)
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Opps! Posted as a separate Question above. Sorry...

Thanks Graham, apart from me changing some names for controls and
objects.
It
worked like magic. Below is the code again u gave me.
"A bit of the old Kiwi Ingenuity"

Now I have a query that incoporates planting information,
application
of
operations information and the products (spary chemicals) used.

I would like to use the same values supplied from
txtApplicationDate,
cboOperations, lstPlantings and lstProducts as parameter values to
send
the
currently viewed selection to a report.
I may need a different / new command button to for this purpose.

On my report design, I have OperationID header to hold operations
info
and
PlantingDetailsID Header/Footer to hold selected planting details
and would like to display the selected products on the details
section.

Here is the the code that Graham gave me which is working to append
records
to my tblApplication and tblApplicationDetails.

Private Sub btnAppend_Click()
Dim db As DAO.Database
Dim rsApplication As DAO.Recordset
Dim rsApplicationDetails As DAO.Recordset
Dim vPlanting As Variant
Dim vProduct As Variant
Dim lApplicationID As Long
Set db = CurrentDb
Set rsApplication = db.OpenRecordset("tblApplications")
Set rsApplicationDetails = db.OpenRecordset("tblApplicationDetails")
For Each vPlanting In lstPlantings.ItemsSelected
With rsApplication
.AddNew
!ApplicationDate = txtApplicationDate
!OperationID = cboOperation
!PlantingDetailsID = lstPlantings.ItemData(vPlanting)
lApplicationID = !ApplicationID
.Update
End With
For Each vProduct In lstProducts.ItemsSelected
With rsApplicationDetails
.AddNew
!ApplicationID = lApplicationID
!ProductID = lstProducts.ItemData(vProduct)
.Update
End With
Next vProduct
Next vPlanting
rsApplication.Close
rsApplicationDetails.Close
End Sub

 

[niuginikiwi]

Hi Graham,
I don't think the report is getting filtered at all.
To briefly start again. I have the MultiSelectSQL function which u gave me
which is being called by this Open Report method here:

Private Sub cmdRunReport_Click()

Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strWhere As String
strWhere = "PlantingDetailsID" & MultiSelectSQL(lstPlantings) _
& " and ProductID" & MultiSelectSQL(lstProducts) _
& " and OperationID=" & cboOperation _
& "and ApplicationDate = " & Format(Me.txtApplicationDate,
conDateFormat) & ")"

DoCmd.OpenReport "rptSpray", acPreview, strWher

End Sub

I also had a date filed which I added to what you gave me as shown above.

But when I run the report with the onclick event above, the reports every
record that is supposed to be on record. When i switched to design view of
report and look at the data tab of the reports property, there is no filter
string (report open args). I even turned the Filter On property to Yes but
there is no change to data displayed.

Sorry bout being confused here and asking many questions. Now its just
beyond my realm of solutions. Also below is the query the report is based on
if it will be of any help to making things clear.

SELECT tblOperations.OperationName, tblApplications.ApplicationDate,
tblApplications.EmployeeID, tblApplications.PlantingDetailsID,
tblApplicationDetails.ProductID, tblApplicationDetails.ApplicationID,
tblOperations.OpertionTypeID, tblProducts.AppRate,
qryPlantingCombined.Length, qryPlantingCombined.Beds,
(([Length]*1.5)*[Beds]*[AppRate])/1000 AS Qty, tblApplications.OperationID
FROM tblProducts INNER JOIN (tblOperations INNER JOIN ((qryPlantingCombined
INNER JOIN tblApplications ON qryPlantingCombined.PlantingDetailsID =
tblApplications.PlantingDetailsID) INNER JOIN tblApplicationDetails ON
tblApplications.ApplicationID = tblApplicationDetails.ApplicationID) ON
tblOperations.OperationID = tblApplications.OperationID) ON
tblProducts.ProductID = tblApplicationDetails.ProductID
WHERE (((tblOperations.OpertionTypeID) In (1)));

 

[Graham Mandeno]

Hi niugini

Two problems:

1. you need another space before the final "and" you have added (to separate
it from the cboOperation value):

& " and ApplicationDate = " & Format(Me.txtApplicationDate,

2. WhereCondition is the 4th argument. You are passing it as the 3rd - you
need an extra comma:

DoCmd.OpenReport "rptSpray", acPreview, , strWhere

 

[niuginikiwi]

Hi Graham,
Thanks so much for pointing out the mistakes in the code. Without your help,
I would have otherwise never figured it out myself.
I corrected as you have pointed out and its all at peace for now.
Your kind help is very much appreciated.

 

[Graham Mandeno]

My pleasure, mate

Give my regards to "Sunny Nelson"