Creating a Report but no Data

[RoadrunnerII]

Still working away at this and relatively new to Access
I have a Command Button that points to a subform
The Subform uses an Unbound txt box to provide the list of items in a drop down list
I have an Event Procedure that calls the Report on Click

The Report uses a record source from a Query that includes the correct data
The Query uses the Criteria that inputs the Form data above
If I run the Query it prompts for the data Ie Usage Status:
I key in the correct item and the query provides the correct data
If I run the Report it prompts for the data and provides the correct output
It is just when I run the Form it does not provide any data in the report
Trying to make this pretty for the end user!
What am I missing???

RRII

 

[Rich]

Use the NoData Event of the report e.g.
Private Sub Report_NoData(Cancel As Integer)
Beep
Cancel = True
MsgBox "You have either not selected a company or there are no records for that selection"
End Sub

 

[RoadrunnerII]

K
Now when I run the form I get the no data event
What's missing
RRII

 

[Rich]

You can search here for Err 2501 there are numerous examples, but under the new forum rules if you cant I'll post back with the code

 

[dkinley]

Instead of trying to trap the error for this particular instance, the best way I have found is in the coding of the report. This way it is 'self-contained' by the following:

Option Compare Database
Option Explicit
     Dim bNoData As Boolean

Private Sub Report_NoData(Cancel As Integer)
    bNoData = True
End Sub

Private Sub Report_Page()
     If bNoData = True Then
          MsgBox "There is no data for this report. Closing report ..."
          DoCmd.Close acReport, "ReportNameHere", acSaveNo
     End If
End Sub

-dK

 

[RoadrunnerII]

I think I have confused this a bit
I am looking for the report to provide the data and it is not at this point
So everything seems to work except when I try to do the input via the form drop down list

RRII

 

[dkinley]

For your combo box, make sure you use the primary key for a column.

In the properties of the combo box, bound the primary key column to the unbound combo box and then hide that column in the column width's property (so the user only sees the column with meaningful information but you will use the primary key in the report query).


Now, in the query your use for the report, make use of the hidden column that is bound to the combo box. Make sure the criteria of the primary key column looks like ....

[Forms]![fFormNameComboBoxIsOn]![cmbComboBoxName]

Then you can use the posts above to fancy it up a bit more. =]

Hope that helps.

-dK

 

[RoadrunnerII]

dk
I understand where you are going here but now I am getting multiply entrys for Usage Status Ie 1 for each Serial number which is the primary key
RRII

 

[dkinley]

I used the primary key as an example - I didn't know you were working off of a child table to query your data.

Since you key in the correct data directly into the query and it returns the expected result (1 record), I would assume that you do not have the correct column bound to the combo box.

Is that a possibility that instead of keying off the primary key there is another field where there are not duplicates to return one and only one record (if that is the goal)?

-dK

 

[RoadrunnerII]

Actually it is returning multiply entries from the query and the Report.
What this Report is suppose to do is show all the devices we have Allocated to a particular group since specified Date of Model yyyyyy with Serial numbers
I am trying to get just the allocated part to work first. The primary key on the Table is SerialNumber. The query brings in the Allocated time parameter.

 

[dkinley]

I see.

If using the Group Name in the report query set the bound column of the combo box for the Group Name ... if using Group Name ID, then the bound of the combo box is Group Name ID.

Make the combo box bounded column correspond to which column you put the criteria in for the form query.

This will limit the records returned as it does when you directly enter the data. This help?

-dK

 

[RoadrunnerII]

So the Query prompts me for StartDate: Model: and Usage Status:
If I enter the correct values I get the right data
These are my inputs from the form
The form has 3 unbound boxes First box is Txt box for the Start Date: We are using a > value so anything after dd/mm/year value we enter will show up
Second unbound box is a Combo box for the Model
The drop down shows the correct list of models available from the Table
Third unbound box is a Combo box for the Usage Status
The drop down shows the correct list of Usage Status values
If I run the form I get no data ...
Popup (There are no records for that selection)
Popup (The Open Report action was cancelled)
If I run the query it prompts for the inputs and I get the correct data
If I run the Report it prompts for the inputs and I get the correct data
I must be just thick in the head here
The query is prompting based on the criteria from the form
Why is the form not pushing the criteria to the query???

I tried removing the Start Date txt box and the Model combo box and still same issue with just the Usage Status combo box.

RRII

 

[dkinley]

Hehehe, no, not thick-headed - we are attempting to discuss visual aspects through a word and it might be a translation gone awry back and forth.

Unless you are using a recordset, the form will not push the criteria to the query.

Using the query builder, which it sounds like you are, the query has to get the criteria from the form.


In the query builder, you have the columns of all the data you want. You are limiting your data on the following columns; StartDate, Model, and UsageStatus.

