Only one record in Report wanted 1

[learnasugo]

I only want the record that was just created to be in a report that is e-mailed to my supervisor. Is it possible to do this or will I am I stuck with all of the records in the table? How would I go about fixing this?

Thank you
learnasugo

 

[scriverb]

You can create a query and have the selection criteria linked back to a form control that identifies the last record created. When the record is created there is a primary index for the record which is usually displayed on the form. Create a report with the RecordSouce as a saved query which selects the fields and then selects the records with references in the criteria statement to the following format:

FORMS![[i]formname[/i]]![i]controlname[/i]

Now after you create the new record execute code to run the report or use the DoCmd.SendObjects to email the report to your supervisor.

Post back with questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[learnasugo]

Excuse me for being a dunce but how would I tell the query to look for the last record only?

Thanks
learnasugo

 

[scriverb]

The above tells the query to send the record that is current on the form. That is the last record. The query expression is selecting on a field based on the value of a textbox on your form. They will match and that record gets passed to the report.

Does that clear it up for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[hymn]

have a look at

http://support.microsoft.com/?kbid=209560

Hope this helps
Hymn

 

[learnasugo]

Yes and no. I know what you are saying, but I have no idea how to do it. This should be easy,this is a real simple database. There is only one table and the primary key is used in the report, but is not shown in the form. Right now this is what I have.

HelpDeskID (Autonumber-PK)
SubmitName (Text)
Ext (Number)
Dept (Text)
Problem (Text)
Description (Memo)
Solution (Memo)
Closed (Yes/No)

The Primary key is used as an identifier for the problem that they submitted. It is not in the form but it is in the report that is sent to my supervisor so that he knows which record to look at.

So what you are telling me is that I need to put some kind of filter on the form so that when it pulls in the data it only looks at the last record of the table. Correct? Where would I put a filter on a report?

learnasugo

 

[scriverb]

Let me first understand the process that you application is processing. You have a form where you enter the data for this record and it is saved but still visible on the form? If this is true then we probably have a command button on the form to send the report to your supervisor?

Let's establish if this is how you want to send out the report. If it is through another process that you want to send the report please explain.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[learnasugo]

They fill out the form and then click on a command button which both saves the record and sends an e-mail to my supervisor. Here is the code for the command button.

Private Sub SubmitForm_Click()
On Error GoTo Err_SubmitForm_Click

Dim stDocName As String
Dim strEmail As String
Dim strMailSubject As String
Dim strMsg As String

DoCmd.RunCommand acCmdSave

strDocName = "HelpDeskFrm"

strEmail = "csimms@merklenet.com"

strMailSubject = HelpDeskID & " " & SubmitName


DoCmd.SendObject objecttype:=acSendReport, _
objectname:=strDocName, outputformat:=acFormatHTML, _
To:=strEmail, subject:=strMailSubject, messagetext:=strMsg

Exit_SubmitForm_Click:
Exit Sub

DoCmd.GoToRecord , , acNewRec

Err_SubmitForm_Click:
MsgBox Err.Description
Resume Exit_SubmitForm_Click

End Sub

Does that change things?

 

[learnasugo]

Here is the code for my command button. It now pulls only one button, but it no longer stores (or at least pulls) the current date and time from the database to the report, it used too but not anymore.

Dim stDocName As String
Dim strEmail As String
Dim strMailSubject As String
Dim strmsg As String


Dim strReportName As String
Dim strCriteria As String

[WhitesEndTime] = Now()
DoCmd.RunCommand acCmdSave

strReportName = "WhitesDone"
strCriteria = "[MMS Job#]='" & Me![MMS Job#] & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

strDocName = "WhitesDone"

strEmail = "rgarneriii@merklenet.com"

strMailSubject = Client & " " & "MMS JOB #" & " " & [MMS Job#]


DoCmd.SendObject objecttype:=acSendReport, _
objectname:=strDocName, outputformat:=acFormatHTML, _
To:=strEmail, subject:=strMailSubject, messagetext:=strmsg

It puts the time and date in a text box on the form, but that is not transferred to the report. Anyone care to take a guess as to what is goin on?

learnasugo

 

[scriverb]

From what is posted here the text boxes on the form are probably not Bound textboxes. That means that there is not a field in the record that is identified as the Control Source for these textboxes. Your report is probably making a direct link back to these unbound textboxes. If it worked at one time and now it doesn't I would check to see what is in the Control source for the textboxes in the report header. This may have changed.

Post what you find in these report text controls control source properties. We can then help.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[learnasugo]

It is the form that I am working on and both text boxes are bound. For right now I have these to blocks of code commented out, so I can at least use the database for now.

learnasugo

 

[learnasugo]

Sorry, long night. Disreguard the previous thread.

The text boxes in the report are bound to the text boxes in the form, and the form stays open while the report is open. Do I need to close the form?

learnasugo

 

[scriverb]

You would only want to close the form with code after the report has printed. Then you can close the form.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[learnasugo]

I just found out something interesting, I don't know if it is relevant, but it is interesting. The control source for the form is a query. The control source for the report is a select of the fields needed for the report from different tables. Could that have anything to do with why I don't see the time and date on the report? I think I see why, some of the fields needed for the report are not in the control source for the query.

learnasugo