Report not open

[smtazulislam]

Hello,
can anyone talk me where I have mistake ? one report opened, second report not opening

Private Sub cmdPrint_Click()
DoCmd.Save
If IsNull(Me.txtDateFrom) Then
    MsgBox "Please select a valid record", vbOKOnly, "Data Required"
End If

If Me.txtPreviousSalary > 1 Then
    DoCmd.OpenReport "rptIncrasingSalary", acViewPreview, "", "[SalaryID] = " & Me!txtSalaryID, acWindowNormal
    DoCmd.RunCommand acCmdPrint
    Debug.Print
Else
If Me.txtPreviousSalary < 1 Then
   DoCmd.OpenReport "rptDecreaseSalary", acViewPreview, "", "[SalaryID] = " & Me!txtSalaryID, acWindowNormal
   DoCmd.RunCommand acCmdPrint
   Debug.Print

End If
End If

End Sub

tried it

Private Sub cmdPrint_Click()
DoCmd.Save
If IsNull(Me.txtDateFrom) Then
    MsgBox "Please select a valid record", vbOKOnly, "Date Required"
    Me.txtDateFrom.SetFocus
End If

If Me.txtPreviousSalary >= 1 Then
    DoCmd.OpenReport "rptIncrasingSalary", acViewPreview, "", "[SalaryID] = " & Me!txtSalaryID, acWindowNormal
    DoCmd.RunCommand acCmdPrint
    Debug.Print
Else
If Me.txtPreviousSalary >= -1 Then
   DoCmd.OpenReport "rptDecreaseSalary", acViewPreview, "", "[SalaryID] = " & Me!txtSalaryID, acWindowNormal
   DoCmd.RunCommand acCmdPrint
   Debug.Print

End If
End If

End Sub

 

[Minty]

Add
Debug. Print Me.txtPreviousSalary
and see what it is actually getting, rather than what you expect.

In the first example - If Me.txtPreviousSalary = 1 neither report would run?

What values are you expecting and what do you want to happen based on that value?

 

[smtazulislam]

Add
Debug. Print Me.txtPreviousSalary
and see what it is actually getting, rather than what you expect.

In the first example - If Me.txtPreviousSalary = 1 neither report would run?

What values are you expecting and what do you want to happen based on that value?

You suggest me do like that

DoCmd.Save
If IsNull(Me.txtDateFrom) Then
    MsgBox "Please select a valid record", vbOKOnly, "Date Required"
    Me.txtDateFrom.SetFocus
End If

If Me.txtPreviousSalary = 1 Then
    DoCmd.OpenReport "rptIncrasingSalary", acViewPreview, "", "[SalaryID] = " & Me!txtSalaryID, acWindowNormal
    DoCmd.RunCommand acCmdPrint
    Debug.Print Me.txtPreviousSalary
Else
If Me.txtPreviousSalary = -1 Then
   DoCmd.OpenReport "rptDecreaseSalary", acViewPreview, "", "[SalaryID] = " & Me!txtSalaryID, acWindowNormal
   DoCmd.RunCommand acCmdPrint
   Debug.Print Me.txtPreviousSalary

End If
End If

 

[smtazulislam]

My query was like that.

SELECT tblEmployeeSalaries.SalaryID, tblEmployeeSalaries.EmployeeID, qryEmployeeExtended.EmployeeName, qryEmployeeExtended.DeptName, qryEmployeeExtended.Designation, tblEmployeeSalaries.DateFrom, tblEmployeeSalaries.DateTo, tblEmployeeSalaries.ContractTypeID, tblEmployeeSalaries.Basic, tblEmployeeSalaries.Food, tblEmployeeSalaries.Others, tblEmployeeSalaries.Increase, tblEmployeeSalaries.Available, Format([DateFrom],"mmmm -yyyy") AS [Effective Month], DLookUp("sGrossSalary","tblEmployeeSalaries","SalaryID = " & [SalaryID]-1) AS PreviousSalary, tblEmployeeSalaries.sGrossSalary, tblEmployeeSalaries.Note1, tblEmployeeSalaries.Note2, tblEmployeeSalaries.Note3, tblAllowanceData.TotalAllowance, LtblAllowaceType.AllowanceType, tblAllowanceData.AllowanceTypeID
FROM (qryEmployeeExtended INNER JOIN tblEmployeeSalaries ON qryEmployeeExtended.EmployeeID = tblEmployeeSalaries.EmployeeID) LEFT JOIN (tblAllowanceData LEFT JOIN LtblAllowaceType ON tblAllowanceData.AllowanceTypeID = LtblAllowaceType.AllowanceTypeID) ON qryEmployeeExtended.EmployeeID = tblAllowanceData.EmployeeID;

