New to Access 1

[DSD]

I am fairly new to access, and created many tables forms and Quiries and report, now needless to day the folks want more.

I have a table with data like this as exemple

IssueID IssueName
28 ABC
29 CBS
30 NBC
31 ESPN

They have asked me to create a report that pulls the data, however they want to chose one issue Id, or in some cases two such as 29 and 31 or all. I can make list boxes but can't seem to get the data to a query and run the report. I assume I have to send the results to a query first maybe not. I've worked with Macro for a couple of days but still havn't figured it out....any suggestions would help.

Thanks

 

[jebry]

Hi!

How do you feel about VBA? The only solution I can think of involves coding. If you want a VBA solution let me know and I will do my best to walk you through it.

Jeff Bridgham
bridgham@purdue.edu

 

[DSD]

I can spell it.... well it's not that bad, I've done a little but not afraid to try anything... it's a learning points... plus lots of backups.

 

[jebry]

Hi!

Well here it goes then. Create a form with the list box and a command button. Set the list box to multiselect which will allow the user to select as many row as they want in the report. Use a label to tell the users that making no selection will return all of the issues. Create a report based on a query that pulls all of the information needed and create the report as is it will be used to display all of the records available. Finally, put this code in the click procedure of the command button:

Dim varRow As Variant
Dim strWhere As String

strWhere = ""
For Each varRow In YourListBox.ItemsSelected
strWhere = strWhere & "IssueId = " & YourListBox.Columns(0, varRow) & " Or "
Next varRow

If strWhere <> &quot;&quot; Then
strWhere = Left(strWhere, Len(strWhere) - 4)
DoCmd.OpenReport &quot;YourReport&quot;, , , strWhere
Else
DoCmd.OpenReport &quot;YourReport&quot;
End If

If you get stuck let me know.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[DSD]

Ok, everthing makes sense except:
1. On the should I use the wizard and assign issueID to that specific field?

2. On the command button, for the on click is that a event procedure or put the code in the expression??


Thanks

 

[jebry]

Hi again!

The only thing my code assumes about the issueID is that it is in the first column of the list box. And yes, the code should go into the event procedure. Open the property window for the command button and go to the Events tab. Click into the On Click line and choose [Event Procedure] from the drop down list. Click on the ... that will appear to the right and that will take you to the procedure where the code is to go. One last thing, the code I wrote uses the placeholder YourListBox. You need to put the actual name of the list box you create in its place (I know that is simple, but so many people have missed that before that I feel the need to say it).

hth
Jeff Bridgham
bridgham@purdue.edu

 

[DSD]

I may have done something wrong, when I run it nothing happens.

Here is the list box
Name Issue

Code under the command key Run Report



Private Sub Process()

End Sub

Private Sub Run_Report_Click()

End Sub
Private Sub Form_Click()



Dim varRow As Variant
Dim strWhere As String

strWhere = &quot;&quot;
For Each varRow In Issue.ItemsSelected
strWhere = strWhere & &quot;IssueId = &quot; & Issue.Columns(0, varRow) & &quot; Or &quot;
Next varRow

If strWhere <> &quot;&quot; Then
strWhere = Left(strWhere, Len(strWhere) - 4)
DoCmd.OpenReport &quot;Test II&quot;, , , strWhere
Else
DoCmd.OpenReport &quot;Test II&quot;
End If

End Sub



Also the name of my report is Test II

In the form I only have one field the IssueID list box...

What did I miss??

 

[jebry]

Hi!

Your code in the VB window should look like the following:

Private Sub Run_Report_Click()

Dim varRow As Variant
Dim strWhere As String

strWhere = &quot;&quot;
For Each varRow In Issue.ItemsSelected
strWhere = strWhere & &quot;IssueId = &quot; & Issue.Columns(0, varRow) & &quot; Or &quot;
Next varRow

If strWhere <> &quot;&quot; Then
strWhere = Left(strWhere, Len(strWhere) - 4)
DoCmd.OpenReport &quot;Test II&quot;, , , strWhere
Else
DoCmd.OpenReport &quot;Test II&quot;
End If

End Sub

Try that.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[DSD]

We're getting closer or at least a reaction
I'm getting a compile error, :Method or data member not found,

I hit OK and the following line is yellowed.

Private Sub Run_Report_Click()




I looked closely and the name of the command button is Run Report......

 

[jebry]

Hi!

Off the top of my head, I don't see where the problem lies. If you want to zip the Db and email it to me I'll take a quick look at it.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[DSD]

I just sent you an email with the attached database zipped, I pulled out the object in question so it would stay small.
Thanks for all the help... I'll learn this one way or the other....

Thanks