ACCESS FEED: Re: [MS_AccessPros] Report not opening, Dialog Form is blank

Khalid-

You should have been prompted for the ClientCIN after hiding the report, so that should have been a clue about what was wrong.

Although you can code most parameter queries without declaring the parameters, it's always a good idea to do so. Some parameter queries - like Crosstab - won't work at all unless you declare the parameters. You should make a habit of always declaring.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 21, 2014, at 10:27 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

Sorry for replying late, i was away from the office as i returned last night i thoroughly checked each and every part.

Oh my God you were totally right saying everything, i was making a very minor mistake BUT it was a critical mistake.
What i was doing was that on the query criteria i was not putting Dialog Forms combox name instead i was putting field name, see below the correct criteria:
[Forms]![Clients Claim Undelivered Cargo Per Pc Dialog]![cboClientCIN]
And i was doing
[Forms]![Clients Claim Undelivered Cargo Per Pc Dialog]![ClientCIN]

Thank you very much John for your all support and persisting me to the recommended way and method.

Would you please mention one more thing that PARAMETERS must be defined in the queries for such Dialog Forms?

Thanks again,
Khalid

On Thursday, March 20, 2014 3:32 AM, John Viescas <JohnV@msn.com> wrote:

Khalid-

Is the Record Source of the report saved as a query? If so, try running the query by itself and enter the values you think you're selecting for ClientCIN and ConsignmentNo in the two parameter prompts. If you get no records, then that would be why your report is coming up blank. If you do get something, you need to figure out why the query isn't using the values from the combo boxes.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 19, 2014, at 9:03 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,
OK i got your point about bound dialog forms.

I am selecting valid ClientCIN and ConsignmentNo. At present there are few ClientCIN in the table, they are 1,3,4,5,13,80,81 and there is only One ConsignmentNo "2012-A-11".

Now this matter is giving me headache and also become amazing for me that why its not been solved. Any more question you need to ask i will answer you immediately.

Khalid

On Wednesday, March 19, 2014 1:20 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

Your bound dialog forms are working because the record set is updatable and / or returns rows. Note that when you use a bound form with the combo boxes also bound, you are UPDATING whatever is the current record when you choose a value in the combo box.

The fact that your No Data event was firing tells me that the parameter values you chose returned no rows. Simply removing the No Data event code won't solve your problem. The Row Source of both combos appear to return only one column, so as long as the Bound Column is 1 for both and you select a valid ClientCIN and ConsignmentNo combination, your report query should work. Note that the way your query is coded, you must choose both values, and the ConsignmentNo must be valid for the ClientCIN you choose.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 19, 2014, at 7:50 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

Before replying you again i have spend many hours on experimenting in different ways but all in vain.

As Dialog Form has NO Record Source now, both combo boxes are UNBOUND and there Row Sources are:
ComboBox "cboClientCIN" Row Source is:
SELECT DISTINCT ClientsClaim.ClientCIN FROM ClientsClaim ORDER BY ClientsClaim.ClientCIN;

And Combobox "cboConsignmentNo" Row Source is:
SELECT DISTINCT ClientsClaim.ConsignmentNo FROM ClientsClaim ORDER BY ClientsClaim.ConsignmentNo;

I do get DISTINCT ClientCIN and ConsignmentNo on the Form and even after selecting each yet the report is blank. I also did checked it in two different ways:
a) On the reports "On No Data" event set a custom message, and in result got the message box instead of opening report.
b) Removed reports "On No Data" event, and got report opened but without any data or output.

Now tell me where i'm wrong?

Second thing I DO NOT disagree with you about saying "a dialog parameter form should NEVER have a Record Source." as you have the knowledge/experience, which i am far behind.
But please explain me WHY on on some other Dialog Forms i put Record Source same as Reports Record Source, and on Dialog Form Combobox is/are BOUND and i do get the Report output.

Khalid

On Wednesday, March 19, 2014 2:52 AM, John Viescas <JohnV@msn.com> wrote:

Khalid-

I assume the Bound Column for both combo boxes is 1. That should work as long as you select values in both combo boxes.

By the way, a dialog parameter form should NEVER have a Record Source.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 18, 2014, at 8:37 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

You must be right saying that Dialog Form must NOT have a Record Source. But i'm confused that i have some Dialog Forms which have the Record Source same for the Report concerned and they are working.

Now as you said i have removed Record Source of Dialog Form and it is now showing its two controls
cboClientCIN (Unbound)
Its Row Source is:
SELECT DISTINCT ClientsClaimPerPiece.ClientCIN FROM ClientsClaimPerPiece ORDER BY ClientsClaimPerPiece.ClientCIN;

cboConsignmentNo (Unbound)
SELECT DISTINCT ClientsClaimPerPiece.ConsignmentNo FROM ClientsClaimPerPiece ORDER BY ClientsClaimPerPiece.ConsignmentNo;

But there is no output on the report.

I may remind once again that without Dialog Form if on the query i simply put under Criteria
Like [Enter Client CIN]
Like [Enter Consignment No]

I do get output on the report.

So why with the Dialog Form report gives no output?

Khalid

On Tuesday, March 18, 2014 11:50 AM, John Viescas <JohnV@msn.com> wrote:

Khalid-

Ah, just reread your original post. The dialog form must NOT have a Record Source. Your comment about the dialog opening "blank" tells me you have a Record Source that is not updatable and returns no rows.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 18, 2014, at 4:15 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

John,

The code is already there:

Private Sub CmdOK_Click()
Me.Visible = False
End Sub

Khalid

On Monday, March 17, 2014 11:05 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