DLookUp("sGrossSalary","tblEmployeeSalaries","SalaryID = " & [SalaryID]-1) AS PreviousSalary,

In this query this is Previous salary

I would like to print
If sGrossSalary is Grater then Previous Salary amount
then print Increasing salary report
if sGrossSalary is less then Previous salary amount
then print decrease salary report.

edit
attached picture

 

[The_Doc_Man]

What is the value you expect to see in txtPreviousSalary? Comparing for a salary of 1 (or, for that matter, of -1) seems a bit small in any units, so that has to be the result of a computation. Can you show us that formula?

When you nest IF/THEN/END IF blocks as you did, I would expect that you would see either but not both reports, but that you could also see NO report. So when you say that you never see the second report, I believe it. BUT is it possible that someone's salary stayed the same?

EDIT: You posted the query while I was composing my response.

That DLookup is going to slow you down tremendously because you run a query within a query for every record. I'll have to look at that for a moment or two.

 

[Minty]

Not quite what I had in mind.
Add the debug before any of the other code ;

Debug.Print  "My Value is: " & Me.txtPreviousSalary

DoCmd.Save

If IsNull(Me.txtDateFrom) Then
    MsgBox "Please select a valid record", vbOKOnly, "Date Required"
    Me.txtDateFrom.SetFocus
    Exit Sub
End If
....etc

Then examine the immediate window in the VBA Editor (Press ctrl & G in the VBA editor to bring up the immediate window)

 

[smtazulislam]

Hello Sir,
Look post #4

if I would like to print
first row then need open INCREASEING report
Second row then need to open Decrease report

 

[The_Doc_Man]

SELECT
    tblEmployeeSalaries.SalaryID,
    tblEmployeeSalaries.EmployeeID,
    qryEmployeeExtended.EmployeeName,
    qryEmployeeExtended.DeptName,
    qryEmployeeExtended.Designation,
    tblEmployeeSalaries.DateFrom,
    tblEmployeeSalaries.DateTo,
    tblEmployeeSalaries.ContractTypeID,
    tblEmployeeSalaries.Basic,
    tblEmployeeSalaries.Food,
    tblEmployeeSalaries.Others,
    tblEmployeeSalaries.Increase,
    tblEmployeeSalaries.Available,
    Format([DateFrom],"mmmm -yyyy") AS [Effective Month],
    DLookUp("sGrossSalary","tblEmployeeSalaries","SalaryID = " & [SalaryID]-1) AS PreviousSalary,
    tblEmployeeSalaries.sGrossSalary,
    tblEmployeeSalaries.Note1,
    tblEmployeeSalaries.Note2,
    tblEmployeeSalaries.Note3,
    tblAllowanceData.TotalAllowance,
    tblAllowaceType.AllowanceType,
    tblAllowanceData.AllowanceTypeID
FROM
    (qryEmployeeExtended
INNER JOIN
    tblEmployeeSalaries
ON
    qryEmployeeExtended.EmployeeID = tblEmployeeSalaries.EmployeeID)
LEFT JOIN
    (tblAllowanceData
LEFT JOIN
    tblAllowaceType
ON
    tblAllowanceData.AllowanceTypeID = LtblAllowaceType.AllowanceTypeID)
