Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pick list for report 1

Status
Not open for further replies.

coyote1

IS-IT--Management
Nov 16, 2006
55
Have a database that stores the minutes of meetings in a table called "tblMinutesOfMeeting".
I have a query called "qryMinutesOfMeeting" that has a date parameter so the user can select a specific date for the particular recorded minutes interested in when the "rptMinutesOfMeeting" is run. This depends on the user to remember the specific date that the minutes were recorded and can be a hassle at times because of the random dates of when some of the meetings are recorded. It would be nice and more user friendly when the report ran that it would let the user pick a specific date from a list of "minutes" sorted by dates and pull that one up on the report screen.

Any suggestions will be greatly appreciated,

Karl
 
Karl
Here's one way...

1. Create an unbound form.
2. On the form put a list box that shows the various dates of the minutes. (I also assume that each set of minutes would have, in addition to the date, an ID column, so you could make this Column 0)
3. Then, on the form, put a Preview (and/or Print) command button that selects the report and uses the dates from the list box.

Tom
 
Thanks for the reply,

This sounds like the way to go but I guess I would
have to add some code to a "onclick" event that would select the date highlighted on the pick list and pop the preview to the screen. Any ideas on that ?

Thanks for your time,

Karl

 
Karl
Right. You add code to the OnClick event for the command button.

The code would look something like this...
Code:
Private Sub YourCommandButtonName_Click()
On Error GoTo Err_YourCommandButtonName_Click

    Dim stDocName As String

    stDocName = "YourReportName"
    DoCmd.OpenReport stDocName, acPreview, , [COLOR=purple]"RecordID = " & YourListBoxName.Column(0)[/color]

Exit_YourCommandButtonName_Click:
    Exit Sub

Err_YourCommandButtonName_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub

Note the Where clause in the DoCmd line (the part I have shown in purple). You will have to change the RecordID to whatever ID you use to identify the individual minutes.

Tom
 
Tom,

Thanks,

I will give it a go and let you know

Karl
 
Karl
As I indicated in my first reply, it's best if the first column (column 0) in the list box is the ID field. The column can be hidden, but needs to be in the list box configuration.

Tom
 
Tom,

When executing the command button (I went over the code syntax a couple of times and made the necessary reference changes as metioned)I am getting the following error message :


Visual Basic for Applications (VBA) encountered a problem while attempting to access a property or method. The problem may be one of the following:
A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base article 283806.
An Expression is misspelled.
Check all expressions used in event properties for correct spelling.
A user-defined function is declared as a sub or as a private function in a module.
Expressions can resolve a user-defined function only if the function is declared as one of the following:
A public function in a module
A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8 update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function properly when security is set to Medium or High. To obtain the latest version of Microsoft Jet, go to Windows Update.

Thanks for any input,

Karl
 
Karl
Please copy the code behind the command button and post it here.

Also, it's possible that you need to make sure the References are all there in the Reference Library in the VB Editor. Go to the code, click on the References tab and see what is checked there.

Are you using Access 2000? If not, what version of Access?

Tom
 
Tom,

I am using Access version 2003 SP2.
Under "References" I have the following checked:
*VB for applications
*MS Access 11.00 object library
*OLE automation
*MS DAO 3.6
*MS ActixeX data objects 2.1

Here is the code for the command button:

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

Dim stDocName As String

stDocName = "rptMinutesMeeting"
DoCmd.OpenReport stDocName, acPreview, , "[ID] = " & List12.Column(0)

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub


Thanks,

Karl
 
Karl
Well, offhand, I don't spot any problems with your code...providing that you have identified everything precisely. For example, is the field in the table actually called ID, or maybe RecordID, or something else? And is the list box actually called List12?

I'm sure that you probably already have at the very top of the code module the following two lines...
Option Compare Database
Option Explicit

That being the case, when you are in the VB Editor, click on the Debug tab and then Compile, and see if that highlights any problematic line.

Now...thinking back to your original post, you had a parameter prompt. You will want to be sure that parameter prompt is removed. Your report can have as its record source either the original table or a query based on that table, but should not have any extra stuff that has to be filled at the time of running the report from the command button.

The error message sure gave you lots of possibilities where things could be wrong, didn't it!! I can't imagine it being a Security problem, nor an incorrect version of the Jet engine being installed, because you would have done your Access 2003 installation from scratch. (I personally don't use 2003, still use 2000, so can't say more about that version)

In the code, you could try changing acPreview to acViewPreview. Either of those work with Access 2000 but I'm not sure about 2003.

Let me know how you make out.

Tom
 
Tom,

Did as suggested but when I ran the debug/compile it hightlights "List12" and says I have to "declare a variable".
Cannot seen to run debug compile again to re-create the message.

Thanks,

Karl
 
Karl
Well, you could add this to the code and see if it helps...

Code:
Dim lstTemp As ListBox
    Set lstTemp = Me.List12

And then change the DoCmd statement to (note the change in the Where clause)

Code:
DoCmd.OpenReport stDocName, acPreview, , "ID = " & [b]lstTemp.Column(0)[/b]

Tom
 
Tom,

Tried the code you mentioned and same orional error.

I am curious if there is a wizard or "auto something" I can try to duplicate somewhat what I am attempting to do and then take a peek at that code to compare ?

Thanks,

Karl




 
Karl
Before you do that, try this and see if it makes a difference. (In contrast to Access 2000, Access 2003 is pernickety about stuff.)

Change the DoCmd line to the following...
Code:
DoCmd.OpenReport stDocName, acPreview, , "ID = " & Forms!YourFormName!List12.Column(0)

Maybe Access is looking for the full reference to the form. I'm hoping that we don't need to declare a variable for the form too, but that is possible. However, try that first.

And if this doesn't work, try creating a new command button using the wizard to call the report. Best to do Preview rather than Print. Then after the wizard has done its work, go in and add the Where clause to the DoCmd line. And possibly add the declaration of the variable for the list box.

One thing I am curious about is the name of the list box. You say it is List12. The number "12" on there seems high since it is the only list box on the form.

Tom

 
Tom,

Sorry I got pulled into another issue unrelated and did not have a chance to see your reply but have made progress in the meantime. I went through all the steps from the beginning and noticed that the form's record source was "tblMinutesOfMeeting". I remember you specifying an "unbound" form so I removed tblMinutesOfMeeting from the record source (left blank) then saved the form re-opened and the darn thing works as designed with no errors.
I think my error was using a form I was working with prior to this post and not creating a new one form scratch.

I wish you well and thanks for all your time !!

Karl
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top