You need code in the form to set Me.Visible = False to let the report run either in After Update of the combo or behind a command button.

John Viescas

--- Original Message ---

From: "Khalid Tanweer" <khalidtanweerburrah@yahoo.com>
Sent: March 17, 2014 10:32 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Report not opening, Dialog Form is blank

Hi John,

On the query i have now defined Parameters as:

PARAMETERS [Forms]![Clients Claim Undelivered Cargo Per Pc Dialog]![ClientCIN] Long, [Forms]![Clients Claim Undelivered Cargo Per Pc Dialog]![ConsignmentNo] Text ( 255 );

SELECT...

===========

Reports code is:

Private Sub Report_Close()

DoCmd.Close acForm, "Clients Claim Undelivered Cargo Per Pc Dialog"

DoCmd.OpenForm "Clients Claim"

DoCmd.Restore

End Sub

Private Sub Report_NoData(Cancel As Integer)

MsgBox "This Client has no claims for the Consignment Number you entered." & vbCrLf & _

"OR this Client CIN does not exist" & vbCrLf & _

"Please enter correct Consignment Number for his claim" & vbCrLf & _

"" & vbCrLf & _

"Try again..." _

, vbInformation, "PCTL - Information"

Cancel = 1

End Sub

Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report

' is in the Open event

bInReportOpenEvent = True

' Open Clients Claim Undelivered Cargo Per Pc Dialog

DoCmd.OpenForm "Clients Claim Undelivered Cargo Per Pc Dialog", , , , , acDialog

' Cancel Report if User clicked the Cancel Button

If IsLoaded("Clients Claim Undelivered Cargo Per Pc Dialog") = False Then Cancel = True

' Set public variable to false to indicate that the

' Open event is completed

bInReportOpenEvent = False

End Sub

==========

Forms code is:

Private Sub Form_Open(Cancel As Integer)

If Not bInReportOpenEvent Then

' If we're not called from the report

MsgBox "For use from the 'Clients Claim Undelivered Cargo (Piece wise)' Report only", _

vbOKOnly

Cancel = True

End If

Form_Open_Exit:

Exit Sub

End Sub

===============

On Form control cboClientCIN Row Source is:

SELECT DISTINCT ClientsClaim.ClientCIN FROM ClientsClaim ORDER BY ClientsClaim.ClientCIN;

And cboConsignmentNo Row Source is:

SELECT DISTINCT ClientsClaim.ConsignmentNo FROM ClientsClaim ORDER BY ClientsClaim.ConsignmentNo;

Yet having the same problem

Khalid

On Monday, March 17, 2014 3:15 PM, John Viescas <JohnV@msn.com> wrote:

Khalid-

Do you have code in the Report that opens the Dialog form in the Open event? And what code is in the form?

Also, you might need to explicitly define the parameter in the query:

PARAMETERS [Forms]![Clients Claim Undelivered Cargo Per Pc Dialog]![ClientCIN] Text;

SELECT ...

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 17, 2014, at 9:04 AM, Khalid Tanweer <khalidtanweerburrah@yahoo.com> wrote:

Hi All,

I have some reports which i am opening through Dialog Form, which are working right.

But now i am stucked for many reports for which report is not opening and Dialog Form to fill parameters comes to a blank form, further i am unable to close this form unless i right click on the form and click on "Form Design".

I have further noticed that any query which have 'Sum' or 'Count' they are not working.

For example i put down the query:

SELECT ClientsClaimPerPiece.ClientCIN, ClientsClaimPerPiece.ClientName, ClientsClaimPerPiece.ConsignmentNo, ClientsClaimPerPiece.CartonNo, ClientsClaimPerPiece.Product, ClientsClaimPerPiece.ProductQtyClaimed, ClientsClaimPerPiece.PricePerPc, ClientsClaimPerPiece.ClaimPerPc, Sum(ClientsClaimPerPiece.ProductQtyClaimed) AS TotQtyClaimed

FROM ClientsClaimPerPiece

GROUP BY ClientsClaimPerPiece.ClientCIN, ClientsClaimPerPiece.ClientName, ClientsClaimPerPiece.ConsignmentNo, ClientsClaimPerPiece.CartonNo, ClientsClaimPerPiece.Product, ClientsClaimPerPiece.ProductQtyClaimed, ClientsClaimPerPiece.PricePerPc, ClientsClaimPerPiece.ClaimPerPc

HAVING (((ClientsClaimPerPiece.ClientCIN)=[Forms]![Clients Claim Undelivered Cargo Per Pc Dialog]![ClientCIN]) AND ((ClientsClaimPerPiece.ClientName) Is Not Null) AND ((ClientsClaimPerPiece.ConsignmentNo)=[Forms]![Clients Claim Undelivered Cargo Per Pc Dialog]![ConsignmentNo]) AND ((ClientsClaimPerPiece.CartonNo) Is Not Null) AND ((ClientsClaimPerPiece.Product) Is Not Null) AND ((ClientsClaimPerPiece.ProductQtyClaimed) Is Not Null) AND ((ClientsClaimPerPiece.PricePerPc) Is Not Null) AND ((ClientsClaimPerPiece.ClaimPerPc) Is Not Null))

ORDER BY ClientsClaimPerPiece.CartonNo, ClientsClaimPerPiece.Product;

============

Could somebody help. Is there some other method to open Dialog Forms for such queries OR something is wrong in this query.

============

The report works perfectly if i do not use Dialog Form and simply in the query i put criteria

Like [Enter Client CIN]

Like [Enter Consignment No]

Thanks in advance for assistance.

Khalid