ON
    qryEmployeeExtended.EmployeeID = tblAllowanceData.EmployeeID;

OK, a couple of specifics. Rather than doing a DLookUp in a query, do one more

tblEmployeeSalaries LEFT JOIN tblEmployeeSalaries AS PrevES ON tblEmployeeSalaries.SalaryID = PrevES.SalaryID - 1

Because of the nested JOIN clauses, you might have to work on the parentheses a little bit. You've already nested four items so there should be minimal problem in adding one more layer. NOTE that you would have the same problem with the DLookUP as for the LEFT JOIN if the person was employed this year and HAS no previous salary. Which means that you might need an NZ around the previous salary. Then compute your salary flag in the SELECT clause

SWITCH (
    tblEmployeeSalaries.sGrossSalary > NZ( PrevES.sGrossSalary, 0), 1,
    tblEmployeeSalaries.sGrossSalary = NZ( PrevES.sGrossSalary, 0), 0,
    tblEmployeeSalaries.sGrossSalary < NZ( PrevES.sGrossSalary, 0), -1 ) AS PreviousSalary ,

Then, just as a suggestion to save yourself so much typing, consider using an AS clause to provide an alias name for each table or query so you can shorten the names a little. With so many long names, it would be VERY easy to make a mistake in typing. Shorten those names, shorted your work. BUT this is a suggestion of convenience for you, not a technical requirement.

 

[Pat Hartman]

DoCmd.Save

I presume you are trying to save the current record since there is no point in saving a form from within your code. The user doesn't modify forms. He modifies data.

If you want to save the current record, and you should before opening a form or report, use:

DoCmd.RunCommand acCmdSaveRecord

or

Me.Dirty = False

 

[smtazulislam]

SELECT
    tblEmployeeSalaries.SalaryID,
    tblEmployeeSalaries.EmployeeID,
    qryEmployeeExtended.EmployeeName,
    qryEmployeeExtended.DeptName,
    qryEmployeeExtended.Designation,
    tblEmployeeSalaries.DateFrom,
    tblEmployeeSalaries.DateTo,
    tblEmployeeSalaries.ContractTypeID,
    tblEmployeeSalaries.Basic,
    tblEmployeeSalaries.Food,
    tblEmployeeSalaries.Others,
    tblEmployeeSalaries.Increase,
    tblEmployeeSalaries.Available,
    Format([DateFrom],"mmmm -yyyy") AS [Effective Month],
    DLookUp("sGrossSalary","tblEmployeeSalaries","SalaryID = " & [SalaryID]-1) AS PreviousSalary,
    tblEmployeeSalaries.sGrossSalary,
    tblEmployeeSalaries.Note1,
    tblEmployeeSalaries.Note2,
    tblEmployeeSalaries.Note3,
    tblAllowanceData.TotalAllowance,
    tblAllowaceType.AllowanceType,
    tblAllowanceData.AllowanceTypeID
FROM
    (qryEmployeeExtended
INNER JOIN
    tblEmployeeSalaries
ON
    qryEmployeeExtended.EmployeeID = tblEmployeeSalaries.EmployeeID)
LEFT JOIN
    (tblAllowanceData
LEFT JOIN
    tblAllowaceType
ON
    tblAllowanceData.AllowanceTypeID = LtblAllowaceType.AllowanceTypeID)
ON
    qryEmployeeExtended.EmployeeID = tblAllowanceData.EmployeeID;

this is work perfectly.

OK, a couple of specifics. Rather than doing a DLookUp in a query, do one more

tblEmployeeSalaries LEFT JOIN tblEmployeeSalaries AS PrevES ON tblEmployeeSalaries.SalaryID = PrevES.SalaryID - 1

This Dlookup not work. error of the PrevES field. I given salaryID = salaryID -1
then work. But same result with my previousSalary field.

Because of the nested JOIN clauses, you might have to work on the parentheses a little bit. You've already nested four items so there should be minimal problem in adding one more layer. NOTE that you would have the same problem with the DLookUP as for the LEFT JOIN if the person was employed this year and HAS no previous salary. Which means that you might need an NZ around the previous salary. Then compute your salary flag in the SELECT clause

