Opening/viewing reports from a combo box

[Valery]

Hi everyone,

In my data entry Client form, named frmUnitUpd, I have a combo box with the following codes to list all reports in the database that begin with the letters COR.

Combo Box: cboReports
Row Source Type: Table/Query
Row Source:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") And (Left$([Name],3)="COR") And (MSysObjects.Type)=-32764 ORDER BY MSysObjects.Name;

As well, a command button, named cmdReports, with the following Event Procedure:

Private Sub cmdReports_Click()
  If Not IsNull(cboReports) And cboReports <> "" Then
DoCmd.OpenReport cboReports, acViewPreview  ' use acNormal to print without preview
  Else
MsgBox ("You Must First Select a Report To Print!")
 cboReports.SetFocus
 End If
 cboReports = ""
End Sub

The above codes work fine. I am able to select a report from the combo box, then click on the command button and it opens in print preview.

Is there a way to enhance these codes so that the selected report is linked to the client record I am into? The link would be a field named: UnitNum, located in the table called tblOccupancy. It is a primary key and number field (not autonumber).

I am a newbie – if you decide to assist (bless your heart), please provide all coding not instructions or explanations on how to create the coding. I would have no idea how to do that. I got the above coding somewhere on the Web.

Thank you!

 

[GinaWhipp]

You just need to apply the WHERE clause...

If your field is numeric...

DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

If your field is text…

DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]='" & Me![ControlOnForm] & "'"

Are you saying that's not working?

 

[Valery]

No, I am sure that would work - looks great. The field is numeric. Exactly where would I insert it? Just so I don't mess up the existing codes which are working fine.

Glad to have your input Gina! Always!

 

[GinaWhipp]

Thanks!

You would replace...

DoCmd.OpenReport cboReports, acViewPreview ' use acNormal to print without preview

with

 DoCmd.OpenReport cboReports, acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

Don't forget to replace the names with the names in your report and on your form.

 

[Valery]

I think it should and could work... but it is taking for ever and then MS Access says "Not Responding". Now, I opened that report, manually, entered the UnitNum of the record I was into (so the link or what you have as Field in Report, and it is fine - opens real quick. The report is just a one page.

In my form, the field is in the header - is that a problem?

The field names and the control names are the same - in both the form and the report. Therefore, field name is UnitNum AND control name is UnitNum.

Mmm?

 

[arnelgp]

you can also use:

docmd.OpenReport reportname:="ReportName", view:=acViewPreview, wherecondition:="[FieldInReport] = [Forms]![yourFormName]![yourControlOnFormName]"

 

[GinaWhipp]

No the Field Name and Control Name are not the same...

Control Name is the name of the Control on your Form (remember, Forms have Controls not fields)

Field Name is the name of the field which is found in the Recordsource of the Report.

 

[Valery]

Thank Arnel. This is the coding I now have, following your suggestion. It is reporting an error:

Run-time error 2103:
The report name 'cboReports' you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist.

The report being selected from the combo box is the same as before - there is only one in there right now.

Private Sub cmdReports_Click()
  If Not IsNull(cboReports) And cboReports <> "" Then
'DoCmd.OpenReport cboReports, acViewPreview  ' use acNormal to print without preview
' DoCmd.OpenReport cboReports, acViewPreview, , "[UnitNum]=" & Me![UnitNum]
 DoCmd.OpenReport reportname:="cboReports", view:=acViewPreview, wherecondition:="[UnitNum] = [Forms]![frmUnitUpd]![UnitNum]"
  Else
MsgBox ("You Must First Select a Report To Print!")
 cboReports.SetFocus
 End If
 cboReports = ""
End Sub

Suggestions?

 

[Valery]

Yes, Gina. I simply wanted you to know that both the field and the control have the same name. I know the difference between the two. THANK YOU

 

[GinaWhipp]

You need to remove the quotes from cboReports because it is a Control and not the actual name of the report.

 

[Valery]

Arggg Now it is doing like before - attempting to run (little round circle thinking) and then Not Responding. It is just taking less time to do it than before.

 

[GinaWhipp]

What is the name of the Control on the Form? What is the name of the Form and what is the name of the Field from the Report?

 

[arnelgp]

maybe you have some code on Open or Load event on your report.

 

[Valery]

What is the name of the Control on the Form? UnitNum
What is the name of the Form? frmUnitUpd
and what is the name of the Field from the Report? UnitNum

"Maybe you have some code on Open or Load event on your report"

Nothing in the REPORT.

I do have the following in the FORM:

OnLoad: Maximize (macro that maximizes the form)
BeforeUpdate: LastUpdateDate (macro that insert today's date in the field UpdateDate)

 

[GinaWhipp]

Let's try this first...

 DoCmd.OpenReport cboReports, acViewPreview, , "[UnitNum]=" & Me![UnitNum]

Okay, it's 4:24 AM where I am... got to get some sleep. I'll check back later to see how it worked.

 

[Valery]

Same - not responding... Calling it a night for now - it's 5 AM. Thank you so much for trying. Will stay posted.

 

[Valery]

Hi,

I tried a separate command button with :

Private Sub Command703_Click()
DoCmd.OpenReport "CORrptLease_AnnexA", acViewPreview, , "[UnitNum]=" & Me![UnitNum]
End Sub

Same thing - waits and waits and get Not Responding. Could my computer be the problem? Like not enough resources or something?

NOTE: I double checked what I already knew:
field UnitNum, both on the form AND in the report, come from the same table: tblOccupancy.

In tblOccupancy, the field UnitNum is a primary key with Data Type : Number (Long Integer).

So I remain confused.

 

[GinaWhipp]

Good idea to use a separate button, we can better track why it's not working when it should.

Please post the SQL of the Recordsource for the Report...

 

[Valery]

Report SQL

SELECT tblOccupancy.UnitNum, tblTenant.*
FROM tblTenant INNER JOIN tblOccupancy ON tblTenant.TenantID = tblOccupancy.TenantID;

Form SQL

SELECT tblOccupancy.*, tblUnit.UnitType, tblTenant.FirstName, tblTenant.LastName, tblTenant.Gender, tblTenant.DOB, tblTenant.Status, tblTenant.MemberDate, tblTenant.HPhone, tblTenant.WPhone, tblTenant.WPhoneExt, tblTenant.CPhone, tblTenant.Email, tblTenant.MailList, tblTenant.Deactivate, tblTenant.DeactivateBy, tblTenant.DeactivateReasons, tblTenant.Comments, tlkpUnitType.UnitTypeDescLong
FROM tblTenant INNER JOIN (tlkpUnitType INNER JOIN (tblUnit INNER JOIN tblOccupancy ON tblUnit.UnitNum = tblOccupancy.UnitNum) ON tlkpUnitType.UnitType = tblUnit.UnitType) ON tblTenant.TenantID = tblOccupancy.TenantID;

 

[GinaWhipp]

I don't see where you have a Control on the Form showing the UnitNum, I only see it in the JOIN. Unless are you pulling it from tblOccupancy? Ans if you are please confirm that the name of the Control on the Form is indeed UnitNum and not tblOccupancy.UnitNum.