In the criteria of these columns you will need a reference to the form's combo boxes. So for the StartDate the criteria will look something like ...

[Forms]![fFormName]![txtTextBoxNameforStartDate]

For the Model ...

[Forms]![fFormName]![cmbComboNameforModel]

For the UsageStatus ...

[Forms]![fFormName]![cmbComboNameforUsageStatus]

Now all three criteria will look to the form for the criteria to limit the records returned.

If this doesn't work, then I am back to making sure the combo box has the correct data in it. The query looks to see what is bound to the combo box when using criteria like this. There can a difference between what you see on the screen and what is 'bound' to the combo box. You can bound one column but see a different one. The only time it is the same is if the viewable column is the bound column.

-dK

 

[dkinley]

There might be some confusion over the whole combo box issue, so I've included visuals this time. =]

Suppose my combo box query (or row source) looked up a table and I got the ID and the Name fields from the table. The ID field was in column 1 and the Name field was in column 2 of the query that my combo box looked up.

Now, to make sure the combo box is working for me.

The first property is the 'Bound Column'. Keep in mind that my combo box is unbound (meaning I do not have a data source to a table), but this property means what data from the combo box query am I binding to the combo box. I have it set to 1 (the first column of the combo box query or row source) because I want the data in the combo box to be the ID column (the way the row source is set up).

The second property is the Column Count and Column Widths. Column Count means I want to display 2 columns of data when the user hits the drop down box. However, in this instance I want to hide the 1st column (the user has no idea what an ID is, but they do undertand a Name). So in the Column Widths, I put in 0";2". 0" is the size of the ID column (it's the first and now hidden from the user) and 2" is the size of the Name column (it's the second and that is all the user can see).

So now a user chooses a Name, they see a Name, but the system sees the ID because that is what is bound to the combo box.

The bottom graphic, Query Builder, is the query I am trying to search data for a report or what-have-you. Since I am bounding the ID to the combo box, I put the combo box in the criteria for the MainID for the query because the system only sees the ID from the way I set up the combo box. This limits all the records in the query to the ID number the user chose in the combo box (even though they thought they chose a Name).

So I have to ensure correct correspondence of the criteria verses what is bound in the combo box if I want to use this sort of filtering on query returns.

That make sense?

-dK

Edit: Another method of using combo boxes is referencing the specific column; e.g., Me!cmbComboBoxName.Column; however for this application, if it is set up right you can use the method I have demonstrated.

 

[RoadrunnerII]

You are correct on the query builder and yes that is the how I have the criteria setup
So if the query builder has DateAllocated as the first column ModelName as the second and Usage status as the 3rd column that should make it easier
Start Date should be column1 ModelName column 2 and UsageStatus column 3
In the form Start Date is a txt box therefore it does not have the bound or Column width parameters
For the ModelName I do have them so the bound column would be 2 right?
For Usage status the bound column would be 3 right?

So I dropped the Start Date and Model names out of the query and moved Usage status to the first column
It works
Now just need to get the other two columns to work

 

[RoadrunnerII]

I figured out my root problem
The unbound boxes where not named what I was calling in the Query
No bloody wonder it was not returning any data
Just thick headed again!!!!

Thanks for the direction and help
RRII

 

[dkinley]

Okay ... I've got something better than a sample DB ... or it should be better - at least I think it provides for better understanding.

In this example there are at least two tables, tblEmployee and tblStatus.

On the form (frmUserInput), the users change two combo boxes to alter the output of a report (rptEmployeeStatus). I demonstrate with arrows that when you click a combo box, the system looks to the underlying queries to get the information for the combo box. Note: I use the term underlying query for the Row Source here (based on one table).

When the information is filled in, the user clicks the print button (cmdPrintButton). This button calls the report. The report must call the underlying query to get data and the underlying query then looks at the combo boxes to get the criteria.

I included combo box properties to complete the illustration and understanding. For the combo boxes, my bound column is column 1. In looking at the underlying query, each one uses the ID in column 1. I set the column widths to 0" and 2" (0";2") so the user does not see the ID, they only see the EmpName or Status (because those are in the 2nd column of the combo box underlying query).

In the underlying query (a seperate query than those for the combo boxes made up of several tables) for the report, I use the the combo box data for the criteria. In this instance, I had to put the combo box reference in the ID fields (for Employee and Status) because I bound the ID fields to the combo boxes.

Hope this diagram helps better.

-dK

 

[dkinley]

... The unbound boxes where not named what I was calling in the Query.

Hehehe .. happens to us all. Good to go.

-dK

 

[RoadrunnerII]

Would like to say a belated Thank you for all the help
I got very busy putting this into production and have just been able to get back to the forum
TY
RRII

 

[dkinley]

Hey RRII, no problem.

Glad it is able to be used for you.

-dk