Trying to calll a report based on 2 form selections in MS Access 2007

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

ladyvj

Thread Starter
Joined
May 12, 2011
Messages
16
Hi,

I need some help. I have created a database where you can search for information on people within a group. I have 4 different disciplines and a few years to choose from. I can get the reports to pull based off just the Discipline or by a person's name. I am having trouble selecting a discipline and year. Example I can pull all information for one discipline for all the years. I want to be able to filter it down to just whatever year is selected by the user.
This is my code can someone help please.

Dim rs As ADODB.Recordset
If cboProjyear.Value > 1 Then
Set rs = CurrentProject.Connection.Execute("select * from tblProjectYears where id=" & cboProjyear.Value)
tempname = ""
If Not rs.EOF Then
tempname = "[Proj Year] =" & rs.Fields("Project Year") & ""
End If
Set rs = Nothing
End If
tempstring = cboDiscipline.Value
If tempstring = "2" Then
'DoCmd.OpenQuery "qryAAscorecards", acViewNormal
DoCmd.OpenReport "AAScorecards", acViewPreview, , tempname
Else
If tempstring = "3" Then
'DoCmd.OpenQuery "qryAAscorecards", acViewNormal
DoCmd.OpenReport "SAScorecard", acViewPreview, , tempname
Else
If tempstring = 4 Then
'DoCmd.OpenQuery "qryEEscorecards", acViewNormal
DoCmd.OpenReport "EEScorecards", acViewPreview, , tempname

Else
If tempstring = "5" Then
'DoCmd.OpenQuery "qryMEscorecards", acViewNormal
DoCmd.OpenReport "MEScorecards", acViewPreview, , tempname
End If
End If
End If
End If


To further explain. Discipline and Project Year are combo boxes so it is a drop down selection. Each discipline has a different set of information so you have to know which discipline to know what report to call and if no year is selected I still want it to show all the records. Thanks.
 
Joined
Jul 29, 2001
Messages
21,334
You can set the criteria without having to create a recordset. Set the criteria as a variable for your DoCmd.OpenReport
 

ladyvj

Thread Starter
Joined
May 12, 2011
Messages
16
I tried that before but the criteria was too big it wouldn't select it properly
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You can also use the Criteria row of the query to set Criteria to match values selected in a form using the
forms![formname]![fieldname]
format
You can then convert that to a VBA SQL statement if you want.
 
Joined
Jul 29, 2001
Messages
21,334
If the criteria is based off a couple of combo boxes the criteria cannot be that long. You should use the primary key value for the discipline and not the text in the combo. That should shorten it up a bit. If you had a smaple of what you are working on I would take a look at it for you.
 

ladyvj

Thread Starter
Joined
May 12, 2011
Messages
16
Ok I got it to work pulling the year but if no year is selected it is not giving me everything. Here is my code

If cboDiscipline.Value = "2" Then
DoCmd.OpenReport "AAScorecards", acViewPreview, , "[Proj Year]=" & Me![cboProjyear], acWindowNormal
Else
If cboDiscipline.Value = "3" Then
DoCmd.OpenReport "SAScorecards", acViewPreview, , "[Proj Year]=" & Me![cboProjyear], acWindowNormal
Else
If cboDiscipline.Value = "4" Then
DoCmd.OpenReport "EEScorecards", acViewPreview, , "[Proj Year]=" & Me![cboProjyear], acWindowNormal
Else
If cboDiscipline.Value = "5" Then
DoCmd.OpenReport "MEScorecards", acViewPreview, , "[Proj Year]=" & Me![cboProjyear], acWindowNormal
End If
End If
End If
End If
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You need to repeat the whole sequence with an outer test
if isnull(Me![cboProjyear] then
If cboDiscipline.Value = "2" Then
DoCmd.OpenReport "AAScorecards", acViewPreview, , , acWindowNormal
Else

etc
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top