MultiSelect Listbox to Report

[brsawvel]

Hello All,

I appreciate the help on this. I've been asking a lot of you for help on this and some have asked that I attach a sample of my db to display what I'm dealing with.

Just a basic explanation of the db. frmListedAM contains two listboxes (overlapped) and a cmd button.

The reason there are two listboxes with the same data is:

1. lstListedAM is in "multiselect = none"; lstListedAMHidden is in "multiselect = simple" and "visible = no"

2. lstListedAM (on my real db) has a code behind it that sends the selected record to a subform where the same record is displayed in detail vs seeing its basic information in the lsitbox. If I had lstListedAM in other than "multiselect = none", the code sending it to subfrm would not work.

So I currently have a cmd button with "on click" event that tells lstListedAMHidden to select all records in the listbox.

The reason I tell it to select all instead of me manually selecting records to be sent to the report is because lstListedAM and lstListedAMHidden are told which records to display from another cmd button (not on this example). So the records displayed on the listbox have already been filtered.

Kinda long winded. Sorry.

 

[WayneRyan]

B,

Those "=" signs will hurt ya.

Change --> For i = 0 To lstListedAMHidden.ListCount = -1

To --> For i = 0 To lstListedAMHidden.ListCount - 1

Wayne

 

[brsawvel]

That must be a mistake I made in the sample db above. I do have it the right way in my actual db. My bad.

 

[Lister]

Hey, have mod'ed you sample database so the report runs. Have a look at the code behind the run report button, set a break point and step through the code to get a feel for how it works.

Good luck mate.

 

[brsawvel]

Hey Lister,

That's awesome. I've got a little more reading to do on it, but I think it will work on my actual db (not at my personal computer right now so I can't check yet). I tweaked the code to report from the hidden listbox and had to correct the code on the select all button like Wayne referenced.

Thanks for all the help. I'll let you know how it goes on my db.

brsawvel

 

[brsawvel]

It worked fine when I first used it, but after I added a record to the table, I got an Error stating -

Error Number:3075 - Syntax error (missing operator) in query expression '(qryListed.fldAssetID = "a" 0)'.

Any idea what that could be? I looked online to see if I could find an answer and came across this webpage -

http://www.tek-tips.com/viewthread.cfm?qid=1268276&page=9

Is that similar?

 

[Lister]

If all you have done is add records to the table that supplies the list box then nothing can be wrong. As the syntax behind the report button is dynamic and will just keep growing.
Also, this is a system error, not a error with the code, as the code (if you have copied and paste it) has its own error catcher. "On Error Goto ErrHandler"
So, something you have changed or added is causing the error.
You are going to have to debug the application. When the error runs, click Ctrl + Break, Debug and find where you are creating the error.
By the looks of it looks simple enough.

 

[brsawvel]

Ok, I think I fixed it, but I want to make sure it's a complete fix and not a quick fix.

In the code I saw this:

If vVal <> Empty Then
     strHolder = strHolder & "'" & vVal & "'" & " Or qryListed.fldAssetID = "

With that I was getting the error message:

Error Number:3075 - Syntax error (missing operator) in query expression '(qryListed.fldAssetID = "a" 0)'.

I kep thinking that "0" near the end of the error message could be the source of the problem. Then, I noticed there was a space between the equal sign and quote sign at the end of the code. So I deleted the space and the event fired properly.

If vVal <> Empty Then
     strHolder = strHolder & "'" & vVal & "'" & " Or qryListed.fldAssetID ="

Please tell me I figured it out. [wishing]

 

[Lister]

No, that will not be it.
The space is there so it can be joined to strHolder and any subsequent string value that may be joined to the back of the string.
" Or qryListed.fldAssetID = 'a' Or qryListed.fldAssetID = 'c' Or qryListed.fldAssetID = 'd' "
And so on.
Put a break point at the start of the code, click the button and step though the code checking the value of the vairables as you go.
You will find the spot where the error is. If the you jump straight to the error, you have a syntax error you need to get rid of.
Add a ' to the start of any suspect line and click run, step through the code until you are sure that you have the line where the error is being created.
Then work through that line to fix.

I know you are new to vba but there is little I can do without seeing the database.
Let us know how you get on mate.

 

[brsawvel]

Hey Lister,

Sorry I haven't been on for a while. Things have been kind of busy. I promise I'll get a copy of the database on soon so you can see what I'm dealing with.

brsawvel