SWITCH (
    tblEmployeeSalaries.sGrossSalary > NZ( PrevES.sGrossSalary, 0), 1,
    tblEmployeeSalaries.sGrossSalary = NZ( PrevES.sGrossSalary, 0), 0,
    tblEmployeeSalaries.sGrossSalary < NZ( PrevES.sGrossSalary, 0), -1 ) AS PreviousSalary ,

Then, just as a suggestion to save yourself so much typing, consider using an AS clause to provide an alias name for each table or query so you can shorten the names a little. With so many long names, it would be VERY easy to make a mistake in typing. Shorten those names, shorted your work. BUT this is a suggestion of convenience for you, not a technical requirement.

SWITCH function is work. Nothing error.
But result display 1 all the field.

IN my View :
I thing my query is worked. But here is VBA is not work.
Shortly I upload the db..

 

[smtazulislam]

can check why not print..
cause details here : Post 1

Edit :

There is mistake that remove PrevousSalary filed.

 

[smtazulislam]

any update

 

[The_Doc_Man]

this is work perfectly.

This Dlookup not work. error of the PrevES field. I given salaryID = salaryID -1
then work. But same result with my previousSalary field.


SWITCH function is work. Nothing error.
But result display 1 all the field.

IN my View :
I thing my query is worked. But here is VBA is not work.
Shortly I upload the db..

The first piece of my post was just your query reformatted, so it should have worked the same way as it did previously. What I did made it more easily readable but otherwise didn't change a thing with syntax. So if your query worked, what I posted should have worked equally well.

There is no particular difficulty with SWITCH. As you said, it works just fine.

The trick is that when you use a DLookup in YOUR query, that runs a second query on-the-spot for EACH RECORD. So EVERY RECORD in the recordset opens a query. The speed of that process (a query within a query) becomes incredibly slow. For a short table, maybe not that bad. For longer tables, it gets worse as the tables get bigger and is generally not considered such a good idea.

What I was suggesting was that since you already had a 4-way JOIN, you could add a "self-JOIN" as a 5th JOIN clause. That was because you apparently have an ID number to identify a prior entry in this table. If you know the current number (which you do, because it is part of tblEmployeeSalaries), you should be able to subtract 1 to get that prior ID number. Knowing that number, you can directly link to the salaries table without having to depend on the DLookup that you showed in the original query. There is no searching in that case. The only remaining issue is one that the DLookup wouldn't solve for you. A first-time employee HAS no prior record so you can't look it up anyway. Thus I added the NZ() functions to protect against failed lookups.

I gave you an example for the form of the self-JOIN code, but it is possible that you might have to play with it. The PrevES was merely an ALIAS name for the employee salary table, and you needed it because you ALREADY were using the employee salary table. A self-JOIN runs into the problem that BOTH instances of the same table have the same fields, so how do you know which one you want. The answer is that when you want something from a specific instance of a table you must qualify it, as tblEmployeeSalaries.SGrossSalary or PrevES.SGrossSalary. That is why I suggested the method I did.

 

[smtazulislam]

Excuse for late reply.
I find the another solution LIKE tblEmployee.GrossSalary is previous Salary And tblEmployeeSalaries.sGrossSalary is Increase/Decrease salary. Its twice table And tblAllowance combine a query.
Its worked now.

Thank you very much all

 

[smtazulislam]

Hereby 6 pages add by Insert Page Break I added Header Each SubReport and main report header is blank.
Main report is open

Private Sub CmdPrint_Click()
DoCmd.OpenReport "rptVacationNormal", acViewPreview, , "RequestVacID = " & Me.RequestVacID, acWindowNormal
End Sub

In Print Preview showed same page twice. One With Header One without Header
Where have mistake ?

 

[smtazulislam]

Any advice ?

 

[smtazulislam]

Hello, Any help with this report ?