asked on

Access 2007 report from 2 tables

I am working with two tables for the first time… Glassing_Yield and Glassing_Inspection

I used Database Tools Relationships to join the tables using the field Glassing_Lot that exists in both tables.

On my form the operator uses a combo box to select the Date_Time, Glassing_Lot, PN, and Starting Qty from Glassing_Yield. The info is displayed to the operator.

The operator then enters the End Qty and Operator.

I use this code to save the record to Glassing_Inspection:

Private Sub Save_and_exit_Click()
Dim OkToExit As Boolean

OkToExit = False
OkToExit = CheckDataBeforeSaveAndExit()

If OkToExit = True Then
With CurrentDb.OpenRecordset("Glassing_Inspection")
.AddNew
![Date_Time] = Now()
![Operator] = Me.lstOperator
![Glassing_Lot] = Me.txtGlassing_Lot
![Yield] = Me.txtEndQty
.Update
End With
OkToExit = PrintLabels()
DoCmd.Close
End If

The record saves correctly to Glassing_Inspection. Now I want to print a report(really it’s a label) using that record. The report also needs to get the PN from Glassing_Yield that matches the Glassing_Lot. And finally also print the sum of quantities from End Qty in Glassing_Yield for that Glassing_Lot...

The report Control Source is Glassing_Inspection.
In the report I have:
Text Box            Control Source
txtGlassingLot      Glassing_Lot
txtPN            =[Glassing_Yield]![PN]
txtOperator            Operator
txtStartQty            =DSum("[End_Qty]","[Glassing_Yield]","[Glassing_Lot] = '" & Me.txtGlassing_Lot & "'")
txtEndQty            Yield
txtDateTime            Date_Time

In the code above PrintLabels() executes this code:
DoCmd.OpenReport "Glassing Inspection Label", acViewNormal, , "[ID]=" & Me.ID
The report fails with the error “No Record Found” on that line.

I hope in all this info you can tell me where I went wrong.

Thanks,
Brooks

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gbnorton

ASKER

When I run the report I get a Enter Parameter Value message box for Glassing_Yield. If I leave it blank and select OK the report prints with no data.

Thanks,
Brooks

gbnorton

ASKER

The previous comment error was caused by the DSUM line. I removed it and the error went away.

gbnorton

ASKER

The report now prints without error but no data.

Rey Obrero (Capricorn1)

upload your db

gbnorton

ASKER

Glassing Application has the forms, reports, code...
Glassing.mdb is just the tables and data.

Thanks,
Brooks
Glassing-Application-Rev-J.mdb
Glassing.mdb

Rey Obrero (Capricorn1)

see the reports ( also created a report Newgalssinginspectionlabel), no need to relink tables

Glassing-Application-Rev-J.mdb

gbnorton

ASKER

I looked at the reports.

I renamed the new report to the old name.

Using the Save, Print, and Exit button from the form frm_Glassing_Inspection the report still prints with no data.

I can open the new report and use print preview and it shows up correctly, except for all records. I just need the current record from the tabel Glassing_Inspection.

How did you get the report to show the data?

How can I get the report to run from the Save, Print, Exit command button for the current record?

Thanks,
Brooks

Rey Obrero (Capricorn1)

<How can I get the report to run from the Save, Print, Exit command button for the current record?>

explain how you create the current record..

gbnorton

ASKER

With CurrentDb.OpenRecordset("Glassing_Inspection")
.AddNew
![Date_Time] = Now()
![Operator] = Me.lstOperator
![Glassing_Lot] = Me.txtGlassing_Lot
![Yield] = Me.txtEndQty
.Update
End With
OkToExit = PrintLabels()

gbnorton

ASKER

Capricorn,

I think I found what I need to learn. In this code:

With CurrentDb.OpenRecordset("Glassing_Inspection")
.AddNew
![Date_Time] = Now()
![Operator] = Me.lstOperator
![Glassing_Lot] = Me.txtGlassing_Lot
![Yield] = Me.txtEndQty
.Update
-> numID = [Glassing_Inspection].[ID]
' Stop
DoCmd.OpenReport "Glassing Inspection Label", acViewNormal ' , , "[ID]=" & numID
End With

I get an error on the line numID = [Glassing_Inspection].[ID]
"Glassing_Yield cannot find the field in your expression"

How can I call the report to use the last record in Glassing_Inspection?

Thanks,
Brooks

gbnorton

ASKER

I am able to call the report with this code successfully and get the record 37.

strID = "[Glassing_Inspection].[ID] = 37"
DoCmd.OpenReport "Glassing Inspection Label", acViewNormal, , strID

Now searching for a function to return the last record ID of the table Glassing_Inspection.

Thanks,
Brooks

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

brooks,
is this a multi_user application?

try this code

dim numId
With CurrentDb.OpenRecordset("Glassing_Inspection")
.AddNew
![Date_Time] = Now()
![Operator] = Me.lstOperator
![Glassing_Lot] = Me.txtGlassing_Lot
![Yield] = Me.txtEndQty
.Update

numID = dmax("ID","[Glassing_Inspection]")

' Stop
DoCmd.OpenReport "Glassing Inspection Label", acViewNormal , , "[ID]=" & numID
End With

gbnorton

ASKER

It is a multi user application. 2 stations currently. I'll try the code are report back.

gbnorton

ASKER

Thanks again!