Revamping some queries for new release of DB

[KristenD]

I would like to revamp some of my queries. On the old front end I have a form that with drop down boxes that gives ranges for the user in order to generate reports. There are two separate queries/forms that I use because I don't know how to set it up so it will choose just one or choose all but one. But I would like to streamline the database and was thinking if I used a combo box but somehow had it use a value for each query. For example the form will say Include self rating and then have a combo box that has Yes/No, ideally what I would like it to do is if the answer is yes run the query that includes that rating or if the user chooses No run the other query.

Is this possible? If so how?? I'm Googling it but not coming up with straight answers.

Thanks a bunch!

 

[Guus2005]

With every installation of office and access there is a sample database accompanied with it. It is called the Northwind database.
It is a sample database with lots of examples.
if your problem is not there then try Orders.mdb or Solutions.mdb

Just search this forum. They are frequently posted here.

HTH

 

[gblack]

In design view of the form, goto the OnClick event procedure in the properties of your dropdown box. In there you can add code something like this:

IF Me.dropdownboxname = "Yes" Then
docmd.OpenQuery("Query1")
ELSE
docmd.OpenQuery("Query2")
END IF

That will run two seperate queries based upon what's clicked in the dropdown box.

 

[KristenD]

THANK YOU!!!! That is exactly what I was looking for!

 

[KristenD]

Ok, I put the combo box on the form and on the wizard had it saved for later use.

I put the code in under (General) and (Directions) in Visual Basic and it is not pulling both queries.

What am I doing wrong?

 

[gblack]

No sure... is it opening either query, or just doing nothing, when you click?

I'm not sure if a string value of "Yes" is the issue, it might not be recognized as a string... Yes/No could be true false... you could try either:

If Me.dropdownname Then
DoCmd.OpenQuery ("QUERY1")
Else
DoCmd.OpenQuery ("QUERY2")
End If

OR

If Me.dropdownname = True Then
DoCmd.OpenQuery ("QUERY1")
Else
DoCmd.OpenQuery ("QUERY2")
End If

 

[KristenD]

Sorry it has taken me so long to reply. I had to go out of town.

The report is working but when I go to export the report into Excel it is saying "Compile Error: Invalid Outside Procedure." This is the command I used:

Option Compare Database

Private Sub cmdExptEmp_Click()
Dim sQry As String
Dim sFilePath As String
sQry = "Employee List"
sFilePath = "C:\Users\kdepuyt\Desktop\"
Debug.Print sFilePath & sQry & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sQry, sFilePath & sQry & ".xlsx", True
MsgBox "Your Query is on your Desktop"
End Sub

Private Sub cmdExptEmpRec_Click()
Dim sQry As String
Dim sFilePath As String
sQry = "qryEmpRecord"
sFilePath = "C:\Users\kdepuyt\Desktop\"
Debug.Print sFilePath & sQry & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sQry, sFilePath & sQry & ".xlsx", True
MsgBox "Your Query is on your Desktop"
End Sub

Private Sub cmdExptRateSkill_Click()
Dim sQry As String
Dim sFilePath As String
sQry = "qryEmpSkillRating"
sFilePath = "C:\Users\kdepuyt\Desktop\"
Debug.Print sFilePath & sQry & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sQry, sFilePath & sQry & ".xlsx", True
MsgBox "Your Query is on your Desktop"
End Sub

Private Sub cmdExptSptID_Click()
Dim sQry As String
Dim sFilePath As String
sQry = "qryRatingbySuperID"
sFilePath = "C:\Users\kdepuyt\Desktop\"
Debug.Print sFilePath & sQry & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sQry, sFilePath & sQry & ".xlsx", True
MsgBox "Your Query is on your Desktop"
End Sub

Private Sub cmdExptSupt_Click()
Dim sQry As String
Dim sFilePath As String
sQry = "qryRatingbySuper"
sFilePath = "C:\Users\kdepuyt\Desktop\"
Debug.Print sFilePath & sQry & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sQry, sFilePath & sQry & ".xlsx", True
MsgBox "Your Query is on your Desktop"
End Sub

Private Sub cmdExptWorkStat_Click()
Dim sQry As String
Dim sFilePath As String
sQry = "qryWorkStatusList"
sFilePath = "C:\Users\kdepuyt\Desktop\"
Debug.Print sFilePath & sQry & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sQry, sFilePath & sQry & ".xlsx", True
MsgBox "Your Query is on your Desktop"
End Sub

 

[Guus2005]

Sorry it has taken me so long to reply. I had to go out of town.

People chasing you? I have that too sometimes.

About your problem: You heed to compile the code. In the menu, look for Debug and then Compile.
This should piont you to where the problem is.

If you still can't figure it out, let me know where Access says the error is.

HTH

 

[KristenD]

Always...had to go half way across the country!

This where is says it has the problem:

Option Compare Database
Option Explicit

If [COLOR="Red"]Me[/COLOR].Combo25 = "Yes" Then
    DoCmd.OpenQuery ("qryEmpSkillRatingSelf")
Else
    DoCmd.OpenQuery ("qryEmpSkillRating")
End If

The Me portion of the code keeps popping up as the problem and I'm not sure why.

 

[Guus2005]

You are starting the code without telling access that this is a function or procedure.

The easiest way to fix this is to go to the form, right-click on the button and choose build event.
You go to the code window and enter the code.

When I am behind my laptop I'll show you what it looks like.

Hth

 

[KristenD]

Ok, Now I've fixed it so the command is on the form but now the email option won't work on the form!

Grrrr....it seems I fix one thing only to have another portion of the DB not work!

 

[Guus2005]

did it work previously?
what doesn't work?

 

[KristenD]

It did work previously when I was using two different forms. But now I've added a combo box with a yes/no option and depending on which one you choose it will run one query or another query. And I've gotten the report to work, as well as the Export to Excel to work and the print button to work but now it will not email the report. This is the code for the email option:

Private Sub cmdEmailRpt_Click()
Dim sRpt
sRpt = "Employee Skill Rating - Supt"
DoCmd.SendObject acSendReport, sRpt, acFormatPDF, , , , , "Please review the attached reports."
MsgBox "Your Email is Ready to Send"
End Sub

I don't know if I need to change the report name because it's based on query that was chosen on the form. I may have bitten off more than I can chew with this database as a newbie!

 

[Guus2005]

Hi KristenD,

Your reply escaped my attention. Sorry about that.
Is your problem fixed?

Need help?

 

[KristenD]

Yes, I did end up getting the problem worked out...Thank you!