Creating a Report from a Form with Listboxes in Microsoft Access 2

[CSatGal]

I'm attempting to design a form for an Access database that will allow users to choose one or more vlaues from at least 3 seperate listboxes (ie Product Line, Agent, Product, etc.). I know how to put listboxes into forms, but I have no idea how to take those forms and create a customized report based on the selected values... help!! I'm also somewhat of a VBA rookie, so please help me out with how to write the necessary code!!

Thanks!

 

[Phil4tektips]

Hi

The code is very simple to use, you've made this feature very simple to implement. Many congratulations and thankyous to FancyPrairie. This is going to be a very handy thread to direct people to, will answer any problems with this topic I think!

The method I had employed before meant I could have a label on the Report with the users selections for the filter. How can I do this now I have adopted your method?

Many thanks -Phil4tektips-

 

[CSatGal]

Hi FancyPrairie

Thanks once again!!

Unfortunately, I'm still an Access Dummy and i'm having trouble. I named the controls as you specified, and put the =Process... into the OnClick events of the labels, and then cut and paste the code, but i'm getting an error:

"The expression OnClick you entered as the event property setting produced the following error:
The expression you entered has a function name that Microsoft Office Access can't find.
*the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*there may have been an error evaluating the function, event, or macro."

What have i done wrong??

 

[FancyPrairie]

Phil4tektips, for my report header page, I simply create a text box (for each list box on the form) and set the CanGrow property to true. And I place the text boxes in the ReportHeader section of the report. In the OnFormat event, I fill the text boxes with what's selected in the list boxes. Something like this:

   Dim strLB as String
   Dim varItem as Variant
   Dim frm as Form

   Set frm = Forms!YourFormName
   strLB = vbNullString

   If (frm!lstBox.ItemsSelected.Count = 0) then
       strLB = "(All Employees")
   Else
       For each varItem in frm!lstBox.ItemsSelected
           strLB = strLB & frm!lstBox.Column(1,varItem) & vbCrLf
       Next
   End If

   txtBox = strLB

CSatGal, did you place the function within the form module? Did you spell the name of the function correctly in the OnClick event? The error you are receiving indicates that it cannot find the function.

 

[CSatGal]

Maybe I'm not correctly understanding what the form module is?? I placed the function right after the General object of the VBA code for the form. Should I have created a new module? Or put it somewhere else? The label is also showing a pop-up caution/error message when the form is in design view saying that the label (lblCodes_All) is not associated with a control... should I associate it with lstCodes?

 

[FancyPrairie]

oops, my mistake. The name of the function is ProcessListBoxClick. However, I had you set the OnClick event to =ProcessListBox... Change the OnClick event to =ProcessListBoxClick([lblEmp_All])

 

[CSatGal]

I tried... i'm still getting the same error message thought.

 

[Phil4tektips]

Do you not need to change "Emp" in the expression to "Codes"

-Phil4tektips-

 

[FancyPrairie]

Your OnClick event for the first label should look like this:

OnClick.....=ProcessListBoxClick([lblCodes_All])

The OnClick event for the list box should look like this:

OnClick.....=ProcessListBoxClick([lstCodes])

The OnClick event for the 2nd label should look like this:

OnClick.....=ProcessListBoxClick([lblCodes])

 

[CSatGal]

I've double checked...they're all just fine! Was I supposed to have changed anything in the code??

 

[FancyPrairie]

As I stated in a previous post, I had not tested the function ProcessListBoxClick (I had stripped things out to simplify it for you and didn't have time to test it). Evidently, the function cannot have optional arguments. So change the first line from this

Public Function ProcessListBoxClick(ctlCaller As Control, _
                           Optional varBackColor_All As Variant = 16777215, _
                           Optional varForeColor_All As Variant = 0

to this

Public Function ProcessListBoxClick(ctlCaller As Control)


     Const varBackColor_All As Variant = 16777215
     Const varForeColor_All As Variant = 0

 

[Phil4tektips]

FancyPrairie,

I've implemented some of the code talked through in this discussion. Its working magnificently.

But how can i adapt the code to read an english date format (26/07/2005) rather than an american one (07/26/2005)?

Many thanks,

-Phil4tektips-

 

[Phil4tektips]

Is this simple to change?

I've looked through the code and cant identify where I could change it?

Cheers,

~Phil4tektips~
Grant us peace in our days work!

 

[FancyPrairie]

Sorry, I had looked at my code per your previous request and then got sidetracked. Anyway, I don't believe my code cares about the format (it's just a date). You need to format the control on the form/report.

 

[Phil4tektips]

Interesting....cause the default I have set up here is the English Format.

When I looked at the format on the table it was in the short date format - 19/06/1994, the text boxes on the filter form itself are also in the short date format.

Thats why I thought it might have been your code somewhere that sets it to teh American style date format?

Thanks.

~Phil4tektips~
Grant us peace in our days work!

 

[Phil4tektips]

FancyPrairie do you have any ideas?

Cheers,

~Phil4tektips~
Grant us peace in our days work!

 

[Tinhat]

Subsequent to Phil4tektips excellent help in setting up this databse, we have an Access database that has been split to allow easier mulituser access.
Problem, we did have some reports (2) that were linked to powerpoint presentations (Link and update selected) Now in the split DB although the report (and the link )is updatable whilst logged on the information is lost on log off.
Data loaded via forms to tables is fine.
Is there any solution to this other than updating the report direct in the master copy of the database? (we were trying to avoid this, too many cooks and all that)
Or is it a case of producing a new form , table and report to allow input of the data?
Any help appreciated
Tinhat.

 

[CSatGal]

Hi All:

The form has worked incredibly well for me for a number of months now.... but I've run into a wee problem. Can the form/code be used to generate a 'results list' in another form (or something?!) that can be used to further browse the database. i.e. if a listbox with a product line (among others) is used to populate a report, can it be changed so that that list box now populates a form or something that will allow me to look at/scroll through/possibly even make changes to the records?

Thanks!!