Update a report from a form 2

[jpl458]

Creating an email application that has a form and a report. The form has the email code in an OnClick event of a button. the emails I wand to send out must have a serial number on them, so I created a report with a textbox named valCouponSerno. In the form is the following code

Dim serNo As Long
serNo = 1
'------Processing loop-----------------

rptCoupon!valCouponSerNo.Value = serNo 'Initialize Serial Number
Do While mailRs.EOF = False

I want to bump the serial number each time through the loop.

Problem is I can't set the initial serial no (here it's 1) in the text box on the report. The report will sent as an attachment.

Been all over including in here to no avail. Tried dozens of variations including opening the form and setting focus to the textbox.
Thanks

jpl

 

[lameid]

The simplest way to put it in a report would be to have a query with the literal 1 as a column. Then in the report set the control to do a running sum.... I think it is the running sum property.

If you want to keep the data, you should not do that in a report and instead assign your serial numbers before sending.... Autonumber in a table is a likely candidate.

The thing to think about is how you want to use the data later if at all.

 

[jpl458]

Here is what I am trying:
I have created a Global function that stays open for the database ap:

Option Compare Database

' Access global variables definition
Global GBL_SerNo As Long

Option Explicit

Public Sub Init_Globals()
' Access global variable initialization
GBL_SerNo = 1 'Initialize SerialNumber
End Sub

Then the part of the code that uses it is:

Do While mailRs.EOF = False
emTo = mailRs.Fields(2).Value
emFrom = "Address.org"
'emCC = "address.org"
emSubject = Me.Subject
emtextBody = Me.TextMessage
If Len(Me.AttachDoc) > 0 Then 'If no attachment, ignore
DoCmd.OpenReport "rptCoupon", acViewNormal
rptCoupon!CouponSerNo = GBL_SerNo 'Put Serial Number onto report
emAttach = Me.AttachDoc
End If
'-----Send Email via function, get next record and loop-------
Call SendAMessage(emFrom, mailRs.Fields("EmailAddr").Value, emSubject, emtextBody, emAttach)
'ecounter = ecounter + 1
GBL_SerNo = GBLSerNo + 1
Me.emCounter = ecounter
mailRs.MoveNext
Loop
Set mailRs = Nothing
Set tblcon = Nothing
MsgBox "Emails Sent"
End Sub

The line in blue gives me an error of Object required(Which I am sure is the textbox on the report0. But from what I've read I should be able to put the serial number into the report via a global, but I can't figure out how to "address" the textbox on the report from the code on the form. I added the docmd to open the report before the update, but that didn't help.

Thanks for the suggestions, and I will try you solution.

 

[PHV]

I think you wanted this:
Reports!rptCoupon!CouponSerNo = GBL_SerNo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

I think PHV has the quick answer.


Another way to go... make a function that returns the global...

Function Serial_No() as long
     Serial_No  = GBL_SerNo 
End Function

Then use that function as the control source in the report.

= Serial_No()

In this case you have to ensure you wait to run the report until the global is set appropriately.

 

[jpl458]

Here is how I got it to work:

Before the processing loop:
DoCmd.OpenReport "rptCoupon"
Me.CoupSerNo = 1 'Initialize Serial Number
DoCmd.Close acReport, "rptCoupon", acSaveYes

and at the end of the processing loop:

DoCmd.OpenReport "rptCoupon"
Me.CoupSerNo = Me.CoupSerNo + 1 'Bump Serial number
DoCmd.Close acReport, "rptCoupon", acSaveYes

Thus I didn't need the global. I am assuming that by saving the report that the serial number will be intact on the report.

When I tried VHS's
Reports!rptCoupon!CouponSerNo = GBL_SerNo

It gave me an error stating that the report was not open, or missing. I don't understand that at all. All the spelling is correct so I don't know. I tried it both with opening the report and not opening the report.

But thanks for the help.

jpl

 

[lameid]

I am assuming that by saving the report that the serial number will be intact on the report.

No. You'd have to save the print or store the data someplace.

 

[jpl458]

I was wrong. It wasn't working, I just thought it was and PHV has the proper answer.

jpl

 

[jpl458]

Nothing works with this line of code.

DoCmd.OpenReport "rptCoupon", acViewReport
'Reports!rptCoupon!lblSerNo.Caption = GBL_SerNo  'Compiles but does not update the caption
'Report!rptCoupon!lblSerNo.Caption = GBL_SerNo   'Returns "Object Required" Error
'DoCmd.OpenReport "rptCoupon", acViewNormal, , "[lblSerNo.Caption] = GBLSerNo"
'DoCmd.OpenReport "rptCoupon"
'Reports!rptCoupon!couponSerNo.value = GBL_SerNo                         'Initialize Serial Number
    DoCmd.Close acReport, "rptCoupon", acSaveYes

I commented ut all the solutions I have tried.

There is a form (EmailForm), and a Report (rptCoupon) and I need to put a serial number onto the report.
I've tried textboxes on the report and Labels and no success. I have put a test button on the report with a bit of code that works jest fine. But when I try to do the same thing from the EmailForm nothing works. for the life of me, I can't find the answer. Been all over the web and the books that I have.

Any help would be greatly appreciated.

jpl

 

[lameid]

Don't use a label instead use a text box and set its value property (default property).

 

[jpl458]

It didn't work, it's as though the report is unknown, or nonexistent even though I can see it. Entered text, and still get "Object Required". I am assuming that the object in question is the text box on the report

I am also assuming that the report needs to be open in order to manipulate the controls that are on it, is that true?

DoCmd.OpenReport "rptCoupon", acViewReport
Report!rptCoupon!CouponSerNo.DefaultValue = "1"

Really confused

jpl

 

[lameid]

Report[red]s[/red]!rptCoupon!CouponSerNo.Value = "1" 
'Report[red]s[/red]!rptCoupon!CouponSerNo = "1" 'or this because Value is the default property of the control

 

[jpl458]

That did the trick! If you can explain briefly what is the difference between Reports! and Report!

I have to rewrite some code now, but thanks for getting over the hump.

Ain't HighTech grand.

jpl

 

[lameid]

Reports is a collection of Report Objects. So you are getting a report based on name... The Default COLLECTION of the REPORT object is Controls...

You could also write the below but almost no one does as it is unnecessarily long...

Reports("rptCoupon").Controls("CouponSerNo").Value = "1"

And technically...

Application.Reports("rptCoupon").Controls("CouponSerNo").Value = "1"

The issue is there is noway to get at the object without the collection.

 

[jpl458]

Brief and to the point.

Thanks

jpl